I have a problem, which I expect is very common, but I am not sure what to search for, as it can be explained in many different ways.

I am designing a database structure for managing invoices, products, prices, currencies, etc.

I am able to manage prices and names of the products (these are expected to change every now and then).

On invoices, products are added by quantity, with discount, vat, unicorns, squirrels, and so on, and then the total of the invoice is calculated.

Now, when a price is updated in the products table, the invoice is not supposed to change. It needs to stay the same forever.

When thinking about this, to me it seems like the product list is a list of "class type" objects, which describe what a product looks like. Or we could call it a "template" or "base" or "abstract", depending on what abstraction we think of.

The products that are added to the invoice are "instances", "implementations", "children", "versioned records" or whatever we'd call them.

How would I structure this in the database? I could split them into two different tables "AbstractProduct" and "Product", but they would have most (possibly all) of their columns in common. In that regard it would seem appropriate to keep them in the same table. But then I would need an extra column to signify whether it is "class" or "instance". That is fine too. This could also be a foreignkey to the class it was created from (or NULL for classes).

I have tried different approaches, but all seems to have annoying caveats.

Is this a known pattern? What is it called? Are there best practices for solving this issue?

  • I would have a table InvoiceDetails that contains the InvoiceID, ProductID and price paid. This preserves the price paid (accounting for discounts, etc.) at time of invoice as well as allowing the price in the products table to vary as needed. You may run into an issue with Products being deleted or changing specific model information, that is harder to solve this way. Ideally the products table would only ever have products added to it and the only field allowed to change is a "discontinued date" and the price. – Jonathan Fite Nov 2 '15 at 20:28

It is a well known pattern. If you want extensive literature on this topic, search on "temporal database".

  • Your answer lead me to this article. It provides an interesting approach, but it becomes very complex to manage updates and deletes, when many relations exist for a table, because I have to manually manage cascading updates/deletes or versions. Could be problematic. Certainly not for the faint of heart (or database n00bs like me) – Eldamir Oct 29 '15 at 12:57
  • 1
    If I knew of a way to simplify it without damage, I'd share it. – Walter Mitty Nov 25 '15 at 19:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.