CODEWITHSUNDEEP
c#sqldatabase
SebSky
SebSky

Reputation: 171

Database - Historic Data

Just wondering if you may have opinion on following.

Imagine that I have a simple app that stores invoices for customers.

To simplify invoice table is:

ID int,
NUMBER varchar(20)
CustomerID INT

Customer data is:

ID int
Number varchar(20)
Name varchar(30)
TaxID varchar(20)
...

Now because I want to keep my invoices with original customer data (so the data that customer has on a time when it was printed) I want to avoid changing original customer data at some point as otherwise all past invoices will have customers details changed as well.

What's the best approach?

Upvotes: 1

Views: 103

Answers (3)

SebSky
SebSky

Reputation: 171

Thanks guys for all the ideas. Meanwhile I've came up with some other possible solution and I wonder what's your opinion on it.

Basically I will have 2 Consumer tables - one that holds static data like phone numer , email code etc etc and another with customer details. Once important data changes I will just add details to details table and this ID will be used in the invoice table so:

[Customer]
ID int
Code varchar(20) 
....
Phone

[CustomerDetails]
ID INT
CustomerID Int
Name ...
Address
And other important data that I need to track

[Invoice]
ID
Number
CustomerDetailsID INT

At first sight it seems to be working fine.

Regards Seb

Upvotes: 0

Jacob Lambert
Jacob Lambert

Reputation: 7677

If you would rather not store the information with the invoice (I would not), another solution would be to keep track of changes to the Customer data with a table like so:

HistoricID INT,
CustomerID INT,
....
ValidFrom DateTime NOT NULL,
ValidTo DateTime (NOT NULL) //read below for reason of ()

You can do this two ways: Insert to this table when you insert to your Customer table then update the row to make the ValidTo field have a value plus insert a row every time you update the Customer table. Or you can insert a row every time you update your Customer table reflecting the previous information.

If you go with the latter, ValidTo should be NOT NULL, and you will need to add a ModifiedDate field to the Customer table. Either way, this can be easily accomplished with triggers.

Upvotes: 1

Haney
Haney

Reputation: 34922

This is essentially the difference between referential data and non-referential data. To avoid the referential problem of always accessing the current customer information, you need to write the customer information, as it was at the time, to the invoice table. So your invoice table schema would look more like:

ID int
NUMBER varchar(20) <-- also why is a column called NUMBER a varchar?!
CustomerID INT
CustomerNumber varchar(20)
CustomerName varchar(30)
CustomerTaxID varchar(20)
...

That way the data will always appear as it was, not as it is currently. The downside is that you store more data since you don't take advantage of normalization.

Upvotes: 2

Related Questions