I'm sorry Vincent I'm not exactly sure what you are proposing. Are you proposing that I add another table in addition to what I already have that all the other tables JOIN to and add a state field in that parent table? How is that different than what I have except now I have a new table with an updatable state field? Maybe you can show a query or two to more specifically show what you are suggesting?
Right now this seems like a simple way to get the last time John was called:
// last answered called for John
SELECT MAX(a.answered)
WHERE c.user = John;
If I don't have a calls_answered table I'm not sure how I would get this information if I had a single table with a mutable state. Unless you are suggesting denormalizing all the tables into one table that would have a lot of null fields. For example answered_date would be null if the call was never answered.
On Tue, Apr 15, 2014 at 5:37 AM, Vincent Veyron <vv.lists@xxxxxxxxxx> wrote:
On Mon, 14 Apr 2014 15:22:13 -0700
Robert DiFalco <robert.difalco@xxxxxxxxx> wrote:
Hi Robert,
Yes, but you wrote :
> But then I lose a bunch of data like the TIMESTAMPTZ of the call, answer,
> connection, etc. Btw, currently these tables never need to be UPDATEd. They
> are immutable in the current design.
With your schema, you would have to query several tables each time. Queries will get messy in a hurry, updates and DDL changes too.
>Occasionally I will want to know things like "When was the last time a user
>answered a call" or "How many times has a user been called".
If you need this, you can use a schema that accommodates it.
> And in the end I'm not sure how the
> proposal of one table and a state that is updatable changes the basic
> thrust of the question. For example, getting last call, last answered,
> total called, total answered. If the state of a call transitions from
> called to answered then making it a field loses all the data with the
> previous state, make sense?
>
(off the top of my head, insert a new record instead of altering an existing one, and create a hierarchy with parent records that point to an original call, may be with a 'parent' field and recursive queries. You probably have many requirements that I'm not aware of, but this one can be met)
--
Salutations, Vincent Veyron
http://marica.fr
Gestion des contentieux juridiques, des contrats et des sinistres d'assurance
