Will this work? Need some input

[TripperDay]

Okay, I am TEH N00B when it comes to this stuff so be nice.

I'm building this database and the users need to be able to put together a view of a Client for a certain date in the past. For example, if there's an audit, we need to be able to show the auditor the client's living situation, monthly bills, and amount of the check they were receiving from the government for that date.

I was thinking that if I had a table for every client attribute (such as Address, SpousalInfo, WorkInfo) with attributes of ClientID, UpdateDate, and ChangedTo (the value the attribute was changed to when the client's info was updated), I could build the view by looking for the appropriate ClientID and the last UpdateDate <= Given Date in each attribute table.

I was also thinking I could have only one Update table with ClientID, UpdatedAttribute, UpdateDate, and ChangedTo, where data type was determined by UpdatedAttribute.

Will either one of these methods work in real life? Am I making any sense at all?

[WillR]

Your option1 sounds better.

Have a tblClient with a unique identifier for each client & not much else i.e. only stuff that will not change (maybe have some form of status flag if you close them off at some point)

Have a separate table with the fields that will be updated... you may even want to split this down further, depending on how much data there is... eg a table for income & employment etc
if you have a record created date then you'd be able to view the data via a simple query as it was at a specific date...

Hope this helps,

Will

[TripperDay]

Thanks. That's what I was thinking, but I couldn't find any info on what people who knew what they were doing did when historic information like that was needed.