Database design: keeping track of record changes

A transaction should not be modified after it has been processed.

A payer details can change over ti开发者_C百科me.

How do you do keep track of changes?

TRANSACTION 
Id(PK) 
PayerId(FK) 
...

PAYER 
Id(PK) 
... 
...

There are a number of ways you could do this.

How about you copy the details into another table at the time of change. Record this, and you'll know to use this information for transactions before that date.

Alternatively you could de-normalise all of this data into a table for reporting.

Or how about a new table that links to the customers details. When this updates link to a new row and on that row link to the previous row. You'll be able to (slowly) extract your data that way.

YOur best bet is to do one of two things. Store the data at the time of the transaction inthe TRANSACTION table or a TRANSACTION DETAILS Table. This is NOT denormalizing. You need the data as of the time it occurred and to leave it as is with a join to the PAYER table which changes over time means you will have incorrect data. The fact that the data is repeated in multiple records is no more significant than CA will appear multiple times in an address table with a state field. You should also store the PAYER ID so you can look up current data if need be.This is the simplest solution.

Another solution is to add a way to look up the data based on time. This is more complex and has to be done very carefully or you will lose data integrity or have very stange report results. It is best I find if you have related tables for the Payor. First set up PAYOR which will basically have the PAYORID and PAYOR DETAILS which wil have the time based data. That way you can enforce the foreign key constraint to PAYOR (so you can look up current details if need be) but still store multiple records in the PAYORDETAILS table when a change to address or name etc. occurs. You would also want a start date and end date for each of these records and trigger to ensure that the start/end dates don't overlap. If you have multiple addresses for instance it then becomes even more complex because each must have a an addres type of which only one can be active at a time (you don't want your reports to show you shipped the product to three addresses). All of your queries should account for checking the start and end date and it is usually best to set up a view which will always show only the current record. This scenario is much more complex to set-up and maintain over time and much more likely to have bugs which can cause you to not have the right data attached to the transaction.

I would strongly recommend making "time" a first class concept in your Payer entity. Damien_the_unbeliever's "temporal table" solution achieves this, but there are alternative implementations.

In the past, I've solved problems like this by adding a validity window to the payer table, and comparing the transaction date to that window.

TRANSACTION 
Id(PK) 
PayerId 
TransactionDate
...

PAYER 
Id 
ValidFrom
ValidUntil
....

This schema allows you to find out the payer as they were at the time of the transaction; it does mean you can't rely on referential integrity (because ID in the Payer table won't be a valid primary key - there are multiple records with the same ID.

A twist on that would be:

TRANSACTION 
Id(PK) 
PayerId(FK) 
...

PAYER 
Id(PK)

PAYER_DETAIL
ID(FK)
PayerID (FK)
ValidFrom
ValidUntil
.....

You can use triggers to keep track of your changes. Keep the history of your changes in new table.

Importance of triggers is that whenever there occurs any change in the table records, we can make history of the changes done.

Sample code for triggers

For updated data you can pick record from Inserted Table

For obsolete data you can pick record from Deleted Table

While creating new table, you can avoid using the foreign key in this table. Directly saving the data into this table will help you to keep the query fast while retrieving history data