I was wondering if there are any best practices for maintaining historic data in a relational database? For example if you were creating a purchase order system and had customer and order tables, if the customer's address changes, you obviously wouldn't want this change to affect previous order details, only future ones. I was thinking you could have a separate customer address table and include it in the order table, but this would get complicated fast as there are many fields you would want to preserve. Any tips on this would be appreciated.
Preserving historic data in a relational database
- Thread starter Chojin
- Start date
Unless you're flooding the database with hundreds of thousands of customers/orders (in which case you'd begin considering using Post Code referencing), you may as well store all the details in long. I'd avoid referencing previous addresses via timestamp (i.e. order timestamps looking up customer address with timestamp prior to that date) - as you'll find customers may return to a previously used address. Better to reference them via a simple record id...
Order Table
[rec_id]
[customer_rec_id] -> Customer Table
[customer_address_rec_id] -> Address Table
[delivery_address_rec_id] -> Address Table
...
Customer Table
[rec_id]
...
Order Items Table
[rec_id]
[order_rec_id]
...
Address Table
[rec_id]
...
Pretty simplistic, and bet there are a few folks here that DB Admin for a living that have far better ways of denormalising these things on the fly.
Order Table
[rec_id]
[customer_rec_id] -> Customer Table
[customer_address_rec_id] -> Address Table
[delivery_address_rec_id] -> Address Table
...
Customer Table
[rec_id]
...
Order Items Table
[rec_id]
[order_rec_id]
...
Address Table
[rec_id]
...
Pretty simplistic, and bet there are a few folks here that DB Admin for a living that have far better ways of denormalising these things on the fly.
Have a one to many relationship between customer and addresses, a one-to-one relationship between custom and their current address, and a one-to-one relationship between order and address for shipping, and maybe another for the cardholder address.