It seems to me that you could simply create two tables like scenerio 1. The
second table would pk across all fields including the new datetime stamp.
When there is a state change add current state in the active table to the
historical table adding a timestamp, then remove the current state and add
the new state.
Also, for some reason, I'm assuming that ActID is a foriegn key to the
accounts information and then is being used as a PK / Unique constraint in
that table? It just seems like that account table is a many-many table.
Maybe i need more info, i don't know...
HTH,
Ben
[quoted text, click to view] <mrpubnight@hotmail.com> wrote in message
news:1122340665.967099.295580@o13g2000cwo.googlegroups.com...
> I'm trying to make a design decision however right now I'm deciding on
> the poorer (but easier) design simply because I can't think of a fast,
> and efficient way to query the data.
>
> Scenario:
>
> Basically we have accounts in our database with a set of FK which
> represent different states for the account. I have to track historical
> changes to the state of the account as the users make the changes.
> Currrently we have 4 different properties that describe the state
> (Status, Activity, Request, ActType).
>
> Problem:
>
> I have to be able to reconstruct the state of an account for a given
> date and/or range of dates.
>
> My design choices are to create one master historical table that takes
> a snapshot of the account whenever the state changes (usually just one
> property will change at a given time), or I can create historical
> tables for each property and add records to those tables as the state
> for the given property changes.
>
> Clearly the second choice is a more normalized solution however the
> first choice gives me the account state by simply querying the table
> and filtering by the accound FK.
>
> The challenge I'm facing is how to construct the first table (complete
> state of the account - through all history) using the four historical
> tables given that the only real common element is the date. I'm having
> difficulty seeing how to build the query without using cursors etc.
> Basically, given this 2nd solution I need to be able create the 1st
> table using a query over the four tables.
>
> Design Scenario 1
> ActID PK
> StatusID FK
> ActivityID FK
> RequestID FK
> ActTypeID FK
> Date
>
> Design Scenario 2
> Table 1
> ActID PK
> StatusID FK
> Date FK
>
> Table 2
> ActID PK
> ActivityID FK
> Date FK
>
> Table 3
> ActID PK
> RequestID FK
> Date FK
>
> Table 4
> ActID PK
> ActTypeID FK
> Date FK
>
> Does anyone have any thoughts on this. I hope I've explained myself
> adequately.
>
> Thanks.
>
