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.li...@wanadoo.fr
<mailto:vv.li...@wanadoo.fr>> wrote:
On Mon, 14 Apr 2014 09:27:29 -0700
Robert DiFalco <robert.difa...@gmail.com
<mailto:robert.difa...@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 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@postgresql.org
<mailto:pgsql-general@postgresql.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)
