On 04/14/2014 04:22 PM, Robert DiFalco 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. 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
> <mailto: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
> <mailto: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
> <mailto:pgsql-general(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
I wonder if you really need to place the parts of the call into the
various tables. ringtime, answertime, closetime and all the values
associated with those parts of a call are all fundamental to a single
call, though perhaps collected incrementally. Easy queries, for sure.
(Sorry, I haven't gone back to see your orig. schema. If it's clear
there why these are in separate files, say no more)