On Tue, 15 Apr 2014 07:21:58 -0700
Robert DiFalco <robert.difalco@gmail.com> wrote:
> I'm sorry Vincent I'm not exactly sure what you are proposing. Are you
> proposing that I add another table in addition to what I already have that
> all the other tables JOIN to and add a state field in that parent table?
No : keep table 'calls' with an additional 'status' field, and drop the other tables
> How is that different than what I have except now I have a new table with
> an updatable state field? Maybe you can show a query or two to more
> specifically show what you are suggesting?
>
> Right now this seems like a simple way to get the last time John was called:
>
> // last answered called for John
> SELECT MAX(a.answered)
> FROM calls_answered a JOIN calls c ON c.id = a.id
> WHERE c.user = John;
>
I probably misunderstood something from your first post. Not sure what you call calls_completed, call_errors for
instance,but I had the impression your records would go into a different table according to their status. Do
calls_answeredmove to calls_completed at some point?
In that case, how do you know that you should query calls_answered instead of calls_completed? Or, if John did not
answer,do you query call_errors?
> If I don't have a calls_answered table I'm not sure how I would get this
> information if I had a single table with a mutable state.
Check an appropriate boolean field (call_answered boolean not null default false) would be a way. Again, this needs
studying,and it would take more details to go on.
I stand by my earlier comment though, see Chris's answer which is on the same line.
>Unless you are
> suggesting denormalizing all the tables into one table that would have a
> lot of null fields. For example answered_date would be null if the call was
> never answered.
>
Not a big problem I should say, unless you deal with really hude data.
--
Salutations, Vincent Veyron
http://marica.fr
Gestion des contentieux juridiques, des contrats et des sinistres d'assurance
