Re: History tracking in databases

Sounds like an audit requirement. Keep the old details so you can check to see who cocked up by deleting the record or giving their friend a major discount, etc.

If this is the case, I would approach the problem by creating separate audit tables and storing a image of the old record each time it is updated / deleted using triggers. This will then log changes made through ISQL or DBACCESS as well as any other application.

If the data is required on a daily basis to view changes online, I would still keep the history in a separate table to avoid complications within the code. You know then that only current data is in the table.

The alternative is to store a (C)urrent status on the latest record. This is fine until you find yourself coding "and status = 'C'" in every sql that reads the table. Take it from me, it really starts to annoy you after a while.

Hope this helps or at least doesn't annoy too much.

Mike.

In article <mjrEFu438.I48_at_netcom.com>, Mark Rosenbaum <mjr_at_netcom.com> writes
>Sounds like a data warehouse with a slowling changing product dimension.
>Try Kimball's book The Data Warehouse Toolkit
>
>Mark
>
>In article <872763022.5320_at_dejanews.com>, <bhq001_at_email.mot.com> wrote:
>>Hi,
>>
>>I am trying to develop a relational database for a
>>project at work.
>>Currently we are at the logical design phase for
>>this database.
>>The database structure is very hierarchical due to
>>the data requirements
>>of the project.
>>As part of the design requirements, the database
>>must provide means of
>>tracking historical data. This is further explained
>>below:
>>
>>Say you have a CRATE (CRATE1) which can contain
>>smaller BOXes (BOX1,BOX2
>>etc.)
>>
>>CRATE1 is related to BOX1 and BOX2.
>>
>>Each BOX contains information specific to itself.
>>
>>Say BOX1 information was changed for some reason but
>>we would still like
>>to know the previous information for BOX1.
>>
>>CRATE1 is now related to BOX1,BOX1(old),BOX2
>>
>>Question: How would this kind of relationship be
>>implemented in an RDBMS?
>>
>>Thanks in advance for any advice given.
>>
>>H.Quan
>>
>>-------------------==== Posted via Deja News ====-----------------------
>> http://www.dejanews.com/ Search, Read, Post to Usenet
>
>
 

-- 
Mik Hobbs