Re: Approach to Data Summary and Analysis

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)
FROM calls_answered a JOIN calls c ON c.id = a.id
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(dot)lists(at)wanadoo(dot)fr> wrote:

> On Mon, 14 Apr 2014 15:22:13 -0700
> Robert DiFalco <robert(dot)difalco(at)gmail(dot)com> wrote:
>
> Hi Robert,
>
> > 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.
>
> Yes, but you wrote :
>
> >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".
>
> With your schema, you would have to query several tables each time.
> Queries will get messy in a hurry, updates and DDL changes too.
>
>
> > 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?
> >
>
> If you need this, you can use a schema that accommodates it.
>
> (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
>