Re: Approach to Data Summary and Analysis

Rob Sargent <robjsargent@xxxxxxxxx> · Mon, 14 Apr 2014 16:34:53 -0600



    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:

          
                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

                > );

                
                --

                
                                                    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@xxxxxxxxxxxxxx)

                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)