A CUSTOMER table keyed by customer_id stores street, zip_code, city and state, and the business says a zip code determines its city and state. Is that a Third Normal Form violation, and would you actually split it out?

## The formal analysis Relation: `CUSTOMER(customer_id, name, street, zip_code, city, state)`, key `customer_id`. Stated dependencies: `customer_id` to all attributes; `zip_code` to `city`; `zip_code` to `state`. Apply the 3NF test to `zip_code` to `city`. Is `zip_code` a superkey? No, many customers share a zip. Is `city` prime, that is, part of some candidate key? No, the only candidate key is `{customer_id}`. Both clauses fail, so the relation violates 3NF. Equivalently, the chain `customer_id` to `zip_code` to `city` is a transitive dependency. Note the table is in 2NF: the key is a single attribute, so no partial dependency is possible. This is another case where 3NF is the form that catches the problem. The textbook decomposition: - `ZIP(zip_code, city, state)`, key `zip_code` - `CUSTOMER(customer_id, name, street, zip_code)` with a foreign key to `ZIP` Lossless, because `zip_code` is the key of `ZIP`, and dependency-preserving, because both extracted dependencies are enforced by that primary key. ## Why this example is really a judgement question Interviewers use the zip code precisely because the premise is shaky. The relational analysis is easy; what they are testing is whether you validate a functional dependency before designing around it. Things that are true of real postal data: - A US ZIP code is a mail-delivery route, not a geographic region, and some cross city and even state lines. So `zip_code` to `city` and `zip_code` to `state` are not universally true. - Many ZIP codes have a preferred city name plus several acceptable alternates, so "the city" is not a single value. - ZIP codes are added, retired and re-shaped over time, so any extracted table is a slowly changing reference dataset with an upstream owner. - Internationally the rules differ. UK postcodes are far more precise; some countries have no postal code at all, so the column may be optional and the dependency undefined. If the dependency does not hold, then extracting `ZIP(zip_code, city, state)` forces exactly one city per zip and will overwrite or reject legitimate addresses. That is a data-loss bug introduced by over-applying a rule. ## Verify before deciding Check the data you actually hold: ```sql SELECT zip_code, COUNT(DISTINCT city) AS cities, COUNT(DISTINCT state) AS states FROM customer GROUP BY zip_code HAVING COUNT(DISTINCT city) > 1 OR COUNT(DISTINCT state) > 1; ``` Empty result plus a business rule confirming it means the dependency is real and the redundancy is pure duplication. Non-empty result means either the data is dirty, or the dependency is false. Distinguishing those two is the actual work, and it usually needs the domain owner, not more theory. ## What splitting buys and costs Buys: - One authoritative row per zip, so a correction is a one-row update rather than a mass update that can go half-done. - A validation surface: a foreign key rejects unknown zips, and the application can autofill city and state at entry, which reduces typos measurably. - Smaller rows and a small cached lookup table. Costs and obligations: - The reference table needs an owner, a source, and a refresh cadence. A stale postal table rejects valid new addresses, which is a worse customer-facing failure than a duplicated string. - A foreign key on `zip_code` turns "unknown zip" into a hard write failure. That may be right for a billing address and wrong for a free-text profile field. - International addresses may not fit the model at all, pushing you toward a country-scoped reference or a free-form address block. ## The pragmatic answer Most mature systems land on a hybrid, and saying so is a strong answer: 1. Keep a **reference** table of postal codes, sourced and refreshed, used for validation and autofill. 2. Store the address **as captured** on the customer, including city and state, because the address of record is a legal and delivery artifact that must not silently change when the reference data is updated. 3. Accept that (2) is a deliberate denormalization, and be explicit that the customer row's city is a *captured value*, not a derived one. Once framed that way, it is not a 3NF violation at all: the captured city genuinely depends on the customer, exactly like a historical price depends on the order line. That reframing is the punchline. Many apparent transitive dependencies dissolve when you ask what the attribute actually means: a snapshot of a fact at capture time is a different attribute from the current value of that fact, and it belongs on the row that captured it. ## What a weak answer looks like Reciting "zip determines city, therefore split" with no mention of whether the dependency is true, who owns the reference data, or what happens to addresses that disagree with it. The relational reasoning is necessary but it is the easy half.