Re: History tracking in databases

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 This can't be answered easily. There are different techniques for doing what you need. I think I may be able to give you some direction. What you could do is in your box table have revision fields. For example Table Name: tblBox Fields: BoxId (You can set this field to type counter if you want) BoxName (Opt. You may have a description field also) Rev (Revision. This could be an integer field which is recommended.) Optional Fields: EffDate (This is the date this instance of this Box is effective) ObsDate (This is the date this instance of this Box is obselete) Active (Boolean(Yes/No) to indicate quickly that this is the active box) The dates are optional because you could just pick the latest Rev of each box programmatically when display, reporting, etc. I recommend at least using the Active field but you must set it when a Revision becomes active. There is a great deal more to it than this, but I hope I have helped. Good Luck -Tim S.