Re: modelling history in a database

From: Jason <>
Date: 13 Feb 2003 10:22:27 -0800
Message-ID: <>

Jan Marten Visser <> wrote in message news:<>...
> In the database I am designing right now, are a few entities of which
> every change has to be recorded. In other words: i have to be able to
> reset to any moment in the past. Think of it as a kind of version
> control.
> I am wondering if any of you now any literature/papers about how to
> model history in an rdbms.

I don't want to presume anything about your project, but if it's relatively small like the things I usually do, then there are a couple of easy ways to do it.

First is through normalization. Say you have a table Customers that has cust_id, cust_name and cust_balance. If you can determine that cust_id and cust_name will very rarely change and you don't need a history of changes, just pull out cust_balance into its own table Customer_Balances with fields bal_id, cust_id, bal_balance and bal_date. Then you just create a new record in Customer_Balances every time the balance changes.

Second, you could create a table Change that has fields for the table, field, old data, new data and date of change.

Again, I know these are pretty simplistic answers, but take them for what they're worth. Received on Thu Feb 13 2003 - 19:22:27 CET

Original text of this message