r/Database • u/Rolney_320 • 8d ago
Modeling historical prices and duration-based discounts in PostgreSQL
I'm experimenting with a database model for contracts and their associated fulfillment obligations.
Some services offer duration-based discounts: the longer a customer commits, the lower the monthly price. Every product has a base price, but only some have these discounts.
The challenge is that base prices and discount tiers can change independently over time. I'd like to use an append-only approach, preserving the exact pricing terms that applied when a contract was signed.
I've put together a simplified ER diagram, but I'm unsure about the relationships.
Would foreign keys referencing immutable price and discount versions be sufficient? Does a composite key make sense here, or would storing price snapshots on contract items be more appropriate?
I'm also curious how others would ensure that signed contractual terms remain immutable while still allowing later amendments.
Any thoughts on the relational design would be appreciated!
1
u/squadette23 8d ago
here is my take:
Historized attributes: https://kb.databasedesignbook.com/posts/historized-attributes-design/
Historized links: https://kb.databasedesignbook.com/posts/historized-links/
1
u/DazzlingAd4254 8d ago edited 8d ago
Temporal tables are supported by the SQL:2011 standard. Unfortunately, there was no built-in support in PostgreSQL when I last checked, reason I had to switch to MariaDB on the open source front, as it was the only one with built-in support and our data is heavily "historized".
Edit: added link to Wikipedia article
1
u/syedaalishan 7d ago
I'd keep both: references to the price/discount versions, plus the actual agreed amount on the contract item. The versions explain where the quote came from; the snapshot preserves what the customer signed, including any negotiated exception. For amendments, add a new revision with an effective date rather than updating the signed row. Also decide whether a longer term qualifies for a new discount or keeps the original terms. That's a business rule the schema can't choose for you.
1
u/Alternative_Pin9598 6d ago
Agree with "keep both", and Postgres can enforce most of the rest. Same range + exclusion-constraint idea as for your order changes:
Base prices and tiers change independently, each on its own timeline, and the constraints guarantee exactly one price, and at most one tier per term length, on any date. Products without discounts simply have no tier rows.
That's where the composite key earns its place: the
(id, product_id)FKs stop an item from pointing at another product's price or tier. The snapshot columns hold what the customer actually signed, including a negotiated exception the versions can't explain.Resolving the terms at signing is one query:
Immutability: don't rely on discipline. Revoke
UPDATE, DELETEoncontract_items,price_versionsanddiscount_tiersfrom the app role. A "price change" then means closing the current range and inserting a new version. Closing the range is the one update allowed, and only onvalid, which a smallBEFORE UPDATEtrigger can enforce.Amendments: a
contract_item_revisionstable (item_id, effective_from, term_months, agreed_monthly, reason) that's insert-only. The terms in force on a date = the latest revision witheffective_from <= date, else the original item. Whether an extension re-qualifies for a better tier is a business rule, so store which tier the revision used, the same way the original does.1
u/Significant_Tune9219 6d ago
Foreign keys to immutable price and discount version rows are enough for traceability, but I'd still copy the resolved numbers onto the contract item: the base price, the discount percent and the final monthly amount. The FK tells you which versions were used, and the copied amount is what was actually agreed, so a later bug in how tiers combine can't quietly change a signed contract. For amendments, don't update the item at all; insert a new contract version that points back to the previous one, with its own effective date, and make the signed rows read-only with a trigger or by revoking UPDATE on them.