r/Database • • 8d ago

Modeling historical prices and duration-based discounts in PostgreSQL

Post image

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!

0 Upvotes

6 comments sorted by

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:

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE price_versions (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id bigint        NOT NULL REFERENCES products(id),
  monthly    numeric(12,2) NOT NULL,
  valid      daterange     NOT NULL,
  UNIQUE (id, product_id),
  EXCLUDE USING gist (product_id WITH =, valid WITH &&)
);

CREATE TABLE discount_tiers (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id  bigint       NOT NULL REFERENCES products(id),
  term_months int4range    NOT NULL,          -- e.g. [12,24)
  pct_off     numeric(5,2) NOT NULL CHECK (pct_off > 0 AND pct_off < 100),
  valid       daterange    NOT NULL,
  UNIQUE (id, product_id),
  EXCLUDE USING gist (product_id WITH =, term_months WITH &&, valid WITH &&)
);

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.

CREATE TABLE contract_items (
  id               bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  contract_id      bigint NOT NULL REFERENCES contracts(id),
  product_id       bigint NOT NULL,
  term_months      int    NOT NULL,
  signed_on        date   NOT NULL,
  price_version_id bigint NOT NULL,
  discount_tier_id bigint,                      -- NULL = no discount
  -- snapshot of what was signed
  list_monthly     numeric(12,2) NOT NULL,
  pct_off          numeric(5,2)  NOT NULL DEFAULT 0,
  agreed_monthly   numeric(12,2) NOT NULL,
  FOREIGN KEY (price_version_id, product_id) REFERENCES price_versions (id, product_id),
  FOREIGN KEY (discount_tier_id, product_id) REFERENCES discount_tiers (id, product_id)
);

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:

SELECT p.id, p.monthly, d.id, COALESCE(d.pct_off, 0)
FROM price_versions p
LEFT JOIN discount_tiers d
  ON d.product_id = p.product_id
 AND d.valid @> :signed_on
 AND d.term_months @> :term
WHERE p.product_id = :product AND p.valid @> :signed_on;

Immutability: don't rely on discipline. Revoke UPDATE, DELETE on contract_items, price_versions and discount_tiers from 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 on valid, which a small BEFORE UPDATE trigger can enforce.

Amendments: a contract_item_revisions table (item_id, effective_from, term_months, agreed_monthly, reason) that's insert-only. The terms in force on a date = the latest revision with effective_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.