You have a supertype entity with several subtypes that share common attributes and each add their own — for example Payment with CardPayment, BankTransfer and Voucher. What are the standard ways to map that into relational tables, and what does each one cost?

## The problem Entity-relationship modelling lets you say "a Payment is either a CardPayment, a BankTransfer or a Voucher" — a supertype with disjoint subtypes, each sharing some attributes (amount, currency, created_at, status) and adding its own (card token and last four digits; IBAN and bank name; voucher code and campaign). Relational tables have no inheritance, so the model must be flattened. Three flattenings are standard, and each moves cost between reads, writes, storage and constraint enforcement. ## Single table (table-per-hierarchy) One table, every column from every subtype, plus a **discriminator** column (`payment_type`) that names which subtype a row is. Reading any payment, or all payments regardless of type, is one table access with no join — the fastest possible read and the easiest to index across the whole hierarchy. Inserts touch one table, so there is no multi-table write to keep atomic. Foreign keys from other tables point at one place. The cost is constraint expressiveness. `card_token` cannot be NOT NULL, because bank transfers have no card token. So the schema no longer states which columns are mandatory for which type, and nothing stops a bank transfer row carrying a card token. The fix is conditional CHECK constraints — for each subtype, assert that its required columns are present and the other subtypes' columns are NULL when the discriminator says so. These are perfectly enforceable and worth writing, but they are hand-maintained and grow combinatorially with subtypes. Secondary costs: the table becomes wide and sparse; rows carry many NULLs (storage impact depends on the engine's NULL encoding, often small but not free); and every new subtype is a DDL change adding nullable columns to a table other subtypes also live in, which in a large table can be a nontrivial migration. ## Class table (joined / table-per-subclass) A parent table holds the identity and the shared attributes. Each subtype gets its own table whose primary key *is* a foreign key to the parent's primary key — a one-to-one identifying relationship. A card payment is one row in `payment` plus one row in `card_payment` sharing the same id. This is the normalized answer. Subtype columns live only where they apply, so they can be NOT NULL and the schema documents the model. Adding a subtype adds a table and touches nothing existing. Other tables can foreign-key to `payment` and reference any payment regardless of type, while a table that must reference specifically a card payment can foreign-key to `card_payment`. Indexes on subtype columns cover only the rows of that subtype, so they stay small. Costs: reading a full instance is a join, and a polymorphic read that needs subtype detail is a join to every subtype table (typically LEFT JOINs, or a UNION ALL of per-subtype queries). Writes touch two tables and must be in one transaction. And the model's real weakness is that the database does not, by itself, enforce **completeness and disjointness**: nothing stops a parent row having no subtype row (an incomplete payment) or rows in two subtype tables at once. You can approximate the guarantee by carrying the discriminator in the parent, including it in the subtype tables' foreign key as a composite `(id, type)` with a CHECK pinning the type per table — that makes double membership impossible. Full "every parent has exactly one child" is only enforceable with deferred constraints or triggers. ## Concrete table per subtype No parent table at all; each subtype table repeats the shared columns. Every single-subtype query is one table with no join, and each table is narrow and dense with no NULLs. Costs are structural. Shared attributes are duplicated in the schema, so a change to a common column is a change in N tables. There is no shared identity space, so identifiers must be unique across tables by convention (a shared sequence or UUIDs), and nothing else in the schema can foreign-key to "any payment". Every cross-subtype query — "total payments today", "find payment by id" — is a UNION ALL over all tables and must be edited whenever a subtype is added. In practice this pattern is defensible only when subtypes are truly independent, rarely queried together, and share little. ## Choosing Ask in order: 1. **How often do you query polymorphically?** "All payments" on every dashboard pushes toward single table or class table, and rules out concrete tables. 2. **How much do subtypes differ?** Two subtypes with one extra column each: single table, trivially. Five subtypes with ten distinct columns each: class table, or the single table becomes an unreadable 60-column sheet. 3. **How much do you need database-enforced constraints?** If "a card payment must have a token" must be guaranteed at the storage layer without hand-written CHECKs, class table wins. 4. **How volatile is the subtype set?** Frequently added subtypes favour class tables — new table, no migration on hot existing tables. 5. **Do other tables reference the supertype?** If yes, concrete tables are effectively out. Hybrids are legitimate: a single table for a mostly-uniform hierarchy with one exotic subtype split out; or a parent table plus subtype tables where the very common subtype's few columns are inlined into the parent. Note also that this is a storage decision independent of any object mapper's vocabulary — the trade-offs are the same whether the code is hand-written SQL or generated, and the schema should be chosen from query patterns and constraint needs rather than from a framework default.