How to handle inmutable data on Databases

Question

I'm working on a quotation software. It basically creates quotations for customers based on certains ProductModel that contains n Plan that contains n PriceRange

Edit:

Here's a ER diagram of the current DB

When you create a quotation, you select 1 or many PriceRanges for it. The thing is those PriceRanges attribute's values can be modified by an user. If they change an attribute's value, previous quotation should maintain the value it was created with (Along with the Plan and ProductModel attribute's values it was created with).

We tried creating a table called QuotationDataParam that contain each attribute for the other 3 tables (ProductModelName, Description, PlanName, RangeLow, RangeHigh, Bounty and Fine). But that way you can't group a PriceRange by Plans, nor can group Plans by ProductModel.

Answer 1

Keep price range data in the PriceRange table, but store this information in the quotation also, using extra quotation detail tables if necessary.

This is no different than any other ordinary invoice. The prices for each line item are always copied to the invoice line items, so that they reflect the price of the items at the time the invoice was created.

Answer 2

I assume the issue is that you would like to have some constraints and foreign keys to make the database automagically enforce statements like "every quoted price is within the price range for that customer/product/etc", but can't because the price ranges can change.

I would associate a timestamp with every price range and quotation. When the price range changes, you don't delete the old range, but simply add a new range with a more recent timestamp. That way, to validate a quotation, you simply check for the newest price range whose timestamp is before the quotation's timestamp. Alternatively, you can give every version of every price range a unique id number via some other mechanism, but I think the timestamp is more meaningful in your case.

Of course, this can be a bit complicated, especially when it comes to those grouping queries you suggested, but most businesses can probably benefit from having a formal audit trail of who changed which product's prices when, so there are plenty of reasons to go this route.