Data Modeling and Schema Design Questions

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.

Goal: keep fast point-selects but preserve scan throughput in columnar store.
Design choices:

• Maintain clustered sort keys so frequently point-selected columns (primary key or lookup key) are leading in sort order; this allows range reads to be localized.
• Use zone maps/min/max per segment to skip segments quickly for scans and to identify segments for point selects.
• Build a lightweight secondary index (sparse or bloom-filter index) mapping key -> segment/file offset to avoid scanning whole dataset for points; keep index stored row-oriented (or in separate KV engine) for constant-time lookups.
• Keep data partitioned by high-cardinality dimensions used in point selects (e.g., date or customer_id) so point queries target fewer segments.
• For frequently-accessed small subsets, maintain a small row-oriented cache/materialized table (hot-store) updated in near real-time; fall back to columnar for large scans.
  Trade-offs: secondary indexes and caches increase storage and write cost but give low-latency point queries while columnar layout remains optimized for scans. Monitor write amplification and tune compaction/partitioning.

Direct answer

Store customer addresses as a small, append-only history table (one row per address version, with an effective date range) rather than a single mutable row, so "current address" and "address at time T" are both simple, indexed queries against the same table, and deletion/retention can target specific historical rows without losing the ability to reconstruct any point in time still within the retention window.

Structured elaboration

CREATE TABLE customer_addresses (
  address_history_id BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  line1 TEXT NOT NULL,
  city TEXT NOT NULL,
  postal_code TEXT NOT NULL,
  valid_from TIMESTAMP NOT NULL,
  valid_to TIMESTAMP     -- NULL means this is the current address
);
CREATE INDEX idx_addr_customer_range ON customer_addresses (customer_id, valid_from, valid_to);

• Current-address lookup: SELECT * FROM customer_addresses WHERE customer_id = ? AND valid_to IS NULL.
• Historical audit of changes: SELECT * FROM customer_addresses WHERE customer_id = ? ORDER BY valid_from, which shows every address the customer has ever had, in order.
• "Address at time T": SELECT * FROM customer_addresses WHERE customer_id = ? AND valid_from <= ? AND (valid_to IS NULL OR valid_to > ?), the same point-in-time range pattern used for any effective-dated attribute.
• Efficient deletion/retention: because each address version is its own row with its own valid_from/valid_to, a retention policy ("delete address history older than 7 years") can target and delete specific old rows directly (DELETE WHERE valid_to < now() - interval '7 years') without disturbing the current or more recent rows, which a single mutable-column design with no history at all could never support in the first place, since it never had the history to selectively retain or discard.

Worked example

When a customer updates their address, the write is two steps in one transaction: UPDATE customer_addresses SET valid_to = now() WHERE customer_id = ? AND valid_to IS NULL (close out the previous current row), then INSERT the new row with valid_from = now() and valid_to = NULL. This keeps exactly one "current" row per customer at all times (enforceable with a partial unique index, CREATE UNIQUE INDEX ON customer_addresses (customer_id) WHERE valid_to IS NULL), while preserving every prior address indefinitely (or until the retention policy removes it).

Trade-offs and pitfalls

• The storage and query cost of this design is proportional to how often a customer's address actually changes, which for most customers is rare; the cost is well justified given the alternative (a mutable single row) cannot answer "address at time T" or support any auditable history at all, both of which are commonly real requirements (fraud investigation, compliance, shipping-dispute resolution).
• The two-step "close out the old row, insert the new one" write must happen in a single transaction; if a crash or bug leaves a customer with two "current" rows (valid_to IS NULL on both) or zero, downstream queries assuming exactly one current row will misbehave, which is exactly why the partial unique index enforcing "at most one current row per customer" is worth adding as a schema-level safety net, not just relying on application-level discipline.
• Retention and deletion interact with the historical-audit requirement in tension: deleting old rows to satisfy a retention policy permanently forecloses any future "address at time T" query into the deleted range, so the retention window itself needs to be a deliberate compliance/business decision, not an arbitrary storage-saving default.

Pros of surrogate integer keys: smaller storage, faster joins (integer comparisons), stable joins despite source key churn, and compact clustered indexes—important at hundreds of millions of rows. They decouple ETL changes and support SCDs. Cons: extra mapping layer (natural -> surrogate) and need for lookups when reconciling. Natural keys pros: human-readable, no lookup table needed. Cons: larger size, volatility, potential duplicates, schema coupling, and slower joins. Recommendation: use surrogate integer keys as primary keys for dimension tables at scale, keep natural_key as a unique column with an index for traceability, and maintain mapping in ETL. This balances performance, stability, and auditability.

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.