A teammate argues that giving every table a surrogate integer primary key removes the need to think about Second Normal Form. How do you respond, and how would you prove the redundancy is still there?

## The confusion being made A **surrogate key** is a system-generated identifier with no business meaning, such as a sequence integer or UUID. A **natural key** is a combination of business attributes that is unique by the rules of the domain. Adding a surrogate key is a choice about *identification and join mechanics*. Normalization is a statement about *functional dependencies* among attributes. The two intersect only through the definition of candidate key. 2NF says: no non-prime attribute depends on a proper subset of **any candidate key**. So the argument "my primary key is one column, therefore 2NF holds" is only valid when the surrogate is the *only* candidate key. ## Case 1: the natural key is still enforced ``` order_line(id PK, order_id, product_id, quantity, product_name, UNIQUE(order_id, product_id)) ``` The unique constraint declares `(order_id, product_id)` to be a candidate key. It is composite. `product_name` is non-prime and depends on `product_id` alone. That is a textbook partial dependency, and the relation violates 2NF exactly as it did before the surrogate column existed. The surrogate did not touch a single functional dependency; it added one attribute that everything else depends on. ## Case 2: the natural key is dropped If the team removes the unique constraint so that `id` is the only candidate key, the formal 2NF condition now holds vacuously. This is technically true and practically worse: - `product_name` is still stored once per line, so storage and cache pressure are unchanged. - Renaming a product is still a mass update that can complete partially and leave two names for one `product_id`. No constraint can catch it. - The database will now happily store two rows for the same `(order_id, product_id)`, so a retry or a double-submit silently duplicates a line. Losing the natural uniqueness is a real integrity regression. - The redundancy simply migrates one normal form up: `product_id` to `product_name` now has a non-key determinant, so the relation violates 3NF instead. That last point is the crisp rebuttal. Normalization defects do not disappear when you change the identifier; they get reclassified. ## Proving it with data Don't argue from theory alone. Run the dependency check against production: ```sql SELECT product_id, COUNT(DISTINCT product_name) AS names FROM order_line GROUP BY product_id HAVING COUNT(DISTINCT product_name) > 1; ``` Two possible outcomes, both useful: 1. **No rows.** The dependency `product_id to product_name` holds in the data, which confirms the column is derivable and purely duplicated. Follow up with a size estimate: distinct products versus total lines gives the duplication factor. 2. **Some rows.** The redundancy has already gone inconsistent. You now have a defect list, not a design opinion, and you can ask which name is authoritative. This is the fastest way to end the debate. Run the same query per candidate determinant, including `order_id to order_date`, to enumerate every partial dependency in the table. ## When keeping the duplicate is right There are legitimate reasons to store a value that looks partially dependent: - **Point-in-time snapshots.** The product name *as printed on the invoice* is not the same attribute as the product's current name. It is genuinely dependent on the full line key because it is a historical record. Model and name it as such, for example `product_name_at_sale`, so a reader knows it must not be refreshed. - **Deliberate denormalization** for read cost, which is a separate decision that must come with an enforcement mechanism (trigger, application invariant, or scheduled reconciliation) and an owner. The distinction is intent. A snapshot is a different fact; a cached copy is the same fact stored twice and needs a stated strategy for keeping it true. ## How to frame the response Agree with the part that is right: surrogate keys are usually a good default for stable identifiers, immunity to business-key churn, and narrow foreign keys. Then separate the concerns. Keep the natural key as a UNIQUE constraint so the database still enforces the business rule, and evaluate normal forms against all candidate keys. Finally, show the counting query. A schema argument settled with a number from production is far more persuasive than one settled by quoting a normal form.