You take ownership of a live, revenue-carrying OLTP schema that contains several textbook anti-patterns at once - missing foreign keys, a generic shared lookup table and a polymorphic association. How do you decide what to fix, in what order, and how do you change it without downtime?
answer
- Rank by silent corruption risk, not ugliness
- Stop the bleeding: no new code extends the pattern
- Cheap guarantees first: constraints on already-clean data
- One structural migration at a time to completion
- Expand, dual-write, backfill, reconcile, switch reads, drop last
basics
~20 sRank by risk of silent data corruption, not by ugliness. Stop the bleeding first so new code does not extend the pattern, then fix what is causing measurable incidents, using expand-and-contract: add the new structure, dual-write, backfill, verify, move readers, drop the old last.
solid answer
~1 min**Decide with evidence, not taste.** For each anti-pattern gather: how much bad data already exists (orphan counts, invalid discriminators, mis-categorised lookups), which incidents it has caused, and how much friction it adds to current roadmap work. An anti-pattern that is merely inelegant and stable ranks below one silently corrupting data or blocking the next two quarters. **Order of work:** 1. **Stop the bleeding.** New tables and new code must not extend the pattern. This is free and stops the problem compounding while everything else is debated. 2. **Add constraints where the data is already clean.** Adding foreign keys is often days of work, not months: measure orphans, clean them, add the constraint in a non-validating mode so new writes are protected immediately, validate history in the background. Highest guarantee gained per unit of effort. 3. **Restructure what hurts.** The polymorphic association and the shared lookup need real migrations. Take one, finish it, prove the pattern works, then take the next. Parallel half-finished migrations are worse than the original schema. **Mechanics:** expand and contract - add the new shape, dual-write, backfill in batches, reconcile old against new continuously, move readers incrementally behind a flag, and drop the old structure last as a separate, deliberate step. **And decide what to leave alone**, explicitly, with the reason recorded.
code
sql · 9 lines-- orphaned children per relationship
SELECT 'order_line' AS rel, count(*) AS orphans
FROM order_line ol LEFT JOIN orders o USING (order_id)
WHERE o.order_id IS NULL
UNION ALL
-- polymorphic rows whose discriminator names no known parent type
SELECT 'comment_bad_type', count(*)
FROM comment
WHERE commentable_type NOT IN ('article', 'photo', 'video');go deeper
Recognise the individual anti-patterns and know that fixes on a live system happen incrementally, not as one rewrite.
Describe expand-and-contract concretely, including dual-write, batched backfill and dropping the old structure last.
Gather evidence per defect, add constraints without long locks, and run a full migration with reconciliation and a rollback path.
Own the ranking by data-corruption risk and roadmap impact, sequence one migration at a time, state explicitly what will not be fixed and why, and install the mechanisms that prevent regression.
## Framing Inheriting a schema with several anti-patterns is a prioritisation problem, not a purity problem. The failure mode is starting three migrations, finishing none, and leaving the system carrying both the old shape and three partial new ones. ## Step 1: quantify, do not editorialise For each defect, collect numbers: - **Existing damage.** Orphan counts per suspected relationship via anti-joins. Rows whose polymorphic discriminator names no real type, or whose parent id resolves to nothing. Referencing columns holding a lookup value from the wrong category. - **Incident history.** Search postmortems and bug tickets for failures traceable to the defect. A missing foreign key that produced a customer-visible incident outranks one that has never mattered. - **Change friction.** How often does roadmap work stall on it? "Every new content type requires editing eleven queries" is a quantified tax. - **Growth rate.** Is the bad data increasing? A defect producing orphans daily is urgent; one that produced them only during a 2023 import is a cleanup, not a redesign. This converts an argument about aesthetics into a ranking anyone can review. ## Step 2: rank by consequence A useful ordering of severity: 1. **Silent data corruption.** Wrong references, cross-category values, orphans that make totals wrong. The system reports incorrect answers and nobody knows. 2. **Unenforceable invariants blocking a change now.** The next feature genuinely requires the guarantee. 3. **Chronic operational cost.** Contention, unbounded row growth, migrations that lock. 4. **Friction only.** Verbose queries, repeated per-type branching. 5. **Inelegance with no measured cost.** Items four and five are candidates for deliberate acceptance. ## Step 3: stop the bleeding first Before any migration, set the ratchet: new tables get foreign keys; new enumerations get their own table or a check constraint, never a row in the shared lookup; new attachable types use the chosen enforced pattern, not the polymorphic pair; new attributes go to the appropriate satellite, not the god table. This costs nothing, needs no migration, and prevents the surface area growing while the real work is scheduled. Make it a written rule with a reviewer, or it will not hold. ## Step 4: harvest the cheap guarantees Adding a missing foreign key is usually the best return on effort in the whole programme. The sequence: 1. Count orphans with an anti-join. 2. Triage them: delete meaningless rows, reassign valuable ones, or null the reference where the relationship is optional. 3. Create the index on the child's referencing column, without which parent deletes become table scans. 4. Add the constraint in a non-validating mode where the engine supports it, so new writes are checked immediately without a long blocking validation, then validate the history separately. After this, one whole class of defect is closed permanently and every future writer is bound by it. ## Step 5: sequence the structural migrations Structural changes - replacing a polymorphic association, decomposing a shared lookup table, splitting a god table - are multi-week programmes. Run **one at a time to completion**. The reason is not capacity but risk: each leaves the system in a dual-shape state, and overlapping dual states multiply the reconciliation surface and the number of ways a rollback can be wrong. Pick the first one for a combination of highest ranked severity and a scope small enough to finish, so the organisation sees a completed migration and learns the pattern. ## The expand-and-contract mechanic 1. **Expand** - create the new structure alongside the old. Purely additive, deployable at any time, invisible to readers. 2. **Dual-write** - every write path maintains both shapes in the same transaction. This is where hidden writers surface, and finding them is itself valuable output. 3. **Backfill** - copy history in bounded batches with throttling, resumable from a cursor, never one large transaction. 4. **Reconcile** - run a continuous comparison of old versus new and alert on divergence. Do not proceed while divergence is non-zero; a non-zero count almost always means an unmigrated write path. 5. **Migrate readers** - move consumers one at a time behind a flag, comparing results where feasible, starting with the least critical. 6. **Stop writing the old shape**, and leave it readable for a defined period as the rollback. 7. **Contract** - drop the old columns or tables. Separate, deliberate, last, and irreversible. The discipline that matters is that every step before the last is reversible, and the last one only happens after a quiet period with no reader activity on the old shape. ## Step 6: decide what not to fix Explicit non-goals are part of the plan. Leave a defect alone when the subsystem is being retired inside the migration horizon, when the blast radius of the change exceeds the cost of the defect, or when the pattern is contained behind a stable interface and produces no bad data. Record the decision and the reason, so the next owner inherits a judgement rather than an oversight - otherwise the same analysis is redone annually. ## Step 7: make the improvement permanent The schema will drift back unless the guarantees are mechanical: constraints rather than conventions, a review step that flags new tables without foreign keys, and a periodic report of orphan counts and unconstrained relationships so regressions are visible. Anything enforced only by a wiki page reverts. ## What separates a strong answer Sequencing and evidence. Weak answers list the anti-patterns and how to fix each in isolation. Strong answers rank by data-corruption risk, insist on stopping the bleeding before restructuring, run one structural migration at a time, keep every step reversible until the final drop, name what will deliberately not be fixed, and put a mechanism in place to prevent regression.
- Why run structural migrations one at a time rather than in parallel to finish sooner?Each migration leaves the system holding two shapes at once, with dual-write paths and reconciliation to maintain. Overlapping those states multiplies the surfaces that can diverge and makes rollback ambiguous, because a failure may involve either or both migrations. Finishing one also proves the mechanics on a smaller blast radius and gives the team a pattern to reuse.
- How do you know it is safe to drop the old structure at the end of a migration?Reader traffic on the old shape must be observably zero for a defined quiet period - verified through query statistics or logging, not assumption - and reconciliation between old and new must have been at zero divergence throughout. The drop is the only irreversible step, so it happens separately from the reader cutover, well after it, and never in the same deployment.
- What stops the schema drifting back into the same anti-patterns after the cleanup?Only mechanical guarantees. Constraints enforce invariants against every writer, a review check flags new tables lacking foreign keys, and a scheduled report on orphan counts and unconstrained relationships makes regressions visible. Conventions recorded only in documentation reliably decay as the team changes.
saying these in an interview costs you the question
- Prioritising by which pattern is most offensive rather than by measured harm
- Starting several structural migrations at once and finishing none
- Dropping old columns in the same deployment that switches readers over
- Adding constraints to dirty data and being surprised the migration fails or locks
- Treating a wiki convention as sufficient to prevent the pattern recurring