Re: Approach to Data Summary and Analysis

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. 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?

On Mon, Apr 14, 2014 at 2:43 PM, Vincent Veyron <vv(dot)lists(at)wanadoo(dot)fr> wrote:

>
>
> On Mon, 14 Apr 2014 09:27:29 -0700
> Robert DiFalco <robert(dot)difalco(at)gmail(dot)com> wrote:
>
> > I have several related tables that represent a call state.
> >
> > And so on for calls_connected, calls_completed, call_errors, etc.
> >
> > So for my question -- is the choice between these a personal preference
> > sort of thing or is there a right or wrong approach? Am I missing another
> > approach that would be better?
>
> Hi Robert,
>
> I guess a call state is subject to change, in which case you would have to
> shuffle records between tables when that happens?
>
> ISTM you should consider using only a 'calls' table, and add an
> 'id_call_state' field to it that references the list of possible states.
> This would make your queries simpler.
>
> create table call_state(
> id_call_state text PRIMARY KEY,
> libelle text);
>
> INSERT INTO call_state (id_call_state, libelle) VALUES ('calls_connected',
> 'Connected'), ('calls_completed', 'Completed'), ('call_errors', 'Error');
>
> > CREATE TABLE calls (
> > id BIGINT NOT NULL, // sequence generator
>
> id_call_state INTEGER NOT NULL REFERENCES call_state,
>
> > user_id BIGINT NOT NULL,
> > called TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
> >
> > PRIMARY KEY (id),
> > FOREIGN KEY (user_id) REFERENCES my_users(id) ON DELETE CASCADE
> > );
>
>
> --
>
> Salutations, Vincent Veyron
>
> http://marica.fr
> Gestion des contentieux juridiques, des contrats et des sinistres
> d'assurance
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>