Re: [Q] Most logical way to keep history ?

Harald Schmidt <Harald.Schmidt_at_tomcat.de> wrote in message news:B5A7B4E8.76EA%Harald.Schmidt_at_tomcat.de...
> in article 8lrq66$h1p$1_at_news1.xs4all.nl, Jerold at
> dlareg_spamfilter__at_cryogen.com wrote on 28.07.2000 13:18 Uhr:
>
> > Hi,
> >
> > What is the most logical solution, or 'best' solution according to
> > database-theory to keep the history of an object (row) in a table.
> >
> > ie:
> > Employee Table
> > Fields: id, Name, Adress, Age
> >
> > If one changes the Name the old information should be stored somewhere
 in
> > the database so that the change can be looked up.
> > (so a history of changes to a certain employee is built up)
> >
> > Should I make a separate table called 'old employee'. Or use a
 parent-child
> > kind of relation ship within the Employee Table like:
> > Employee: id, Name, Adress, Age, Parent. where "parent" points to its
> > (newer) parent. ?
> >
Look at your queries. Is the current information performance-critical? Do you usually combine current and history? This may dictate one vs two tables.

Are you really looking at "time-slices", i.e. state at time t? This leans towards one table, but note that your relationships get more complicated, e,g 1-n becomes m-n. Your primary and foreign keys expand to include timestamps, so consider a guid primary key with a separate table relating this key to the time/version info instead.

Finally, temporal dbases are not simple. See

http://timelab.co.umist.ac.uk/publications/reports.html
http://www.isse.gmu.edu/~csis/faculty/tansel93.html
http://vision.ucsd.edu/~deborah/temporal.html