Re: modelling history in a database

From: Jason <>
Date: 14 Feb 2003 08:57:48 -0800
Message-ID: <>

Anton Versteeg <> wrote in message news:<>...
> Depending on the usage of the history table,
> one could leave away the 'new data' part,
> since the 'new data' can be found somewhere else:
> either in a following history entry or in the original table.
> stu wrote:
> > "Jason" <> wrote in message
> >
> > > Second, you could create a table Change that has fields for the table,
> > > field, old data, new data and date of change.
> > >
> >
> > I like that idea! Have you tried it in practice? Does anybody else have
> > any comments on this suggestion?
> >
> > Cheers
> > Stu

The main reason I include a newdata field is to make it easy when automating changes. Say you have a table Lunch with fields Lunch_id and Lunch_fruit, and you know that Lunch 12 will switch between Apples and Oranges every week. If you have a Change Table with a record like this:

Chng_id = (autonum)
Chng_date = (timestamp)
Chng_table = 'Lunch'
Chng_keyval = 12
Chng_field = 'Lunch_fruit'
Chng_olddata = 'Apples'
Chng_newdata = 'Oranges'
Chng_peryear = 52

its really easy to automate the change, every week, with whatever language/script you're using. You could define one-time, non-repeating changes
as Chng_peryear = 0 or NULL or whatever.

One caution: I am fairly new to database design and this scheme might be naive. Anyone have any objections? I'd be really interested to know any pitfalls. Received on Fri Feb 14 2003 - 17:57:48 CET

Original text of this message