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@netcom.com>, Mark Rosenbaum <mjr@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@dejanews.com>, <bhq001@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