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 PriceRange
s 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
.
2
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.
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.