Re: modelling history in a database
Date: 14 Feb 2003 08:57:48 -0800
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,
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