Data Modeling and Schema Design Questions

Requirements: query price at any timestamp; aggregate revenue by price band per day. Approach: use append-only price history table with effective ranges, plus denormalized price-on-order snapshots and partitioning.
Schema:

• product_price_history(product_id, price_cents, effective_from TIMESTAMPTZ NOT NULL, effective_to TIMESTAMPTZ, source varchar, PRIMARY KEY(product_id, effective_from))
• orders(order_id, product_id, qty, price_snapshot_cents, order_ts)
  Storage & indexes:
• Partition product_price_history by date-range of effective_from for efficient prune.
• Index on (product_id, effective_from) and on (product_id, effective_from, effective_to) or use tstzrange and GiST index for faster time-slice lookup.
  Typical queries:
• Price at time t: SELECT price_cents FROM product_price_history WHERE product_id = X AND effective_from <= t AND (effective_to IS NULL OR effective_to > t) LIMIT 1; (use index on product_id,effective_from)
• Revenue by price band per day: ensure orders store price_snapshot_cents at purchase time; then run GROUP BY date_trunc('day', order_ts), CASE WHEN price_snapshot BETWEEN band1 THEN ... END, SUM(price_snapshot_cents*qty).
  Handling multiple changes per day:
• Keep product_price_history as high-resolution (per-change). For aggregations, rely on orders.price_snapshot to avoid expensive joins to history.
  Alternative: if you must compute revenue by price band for historical times when price changed but orders lack snapshot, join orders to price_history using GiST range index on tstzrange(effective_from, effective_to) to find price at order_ts. Partitioning and indexes are crucial to avoid full scans.
  Trade-offs: Storing price snapshot duplicates data (more storage) but makes aggregations fast and stable; history table kept compacted and partitioned for retention.

Direct answer

A surrogate key is a system-generated identifier with no business meaning (an auto-increment integer or a UUID); a natural key is a real-world attribute that's already unique (an email address, a national ID). In a large OLTP system, prefer a surrogate key as the primary key and enforce the natural key separately with a UNIQUE constraint, unless the natural key is guaranteed stable, short, and never needs to change.

Structured elaboration

Trade-offs:

• Uniqueness guarantees: a surrogate key is unique by construction; a natural key's uniqueness depends on a real-world guarantee (emails are supposed to be unique, but people change emails, accounts merge, and typos happen) that the business, not the database, actually controls.
• Join performance and index size: a small fixed-width integer surrogate key produces smaller, faster indexes than a variable-length string natural key (an email), which matters at scale across every table that references it.
• Replication/merge complexity: if two systems (or two data centers) need to merge records that used natural keys independently, natural-key collisions or format mismatches (case sensitivity, whitespace) become integration headaches; surrogate keys sidestep the comparison but push the "these two rows are actually the same entity" problem to a separate identity-resolution step.
• Human-readability: a natural key like an order number is meaningful in logs, support tickets, and URLs; a surrogate key is opaque and usually needs a natural-key-like display value anyway for humans.

Worked example

For customers(customer_id BIGINT PRIMARY KEY, email TEXT UNIQUE NOT NULL) and orders(order_id BIGINT PRIMARY KEY, customer_id BIGINT REFERENCES customers, ...): every foreign key across the system references the compact customer_id integer, not the variable-length email. If a customer later changes their email, that's a single-row update to customers.email and nothing else has to change, because nothing else stored the email as a join key. Had email been the primary key instead, every table referencing it, plus every place that logged or cached it, would need updating when the email changes, which is exactly the fragility surrogate keys are designed to avoid.

Trade-offs and pitfalls

• A pure natural key becomes especially fragile the moment the "immutable, unique forever" assumption breaks; social security numbers get reissued in rare cases, emails get reused after account deletion, and any of those breaks a chain of foreign keys that assumed permanence.
• The common middle ground, and usually the right one, is: surrogate key as the primary key for joins, natural key kept as a UNIQUE NOT NULL column for lookups and human-facing operations, giving you the stability of the surrogate and the readability/query convenience of the natural key.
• Surrogate keys are not free: they add one layer of indirection (you often have to look up the surrogate key before you can query by the natural key the user actually typed), and if generated naively (plain auto-increment) they leak information about record counts and creation order, which occasionally matters for security or business-confidentiality reasons.

Direct answer

A composite primary key works by combining two or more columns so that the combination is guaranteed unique, even if no single column is; here (order_id, product_id) guarantees a given product appears at most once per order, while still letting the same product appear on many different orders and the same order contain many different products.

Structured elaboration

CREATE TABLE order_items (
  order_id   INT NOT NULL,
  product_id INT NOT NULL,
  quantity   INT NOT NULL,
  price      NUMERIC(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

Joining to orders and products is unaffected by the key being composite: orders joins on order_id, products joins on product_id, exactly as it would with a single-column key:

SELECT o.order_id, p.name, oi.quantity, oi.price
FROM order_items oi
JOIN orders   o ON o.order_id   = oi.order_id
JOIN products p ON p.product_id = oi.product_id;

Worked example

Verified in sqlite3: inserting (order_id=1, product_id=100) twice (say, two separate lines both adding the same product to the same order) fails on the second insert with a primary-key violation, confirming the composite key correctly prevents a duplicate line for the same product on the same order. If the business actually wants to allow two separate lines for the same product on the same order (for example, two different discount codes applied to two units of the same product), that's exactly when you'd prefer a surrogate order_item_id instead: the composite key as given structurally assumes one row per (order, product) pair, which is a real business rule, not just an implementation detail.

Trade-offs and pitfalls

• The composite key here is enforcing a genuine business rule (one line per product per order), not just providing uniqueness for its own sake; if that rule is ever wrong (the two-discount-codes scenario above, or wanting separate lines for auditability), the fix is a surrogate order_item_id plus, if the one-line-per-product rule still needs to hold in the common case, a separate UNIQUE(order_id, product_id) constraint that can be relaxed later without changing the primary key.
• A common mistake is adding a surrogate key "just in case" without checking whether the composite key was already expressing a real invariant worth keeping; dropping the composite uniqueness silently allows the exact duplicate-line bug the original design prevented.
• At scale, a composite key clusters the table by order_id first (in engines that physically order by the primary key), which is usually the right locality for "all items in this order," but means "all orders containing this product" needs a secondary index on product_id alone rather than relying on the primary key.

Direct answer

Track promotion redemptions with three tables: promotions (the rule definition), promotion_assignments (which users are eligible for which promotion), and promotion_redemptions (which assignments were actually used), so that redemption rate is a simple ratio between the last two tables.

Structured elaboration

• promotions(promotion_id PK, code, discount_type, discount_value, is_one_time_use, valid_from, valid_to).
• promotion_assignments(assignment_id PK, promotion_id FK, user_id FK, assigned_at): one row per user made eligible for a promotion, whether by targeting, opt-in, or automatic enrollment.
• promotion_redemptions(redemption_id PK, assignment_id FK, order_id FK, redeemed_at, is_one_time_use): one row per actual use. The is_one_time_use column is a copy of the flag from promotions, captured at redemption time so the one-time-use rule can be enforced with a partial unique index scoped to this table alone (a unique index cannot reference columns on another table, so the flag has to be denormalized onto the redemptions row itself to be enforceable this way).

Metrics fall out directly: redemption_rate = COUNT(DISTINCT promotion_redemptions.assignment_id) / COUNT(DISTINCT promotion_assignments.assignment_id) per promotion, and time_to_redemption = redeemed_at - assigned_at, averaged per promotion.

Worked example

CREATE TABLE promotions (
  promotion_id   BIGINT PRIMARY KEY,
  code           TEXT NOT NULL UNIQUE,
  discount_value NUMERIC(10,2) NOT NULL,
  is_one_time_use BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE promotion_assignments (
  assignment_id  BIGINT PRIMARY KEY,
  promotion_id   BIGINT NOT NULL REFERENCES promotions(promotion_id),
  user_id        BIGINT NOT NULL,
  assigned_at    TIMESTAMP NOT NULL,
  UNIQUE (promotion_id, user_id)
);

CREATE TABLE promotion_redemptions (
  redemption_id  BIGINT PRIMARY KEY,
  assignment_id  BIGINT NOT NULL REFERENCES promotion_assignments(assignment_id),
  order_id       BIGINT NOT NULL,
  redeemed_at    TIMESTAMP NOT NULL,
  is_one_time_use BOOLEAN NOT NULL   -- copied from promotions.is_one_time_use when the redemption is written
);

-- Enforces "at most one redemption per assignment" only for assignments flagged
-- one-time-use; multi-use promotions are free to redeem the same assignment
-- more than once.
CREATE UNIQUE INDEX one_time_redemption_uq
  ON promotion_redemptions (assignment_id)
  WHERE is_one_time_use;

This was verified in sqlite3 (which supports partial indexes): for a one-time-use promotion, a second INSERT referencing the same assignment_id is correctly rejected by the partial unique index; for a non-one-time-use promotion (is_one_time_use = false), a second redemption against the same assignment_id correctly succeeds, since that row falls outside the partial index's WHERE clause. A plain, unconditional UNIQUE constraint on assignment_id (with no WHERE clause) would have blocked legitimate repeat redemptions for multi-use promotions too, which is why the constraint has to be partial rather than blanket.

Trade-offs and pitfalls

• Concurrency is the real risk with one-time-use promotions: two simultaneous checkout requests for the same user and promotion could both pass an application-level "already redeemed?" check before either commits; the partial UNIQUE index is what actually prevents the double-redemption race, by making the database itself reject the second insert rather than relying on a check-then-act race in application code.
• The is_one_time_use value on promotion_redemptions is a denormalized copy of the flag on promotions, captured at write time; if the promotion's flag ever changes after assignments already exist, already-written redemption rows keep whatever flag they were written with, so a promotion-level flag change does not retroactively tighten or loosen enforcement on rows written before the change.
• For promotions with stacking rules, conditional eligibility (new customer only, minimum basket size), expiration, and partial refunds, this schema is a floor, not a ceiling: those rules typically live in a separate eligibility-evaluation layer that reads promotions and the customer/order context at checkout time, rather than being encoded as extra columns on these three tables, because the rules themselves change far more often than the redemption-tracking shape does.
• Redemption rate as defined here silently excludes users who were never assigned in the first place; if the real question is "what fraction of ALL customers used this promotion," you need to join against the full customer base, not just promotion_assignments, which measures conversion only among those who were targeted.

Direct answer

The three realistic options for row-level change history are a separate audit table (one row per change, referencing the original table), database-level change-data-capture (CDC, reading the write-ahead log independently of the application), or an append-only pattern (the "table" itself is a log of versions, with the current state being the latest version per key); the right choice trades off write-path coupling against query convenience.

Structured elaboration

• Separate audit table (orders_audit(audit_id, order_id, changed_by, changed_at, change_reason, old_values, new_values)), populated either by a trigger on orders or by explicit application-level writes alongside the main update. Simple to query directly ("show me the history of this order"), but a trigger-based implementation couples every write's latency to the trigger's cost, and an application-level implementation risks missing history if some code path updates the table without remembering to also write the audit row.
• Database-level CDC (reading the write-ahead log or a logical-replication stream, e.g., via Debezium, entirely outside the application): captures every change regardless of which application code path made it (closing the "forgot to log it" gap), decoupled from the write's own transaction latency, but the history lives in a separate downstream system (a Kafka topic, a sink table) rather than directly queryable alongside the source table without additional plumbing.
• Append-only pattern (never UPDATE in place; every change is a new row, and "current" is whichever row for a given key has the latest timestamp or highest version number): the history IS the table, so there's no risk of forgetting to log a change, and querying "state as of time T" is a natural filter; the trade-off is that every ordinary read of "current state" now needs a filter or a materialized "current" view to avoid scanning the entire history.

Worked example

For created_by/created_at/updated_by/updated_at as always-present columns: these answer "who last touched this row and when" cheaply for the CURRENT row, but they overwrite prior history on every update, so they are not a substitute for any of the three mechanisms above if you need to see the full sequence of past changes, only a complement to them for the common, cheaper case of "who changed this most recently."

Trade-offs and pitfalls

• The trigger-based audit table is the most common production pattern because it's the most directly queryable, but it's also the easiest to accidentally bypass: a bulk UPDATE run directly against the database by an operator, or a data-migration script, can skip the application code path the audit-table logic assumed was the only writer, silently leaving a gap in the history.
• CDC closes that "forgot to log it" gap completely (it reads the actual write-ahead log, not application intent), which is exactly why it's the more robust choice when auditability is a compliance requirement rather than a convenience feature, at the cost of the history living in a separate system that needs its own query tooling.
• The append-only pattern's "no UPDATE ever" discipline is the strongest guarantee of the three (there is structurally no way to lose history, since nothing is ever overwritten), but it pushes real complexity onto every reader that wants current state, and it requires a clear convention (a version number, or a is_current flag maintained carefully) to avoid every query needing to compute "which row is the latest" itself.