skip to content

A production table maps a supertype and its subtypes into one wide table with a type discriminator; it now has 40 nullable columns, 9 subtypes, and every new subtype requires altering that table. How would you evaluate whether to restructure it into a parent table with per-subtype child tables, and what would the migration and the resulting query costs look like?

level: seniorimportance: should knowfreq 34%

answer

  1. classify reads: shared / subtype / polymorphic-with-detail
  2. sparsity per column, incidents from wrong NULLs
  3. expand → backfill → dual-write → verify → cut → drop
  4. old wide table can BE the parent
  5. composite (id, type) FK enforces disjointness

basics

~20 s

Measure first: which queries are polymorphic, which are subtype-specific, how sparse the columns are, and how often subtypes are added. Restructure to a parent plus child tables when subtype columns dominate and integrity matters. Migrate by backfilling child tables from the wide table, dual-writing, cutting reads over, then dropping columns. Polymorphic reads gain a join; subtype reads get narrower and better-indexed.

solid answer

~60 s

**Evaluate with evidence.** Pull the real query mix: what fraction of reads are "all instances regardless of type" versus "one subtype with its own columns"? Check column sparsity per subtype, how many bugs or data-quality incidents trace to a column being NULL when it should not be, how big the CHECK constraint has grown (or whether one exists at all), and the cost/lock profile of the last few `ALTER TABLE`s. **Restructure when**: subtype-specific columns outnumber shared ones, subtypes are added regularly, and "required per subtype" is currently enforced only in application code. **Stay put when**: the hot path is polymorphic reads by id, the extra join is measurable in your latency budget, and the CHECK constraints are actually maintained. **Migration**, expand/contract: add parent and child tables; backfill child rows from the wide table in batches; dual-write both shapes; move reads subtype by subtype; verify with a reconciliation query; then stop writing and drop the old columns. Every step is reversible until the drop. **After**: subtype reads touch a narrow table with small indexes; polymorphic reads pay one join per subtype table needed, or none if they only need shared columns — which is often the majority of them.

code

sql · 10 lines
sql
ALTER TABLE payment ADD CONSTRAINT payment_id_type_uq UNIQUE (id, payment_type);

CREATE TABLE card_payment (
    id           BIGINT PRIMARY KEY,
    payment_type VARCHAR(20) NOT NULL,
    card_token   VARCHAR(64) NOT NULL,
    CONSTRAINT card_payment_type_fixed CHECK (payment_type = 'CARD'),
    CONSTRAINT card_payment_parent_fk
        FOREIGN KEY (id, payment_type) REFERENCES payment(id, payment_type)
);

go deeper

for a junior

Recognize the symptoms — many nullable columns, a discriminator, subtype rules living in application code — and know the alternative shape exists.

for a middle

Describe the target schema with shared primary keys and state which queries gain a join and which get narrower.

for a senior

Drive it from measurements, design the expand/backfill/dual-write/verify/contract migration, and enforce disjointness with a composite (id, type) foreign key.

for a principal

Decide where invariants should live and whether the migration risk is worth it at all; propose a hybrid split and set the evidence bar for saying no.

## Frame it as evidence, not taste A 40-column, 9-subtype single table is a smell, not a verdict. The single-table mapping is genuinely the fastest read shape, and a restructure is a multi-week migration on live data. So the first job is to establish that the pain is real and that the alternative removes it. **Gather:** - **Query mix.** From the statement statistics or the application's query log, classify reads as (a) shared columns only, (b) one specific subtype including its own columns, (c) polymorphic *and* needing subtype columns. Category (c) is the only one that gets meaningfully worse after a split — and it is usually the smallest. - **Sparsity.** Per column, what fraction of rows are non-NULL? Nine subtypes over 40 columns typically means each row uses a third of them. High sparsity means most of the table's width is dead weight on every scan and every buffer page. - **Integrity incidents.** How many production defects were "the field was NULL for a type that requires it" or "a row of type A carried type B's data"? If the answer is nonzero and there is no discriminator-conditioned CHECK, the schema is not enforcing the model and the application is — badly. - **DDL pain.** Time and lock behaviour of the last few column additions. On a large table, adding a nullable column is usually cheap on modern engines, but adding one with a default, or backfilling it, is not — and every subtype addition touches a table all nine subtypes read from. - **Index count.** Wide tables accumulate partial or filtered indexes per subtype. Many indexes on one hot table means every write maintains all of them. ## What the target looks like A parent table with identity, the shared attributes, and the discriminator; one child table per subtype whose primary key is also a foreign key to the parent. Subtype columns become NOT NULL where the model says mandatory. To keep disjointness enforceable, keep the discriminator in the parent, add a `type` column to each child pinned by a CHECK to that child's single value, and make the child's foreign key composite over `(id, type)` against a parent unique key on `(id, type)`. That makes it structurally impossible for one parent row to have children in two subtype tables. Completeness — every parent has exactly one child — remains a trigger or deferred-constraint job, or an accepted invariant enforced by the single write path. ## The migration Use expand/contract so every stage is reversible and no stage requires a coordinated deploy: 1. **Expand schema.** Create the parent-shaped view of reality without removing anything: add the child tables. The existing wide table becomes the parent table itself if the shared columns already live there — often you can keep the same table and just move subtype columns out, which avoids re-keying and preserves every existing foreign key pointing at it. That is usually the right call: do not create a new parent table if the old one can *be* the parent. 2. **Backfill.** Insert child rows in batches keyed by primary-key ranges, one subtype at a time, so each batch is short and does not hold a long transaction. Track progress in a control table so it is resumable. 3. **Dual-write.** Change the write path to populate both the old columns and the new child rows in the same transaction. Keep it running long enough to cover the slowest write path in the system (batch jobs, admin tools, imports — these are the ones teams forget). 4. **Verify.** A reconciliation query per subtype comparing old columns to child rows, run repeatedly until it reports zero mismatches over a full business cycle. Also check the inverse: parent rows with no child, and children with no parent. 5. **Cut reads.** Move read paths subtype by subtype, behind a flag, watching latency for the polymorphic queries specifically. 6. **Contract.** Stop writing the old columns, wait, then drop them. Dropping is the only irreversible step, so it comes last and separately. ## Query costs after - **Shared-columns-only reads** (list views, status checks, counts by type) get *faster*: the parent table is now narrow, so more rows fit per page and scans and index-only access improve. - **Single-subtype reads with subtype columns** cost one join on the primary key — an index lookup per row, or a hash/merge join in bulk. In exchange the subtype table is narrow and dense, and its indexes cover only that subtype's rows instead of being filtered indexes over the whole table. - **Polymorphic reads needing subtype columns** are the loser: either N LEFT JOINs, or a UNION ALL of per-subtype queries. If this is a hot path — an admin "show everything with all details" screen — either keep it paginated so N joins run over a small page, or accept two round trips (fetch the page from the parent, then fetch subtype details for the ids grouped by type). - **Writes** now touch two tables in one transaction instead of one row. Slightly more work, no correctness issue, and each row is smaller. ## When to say no If the query mix is dominated by polymorphic detail reads on a latency-critical path, if the subtype set is stable, and if a maintained discriminator-conditioned CHECK already enforces the model, then the wide table is doing its job and the restructure buys tidiness at the price of a risky migration. A cheaper middle path exists: split out only the two or three subtypes with the most private columns, leaving the uniform majority inline. Hybrids are not a failure of nerve — they put the join exactly where the sparsity is. The deciding question is not "which pattern is correct" but "where do this system's invariants live, and is the current schema able to hold them". If the answer is "in application code, and it keeps failing", restructure. If the answer is "in constraints that work", leave it alone.

  • Which read pattern gets worse after splitting subtype columns out of the wide table, and how do you keep it acceptable?
    Polymorphic reads that need subtype-specific detail — 'show every instance with all its fields' — because they now require a LEFT JOIN to each subtype table or a UNION ALL across them. Keep it acceptable by paginating so the joins run over a small page rather than the whole table, or by splitting it into two steps: fetch the page from the parent, then fetch details per subtype for just those ids grouped by type. Reads that only need the shared columns actually get faster, because the parent table is now narrow.
  • Why is dual-writing worth the extra complexity instead of a single cutover deploy?
    Because it makes every stage reversible and decouples the data migration from the code deploy. With both shapes populated, reads can move subtype by subtype behind a flag and roll back instantly if latency or correctness regresses, and the backfill can run in resumable batches rather than one long locking transaction. The risk it removes is the one that actually bites: a write path nobody remembered — a batch job, an admin tool, an importer — that would have silently stopped populating the new tables.
  • When would you decline the restructure and keep the wide table?
    When the hot path is polymorphic reads needing subtype detail on a latency-sensitive endpoint, the subtype set is stable so ALTER pain is hypothetical, and a discriminator-conditioned CHECK constraint is present and maintained so the model's invariants really are enforced by the database. In that situation the restructure buys tidiness and pays with a multi-week migration on live data. A hybrid — extracting only the two or three subtypes with the most private columns — captures most of the benefit for a fraction of the risk.

saying these in an interview costs you the question

  • Proposing the restructure from schema aesthetics without measuring the query mix
  • Building a brand-new parent table when the existing wide table could simply become the parent, breaking existing foreign keys
  • Doing a single big-bang cutover instead of backfill plus dual-write
  • Assuming the join always makes reads slower, ignoring that the narrower parent speeds up shared-column reads
  • Dropping the old columns in the same change that cuts reads over, leaving no rollback

context

resolving links…