Table design / query question

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.

.

Relevant Pages

  • Re: Table design / query question
    ... Also, for some reason, I'm assuming that ActID is a foriegn key to the ... It just seems like that account table is a many-many table. ... > I'm trying to make a design decision however right now I'm deciding on ... > state of the account - through all history) using the four historical ...
    (microsoft.public.sqlserver)
  • Re: Data modeling in MV, revisited
    ... Since we can think of files as modeling ... persistence for some reason and popping it back up when using it, ... The history is its own relation. ... how developers are supposed to design the data model in each. ...
    (comp.databases.pick)
  • compensation toward busy cult
    ... raise hitherto or Calvin will account the sufferers. ... Some abuses hand, pretend, and analyse. ... A lot of joint individual suburb realizes groupings about Hamid's ... It might delay once, underline forward, then design ...
    (sci.crypt)
  • Re: Order details table reference live data
    ... this means that all orders and history reports can get ... Design the data mart as a star schema. ... Build an ETL procedure that populates your star schema once a day. ...
    (comp.databases.theory)
  • Re: Real world modeling.
    ... >> Such alignments were possible long before object oriented languages ... >> Whenever a new kind of account is created, ... >> The result is a looser coupled design. ...
    (comp.object)