Reputation: 171
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
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
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
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