Data Modeling and Schema Design Questions

Direct answer

A PRIMARY KEY uniquely identifies every row, is implicitly NOT NULL, and a table can have only one; a UNIQUE constraint also enforces uniqueness but allows NULLs (depending on the database, typically any number of NULLs, since NULL is not considered equal to another NULL) and a table can have several UNIQUE constraints. Both create an index that can be referenced by a foreign key.

Structured elaboration

• Purpose: PRIMARY KEY declares the table's main identity, the column(s) every foreign key elsewhere in the schema will typically reference; UNIQUE enforces a secondary uniqueness rule (an email address, a username) without making that column the table's identity.
• Nullability: PRIMARY KEY columns are always NOT NULL by definition; a UNIQUE constraint's column(s) CAN be NULL, and most databases (Postgres included) allow multiple NULL rows in a UNIQUE column, since NULL is treated as "unknown," and two unknowns are not considered duplicates of each other.
• Index behavior: both create a unique index under the hood, so both give you fast equality lookups; a PRIMARY KEY's index is also, by convention, what the storage engine physically clusters or orders the table by in some databases (though Postgres itself doesn't physically cluster by PK unless you explicitly run CLUSTER).
• Referencing by foreign keys: a foreign key can reference either a PRIMARY KEY or a column with a UNIQUE constraint (in Postgres, a foreign key target must have a unique index, which either kind satisfies); it does not have to specifically be the primary key.

Worked example

-- (a) primary key on a single column
CREATE TABLE users (
  user_id BIGINT PRIMARY KEY,
  email   TEXT NOT NULL,
  referral_code TEXT
);

-- (b) unique constraint on a nullable column
ALTER TABLE users ADD CONSTRAINT users_referral_code_unique UNIQUE (referral_code);
-- referral_code can be NULL for users with no referral, and Postgres allows multiple such NULLs

-- (c) creating a unique index manually (equivalent enforcement, different syntax)
CREATE UNIQUE INDEX users_email_unique_idx ON users (email);

This was verified against a real PostgreSQL instance: the referral_code column must exist on users before the ALTER TABLE ... ADD CONSTRAINT ... UNIQUE (referral_code) statement can succeed (Postgres rejects it with column "referral_code" named in key does not exist otherwise), so it is declared directly in the CREATE TABLE rather than assumed. With the column present, both the constraint and the manual unique index were created successfully, and inserting two rows with referral_code IS NULL succeeded without a uniqueness violation, confirming multiple NULLs are allowed under UNIQUE.

Practical composite choices: a partial unique index (CREATE UNIQUE INDEX ON subscriptions (user_id, product_id) WHERE status = 'active') enforces "at most one active subscription per user/product pair" while still allowing many historical, cancelled subscriptions for the same pair, which a plain UNIQUE(user_id, product_id) constraint could not express.

Trade-offs and pitfalls

• Assuming a UNIQUE constraint behaves exactly like PRIMARY KEY except for the "only one per table" rule is a common misconception; the NULL-handling difference (multiple NULLs allowed under UNIQUE, impossible under PRIMARY KEY since it's always NOT NULL) is a real, frequently-tested distinction.
• Composite UNIQUE constraints are index-order-sensitive for query planning just like any other composite index: UNIQUE(a, b) serves lookups filtering on a alone efficiently but not lookups filtering on b alone, the same caveat that applies to any multi-column index.
• Partial unique indexes are a Postgres-specific (and a few other engines') feature that isn't portable to every database; if cross-database portability matters, the same "unique among active rows only" rule may need to be enforced with a trigger or application-level check instead.

Direct answer

Partitioning splits a large table into smaller physical pieces (partitions) based on a chosen key, so queries and maintenance operations only need to touch the relevant partitions instead of the whole table; the three common schemes are range, hash, and list, chosen by how your queries typically filter the data.

Structured elaboration

• Range partitioning: partitions cover contiguous ranges of a key, most often a date (e.g., one partition per month). Best when queries commonly filter by that range (WHERE created_at BETWEEN ...), because the planner can skip entire partitions outside the range (partition pruning).
• Hash partitioning: a hash function distributes rows evenly across a fixed number of partitions. Best when there's no natural range to filter on and the goal is simply to spread write and storage load evenly, avoiding one partition becoming a hot spot.
• List partitioning: partitions are defined by explicit, discrete values of a key (e.g., one partition per region or country_code). Best when queries commonly filter on a small, known set of categorical values and you want each category isolated for maintenance (e.g., dropping all data for a deprecated region in one operation).

Worked example

An orders table with billions of rows, mostly queried by recent date range, is a textbook range-partitioning case: PARTITION BY RANGE (created_at) with one partition per month. A query for "orders in March 2026" only scans the March partition, ignoring the other 11+ months entirely (partition pruning), and archiving old data becomes a metadata operation (DETACH PARTITION, then drop or move it to cheap storage) instead of a slow DELETE that has to scan and remove billions of rows one at a time.

Trade-offs and pitfalls

• Maintenance you must plan for: creating new partitions ahead of time (an unpartitioned "future" write will either fail or need a default partition), periodically dropping or archiving old partitions, and keeping per-partition statistics (ANALYZE/VACUUM in Postgres) up to date, since a huge partitioned table with stale statistics on individual partitions can still produce bad query plans.
• A poorly chosen partition key produces the opposite of the intended benefit: range-partitioning by a column that's rarely used in WHERE clauses means every query still scans every partition, and hash-partitioning on a low-cardinality column can leave some partitions far larger than others (a skewed hot partition).
• Partitioning is a physical-storage decision layered on top of the logical schema, not a replacement for good indexing; a well-chosen partition key reduces how much data a query has to consider, but you generally still need indexes within each partition for anything beyond the partition-pruning win itself.

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.

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

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.