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

Jerold wrote:
>
> What is the most logical solution, or 'best' solution according to
> database-theory to keep the history of an object (row) in a table.

Just add a 'begin-date' and 'end-date' column to the table. Then every time a property of an object is changed you: 1. Add a complete new row with the new properties. 2. Set the 'begin-date' of the new row to the date that this property changed.
3. Set the 'end-date' of the old row to the date that this property changed.

This way you will have all the information (historical and current) in one and the same table. If you only want to see the current information you can define a view that selects all the rows where the 'end-date' is NULL or beyond the current date. If you want to see who was working for the company in 1998 you simply ask for the rows where the begin-date lies before 1-1-1998 and the end-date is NULL or later than 12-31-1998.

Note that in this table it is no longer true that you have only one row per object so the object identifier is no longer a key of the table. The new keys are left to the reader as an exercise :-). I can tell you how to get rid of the NULL values (some relational purists such as C.J. Date don't like them) and the redundancy that is caused by the fact that you copy the entire row if only field changes, but IMHO that will make your database schema only much harder to understand.

Kind regards,

• Jan Hidders