1

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?

001
  • 62,807
  • 94
  • 230
  • 350
  • What's wrong with storing the product codes/descriptions with the invoice? More specifically, I see them stored in `Invoice_Line_Items`, following the design the link points to. – Andriy M Feb 06 '11 at 19:58
  • duplication that waste resources, also unable to track what was changed. – 001 Feb 07 '11 at 01:38

1 Answers1

6

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

Community
  • 1
  • 1
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459