Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: modelling history in a database

Re: modelling history in a database

From: Jason <pmorrison_at_4lpi.com>
Date: 13 Feb 2003 10:22:27 -0800
Message-ID: <cf188409.0302131022.3be0e1ad@posting.google.com>


Jan Marten Visser <jan.marten.visser_at_virgil.nl> wrote in message news:<3E4A2B18.C729DD29_at_virgil.nl>...
> 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 - 12:22:27 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US