2

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?

1
  • 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. Nov 2, 2015 at 20:28

1 Answer 1

2

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

2
  • 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, 2015 at 12:57
  • 1
    If I knew of a way to simplify it without damage, I'd share it. Nov 25, 2015 at 19:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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