Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: modelling history in a database

Re: modelling history in a database

From: Jason <pmorrison_at_4lpi.com>
Date: 14 Feb 2003 08:57:48 -0800
Message-ID: <cf188409.0302140857.556b0086@posting.google.com>


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 - 10:57:48 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US