Things like this. AVG ring time before answer, average connected call duration. % of calls never answered. % of calls that are answered that are connected. Number of times John has answered a call versus how many times we've called him.That sort of stuff.
On Mon, Apr 14, 2014 at 3:34 PM, Rob Sargent <robjsargent@xxxxxxxxx> wrote:
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)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.lists@xxxxxxxxxx> wrote:
Salutations, Vincent Veyron
On Mon, 14 Apr 2014 09:27:29 -0700
Robert DiFalco <robert.difalco@xxxxxxxxx> 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
> );
--
http://marica.fr
Gestion des contentieux juridiques, des contrats et des sinistres d'assurance
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
