Re: modelling history in a database

Anton Versteeg <anton_versteeg_at_nnll.iibbmm.com> wrote in message news:<3E4CC531.2506F59C_at_nnll.iibbmm.com>...
> 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" <pmorrison_at_4lpi.com> wrote in message
> > news:cf188409.0302131022.3be0e1ad_at_posting.google.com...
> > > 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