Hello,

For a database design, I'm looking for the best way to model history.
For example (but it can of course get more complex):
We have a PERSON table and one of the attributes is status which can
be [baby, toddler, child, adolescent, adult] or something like that.
I want to keep a history and know from every person on what date he
changed from one status to another, e.g. James Smith went adolescent
on 2005-02-07. (I know the example is stupid )
My thought is to make a separate table with that. Something like
PERSONSTATUSHISTORY and add a record to that whenever a status changes
in the PERSON table. The advantage is that the PERSON table has one
record per person and it's all easily understandable I think. Another
way is to put changes immediately in the PERSON table. But of course
then my person_id primary key for PERSON-table is not enough anymore
and it's more difficult to just select a person and see what his
status is now.

Can anybody advice me on how to model history the best way? Or show
me places to read about it. There are probably things I don't think
about and I want a model that I won't regret about later on .

Greetz,

Bart

On 20.03.2007 12:10, Bart op de grote markt wrote:
How are you going to use that information? If you frequently query
current data and only occasionally need the old data then my guess would
be that the two table approach is best. If, OTOH you continuously
access all the data (i.e. current and old) then a single table might
work equally well. It depends.

Kind regards

robert

Bart op de grote markt wrote:
Depending on how you want to use this database you can go in two directions.
If you want a transactional database that is history aware, you should
have a look at temporal databases:
http://en.wikipedia.org/wiki/Temporal_database
Some dbms's like oracle have built in support for temporal queries based
on transaction time.

You can also have a look at the Slowly Changing Dimension technique that
is applied in data warehouses for preserving history.

Hope this helps,
Axel Hallez

Thanks for the replies! I will investigate it.

Kind regards,

Bart

Here is an old posting of mine. Let me use a history table for price
changes. The fact to store is that a price had a duration:

CREATE TABLE PriceHistory
(upc CHAR(13) NOT NULL
REFERENCES Inventory(upc),
start_date DATE NOT NULL,
end_date DATE, -- null means current
CHECK(start_date < end_date),
PRIMARY KEY (upc, start_date),
item_price DECIMAL (12,4) NOT NULL
CHECK (item_price > 0.0000),
etc.);

You actually needs more checks to assure that the start date is at
00:00 and the end dates is at 23:59:59.999 Hrs. You then use a
BETWEEN predicate to get the appropriate price.

SELECT ..
FROM PriceHistory AS H, Orders AS O
WHERE O.sales_date BETWEEN H.start_date
AND COALESCE (end_date, CURRENT_TIMESTAMP);

It is also a good idea to have a VIEW with the current data:

CREATE VIEW CurrentPrices (..)
AS
SELECT ..
FROM PriceHistory
WHERE end_date IS NULL;

On 23.03.2007 15:51, --CELKO-- wrote:
What would be the best way to ensure that there are no overlapping
ranges for one product?

But there's a caveat: if prices are entered with future start dates
(i.e. "we start selling X for $9.95 next month") the view will pull a
wrong current value. In that case a different view might be better:

-- Oracle
create view ...
as
select ..
from PriceHistory
where start_date <= sysdate
and ( end_date is null or end_date > sysdate )

Kind regards

robert

--CELKO-- wrote:
What are the advantages and disadvantages to keeping history in the table with
the current data vs, having a separate history table?

-- Lew

It would depend on your business rules and situation. I have found
that people who have a history table are probably using it to look at
recent trendsm so keeping that data in one place makes queries
easier. It also means that if we get new data, we do not have to move
rows from one table to another.

Bart op de grote markt wrote:
call Person_History, and make the person key unique in History but make
the Person_History table have both the person and date. This gets
complicated because Person_History could also have a Foreign Key called
Events which links Persons to specific Events. Take an event like say
the Alamo. Many people died at the Alamo, so you want to link them all
to that event. Some events may even have multiple children events. For
instance world war II has lots of Battles.

The usual design error is to have only one time in a row to capture
when an event started, then do horrible self-joins to get the duration
of the status change. Let me use a history table for price changes.
The fact to store is that a price had a duration:

CREATE TABLE PriceHistory
(upc CHAR(13) NOT NULL
REFERENCES Inventory(upc),
start_date DATE NOT NULL,
end_date DATE, -- null means current
CHECK(start_date < end_date),
PRIMARY KEY (upc, start_date),
item_price DECIMAL (12,4) NOT NULL
CHECK (item_price > 0.0000),
etc.);

You actually needs more checks to assure that the start date is at
00:00 and the end dates is at 23:59:59.999 Hrs. You then use a
BETWEEN predicate to get the appropriate price.

SELECT ..
FROM PriceHistory AS H, Orders AS O
WHERE O.sales_date BETWEEN H.start_date
AND COALESCE (end_date, CURRENT_TIMESTAMP);

It is also a good idea to have a VIEW with the current data:

CREATE VIEW CurrentPrices (..)
AS
SELECT ..
FROM PriceHistory
WHERE end_date IS NULL;

Then download the Rick Snodgrass book on Temporal Queries in SQL from
the University of Arizona website (it is free). And finally Google up
my article at www.DBAzine.com on transition constraints.