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

You've hit one of the major shortcomings of the relational model: the ability to handle dependance on time. Database are only ever consistent at a single point of time, hence the need for read-consistent gets, rollback segments, etc.

Normally what you want to capture is how a particular attribute or combination of attributes of an entity changes over a period of time. There are a number of ways that you can do this:

1. Put your history in a child table. The primary key of the child table will be a composite key made up of the foreign key and a sequence number. Attributes will be the attribute(s) of the parent key which should be tracked, a start date and an end date. The end date you don't need but you'll get performance problems if you leave it out.

Example: If you want to record the status of a customer over a period of time:

CREATE TABLE CUSTOMER
(

    CUSTOMER_ID number,
    STATUS varchar(1),
) primary key constraint on CUSTOMER_ID;

Create a customer status history table

CREATE TABLE CUSTOMER_STATUS_HISTORY
(

    CUSTOMER_ID number,
    SEQUENCE_NO number,
    STATUS varchar(1),
    START_DATE date,
    END_DATE date,
) primary key constraint on (CUSTOMER_ID, SEQUENCE_NO),   foreign key constraint on CUSTOMER_ID;

Excuse my SQL.

2. Store your history in your customer table:

CREATE TABLE CUSTOMER
(

    CUSTOMER_ID number,
    SEQUENCE_NO number,
    STATUS varchar(1),
    START_DATE date,
    END_DATE date,
) primary key constraint on (CUSTOMER_ID, SEQUENCE_NO);

Whatever you choose is a performance consideration depending on the nature of your application. For example if you application is mainly OLTP you probably go for option 1 as your are probably more concerned with the current status. If you application is batch oriented for example a billing system option 2 might be enough.

In most cases option 1 would be the best option as you are taking the historical data, which may be growing rapidly, away from the current data.

Jerold wrote:

> 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. ?
>
> Thanks!
>
> Jerold.

--
Ciaran Harron
Logica
Senior Consultant
Solutions, Telecoms, Asia
tel: +6221 526-4702
mobile: +62811 987348
fax: +6221 252-5760
http://www.logica.com