You inherit an orders table that stores the customer's address on every order row, and rows for the same customer now disagree with each other. How would you assess the damage and fix the design without losing information?

## Step one: is this redundancy at all? The critical distinction, and the thing interviewers are testing, is between a duplicated fact and a recorded historical fact. If the column answers where did we ship order 1234, then it is not a copy of anything. It is an independent fact about the order, captured at a moment in time, and it must not change when the customer moves. Two orders from one customer disagreeing is then correct data, not drift. If the column answers where does this customer live, then it is a copy of a fact owned by the customer, stored once per order. That is redundancy, and disagreement means some copies were updated and others were not. So the first action is not a query, it is a conversation, plus reading the code paths that write the column. Do any of them update the address on historical orders when the customer edits their profile? If yes, someone intended it as a current-address copy. If nothing ever updates it after insert, it is behaving as a snapshot. ## Step two: quantify Before changing anything, size the problem. Count customers with more than one distinct address, look at whether the variants are genuine relocations, formatting differences, or partial-update residue, and check whether the customer table has an address of its own that agrees or disagrees with the order copies. Also look at when the divergent rows were written; drift usually clusters around a specific release or import. This matters because the remediation differs. Formatting variance is a normalization-of-values problem. Genuine relocation history means the column really was a snapshot. Partial-update residue means a broken write path exists and will keep producing bad data until you fix it. ## Step three: target design The general shape: one home per fact. - customer_address(address_id, customer_id, line1, city, postcode) holds each address once. Orders that need the current address reference the customer and read through. - If the business genuinely needs to know where each order went, keep an explicit snapshot on the order, but make its name say so, ship_to_address_id or a set of denormalized ship_to columns, and make it write-once. A snapshot that is never updated after insert is not a redundancy defect, it is an immutable record, and this is worth stating explicitly because it is the one legitimate reason for the duplication. The deciding question is mutability. A copy that is expected to track its source is a liability. A copy that is expected to freeze is a fact. ## Step four: backfill and reconcile Pick an authority. Usually the customer master record if one exists, otherwise the most recent order, otherwise an upstream system. Whatever you choose, record the rule, because it determines what history you are asserting. Rows that cannot be resolved, for instance a customer whose orders show two addresses with no timestamp ordering that explains it, should be flagged for manual review rather than silently collapsed. Collapsing ambiguous data is how a data-quality problem becomes an invisible data-loss problem. Keep the original values in a staging or audit table for the duration of the migration so any decision can be revisited. ## Step five: close the write paths A schema change alone does not stop drift; the code that wrote inconsistent copies must go. Enumerate every writer, including background jobs, admin tooling, and imports. Then make the invariant enforceable: a foreign key so the address cannot be free text, and, where a snapshot is legitimate, no UPDATE path at all so it cannot drift after creation. Add a verification query to the migration and, if the cutover is staged, to monitoring, so a regression is detected rather than discovered in a report months later. ## What good answers include Intent before action, measurement before migration, an explicit position on snapshots versus copies, a named authority for the backfill, refusal to guess on ambiguous rows, and closing the write paths. Answers that jump straight to add a foreign key miss that the column may have been legitimate all along.