Re: Approach to Data Summary and Analysis

On Mon, 14 Apr 2014 09:27:29 -0700
Robert DiFalco <robert.difalco@gmail.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
possiblestates. 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