Case Study 2 — The Payment Table With Forty Nullable Columns

Choosing an inheritance mapping is a real design decision, not a default. A team mapped several payment subtypes into one giant table and spent two years fighting nullable columns and invalid data. The fix was picking the mapping that matched their needs.

Background

A platform accepted payments by several methods, each with its own data: card (last 4 digits, expiry, network), bank transfer (routing, account, bank name), gift card (card code, balance applied), and crypto (wallet address, chain, tx hash). The team mapped all of them into a single payments table — single-table inheritance — with a method column and every subtype's fields as columns:

CREATE TABLE payments (
    payment_id integer PRIMARY KEY,
    order_id   integer NOT NULL REFERENCES orders(order_id),
    method     text NOT NULL,
    amount     numeric(10,2) NOT NULL,
    -- card fields:
    card_last4 text, card_expiry text, card_network text,
    -- bank fields:
    bank_routing text, bank_account text, bank_name text,
    -- gift card fields:
    gift_code text, gift_balance_applied numeric,
    -- crypto fields:
    wallet_address text, chain text, tx_hash text
    -- ...and it kept growing
);

It shipped fast. Then it festered.

What went wrong

Mostly-null rows. A card payment had values in three columns and NULL in the other ~ten. Every row was 75% empty. As new methods were added, the table grew ever wider and emptier.

No integrity per subtype. The database couldn't enforce "a card payment must have card_last4" or "a bank transfer must have bank_routing," because those columns had to be nullable (other methods don't use them). So bugs wrote card payments with null card data and bank transfers with crypto fields populated, and nothing stopped it. Data-quality issues piled up.

Invalid combinations. Nothing prevented a row with method = 'card' that also had wallet_address and bank_routing set — a nonsensical Frankenstein payment. The "shape" of each method lived only in developers' heads.

Wide, confusing queries. Every query and every form had to know which columns mattered for which method. New developers were baffled by the forty-column table.

The team tried to patch it with a sprawl of CHECK constraints keyed off method (CHECK (method <> 'card' OR card_last4 IS NOT NULL), ×many), which helped but was unwieldy and easy to forget for new methods.

The fix: table-per-subclass

Because the subtypes had substantial distinct attributes and integrity mattered, the right mapping was table-per-subclass: a base payments table for the common fields, and one table per method for its specific fields, linked 1:1.

CREATE TABLE payments (
    payment_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id   integer NOT NULL REFERENCES orders(order_id),
    method     text NOT NULL CHECK (method IN ('card','bank','gift','crypto')),
    amount     numeric(10,2) NOT NULL CHECK (amount >= 0),
    paid_at    timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE card_payments (
    payment_id  integer PRIMARY KEY REFERENCES payments(payment_id) ON DELETE CASCADE,
    card_last4  text NOT NULL,            -- NOW required, where it belongs
    card_expiry text NOT NULL,
    card_network text NOT NULL
);

CREATE TABLE bank_payments (
    payment_id   integer PRIMARY KEY REFERENCES payments(payment_id) ON DELETE CASCADE,
    bank_routing text NOT NULL,
    bank_account text NOT NULL,
    bank_name    text NOT NULL
);
-- ...gift_payments, crypto_payments similarly...

Now:

  • No wasted nulls — each subtype table holds only its own (required) columns.
  • Integrity per subtype — card_payments.card_last4 is NOT NULL, enforced. A card payment without card data is impossible.
  • No invalid combinations — a card payment's data lives in card_payments; there's no place to accidentally store crypto fields on it.
  • Adding a method is clean — a new subtype table, no ALTER to a giant shared table, no new nullable columns for everyone else.

The trade-off they accepted: querying a full payment of a known method needs a join (payments JOIN card_payments), and "all payments with their method-specific details" needs per-type handling. For their workload — payments are usually processed by known method — that was a fine price for the integrity gained.

When single-table would have been right

The lesson isn't "table-per-subclass is always better." If the subtypes had few distinct attributes (say, each method added one or two fields) and the app mostly queried all payments together, single-table inheritance (one table, method discriminator, a handful of nullable columns) would be simpler and join-free — the right call. Mercado's own payments table is single-table (just a method column, no per-method columns) precisely because its needs are simple. The mapping choice depends on how many distinct attributes the subtypes have and how you query them — there's no universal winner.

The analysis

  1. Inheritance mapping is a deliberate decision. Single-table, table-per-subclass, and table-per-concrete each fit different situations. Defaulting to single-table because it's easiest can produce a forty-column, null-riddled mess.

  2. Many distinct attributes + integrity needs → table-per-subclass. When subtypes differ substantially and you need "card payments must have card data" enforced, splitting tables lets each subtype's columns be NOT NULL where they belong.

  3. Few attributes + query-together → single-table. When subtypes are thin and you usually read all rows together, one table with a discriminator is simpler and avoids joins (Mercado's choice).

  4. The cost of the wrong choice is integrity and clarity. Single-table here meant the database couldn't enforce subtype rules, so bad data accumulated and the table became unreadable. The fix restored enforceable integrity.

  5. Match the mapping to the data and the workload. Count the distinct attributes; consider whether you query subtypes separately or together; weigh joins vs. nulls. Then choose — don't default.

Discussion questions

  1. List the concrete problems single-table inheritance caused here.
  2. Why does table-per-subclass let card_last4 be NOT NULL when single-table couldn't?
  3. What trade-off did the team accept with table-per-subclass, and why was it acceptable for their workload?
  4. Mercado's payments is single-table. Why is that the right choice there but the wrong one here?
  5. ⭐ Give the decision criteria you'd use to choose among the three inheritance mappings for a new subtype situation.