Invoice database design

Question

The invoice database design, might look something like this... http://www.databaseanswers.org/data_models/invoices_and_payments/index.htm

Now If the user decides to change/revise the product code/description

It will change the previous order and invoice produce code/description :(

What do you do? Copy the product code description to the invoice table instead?

Answer 1

You basically have two options:

• either you make your Products table "time-enabled" (also known as "temporal database"), e.g. you keep the "previous" state of your individual product in your table, and you give every entry a ValidFrom / ValidTo pair of dates. That way, if you change your product, you get a new entry, and the previous one remains untouched, referenced from those invoices that used it; only the ValidTo date for the product gets updated

or:

• you could copy the products (at least those bits you need for your invoice) to the invoice - that'll make sure you always know what the product looked like when you created the invoice - but this will cause lots of data duplication (not recommended)

See this other Stackoverflow question on temporal databases as another input, and also check out this article on Simple-Talk: Database Design: A Point in Time Architecture