skip to content

A catalog holds 200 kinds of item, and each kind has mostly different properties. What are the relational alternatives to a generic attribute-value table for that, and how do you choose between them?

level: seniorimportance: should knowfreq 45%

answer

  1. Who owns the attribute: developer or customer
  2. Wide nullable table = typed, DDL churn
  3. Parent + child per kind = full integrity, one join
  4. Composite FK (item_id, kind) prevents mismatched subtype
  5. Promote tail attributes to columns when queried

basics

~20 s

Options are: one wide table with many nullable columns; a shared parent table plus one subtype table per kind holding that kind's typed columns; a separate table per kind; a JSON column for the long tail; and attribute-value rows as the last resort. Choose by who defines the properties, how stable the set is, and whether you filter or aggregate across kinds.

solid answer

~60 s

Ranked by how much integrity they keep: 1. **Single wide table with nullable columns** - typed and constrained, trivial to query, NULLs are cheap in most engines. Breaks down when the union of properties is huge or new kinds arrive weekly. 2. **Subtype tables (class-table inheritance)** - shared columns and the identity in a parent table, one child table per kind with that kind's own typed, constrained columns and a foreign key back to the parent. Full integrity, one join per read, and N tables to maintain. Best when kinds are relatively few, stable, and developer-defined. 3. **Table per kind with no parent** - simplest queries per kind, but nothing to point a foreign key at and cross-kind queries need UNION ALL. 4. **JSON column for the long tail** - core queried attributes stay real columns, the rest lives in one document per row, indexed on the paths you actually query. 5. **Attribute-value rows** - only when end users define the attributes at runtime. The deciding questions are: do developers or customers define the properties, how often does the set change, and do you filter, sort or aggregate across kinds?

code

sql · 15 lines
sql
CREATE TABLE item (
  item_id bigint PRIMARY KEY,
  kind    text NOT NULL,
  name    text NOT NULL,
  price   numeric(12,2) NOT NULL CHECK (price >= 0),
  UNIQUE (item_id, kind)
);

CREATE TABLE item_book (
  item_id bigint PRIMARY KEY,
  kind    text NOT NULL DEFAULT 'book' CHECK (kind = 'book'),
  isbn    char(13) NOT NULL UNIQUE,
  pages   int NOT NULL CHECK (pages > 0),
  FOREIGN KEY (item_id, kind) REFERENCES item (item_id, kind)
);

go deeper

for a junior

Name the alternatives - one wide table with nullable columns, a table per kind, or a JSON column - and say that real columns give you types and constraints.

for a middle

Contrast single-table, subtype tables and JSON on typing, constraint enforcement, join cost and DDL churn, and pick one for a concrete catalog.

for a senior

Split shared, queried attributes from the per-kind tail, get the composite-key trick for subtype integrity right, and describe the promotion path when a tail attribute becomes hot.

for a principal

Drive the decision from ownership of the attribute set and from the cross-kind query and reporting contract, and weigh long-run migration and tooling cost across 200 kinds.

## Framing the decision The real question is not "which pattern is best" but "who owns the attribute set and what queries must run across kinds". Two axes decide almost everything: - **Who defines an attribute?** If developers do, a deployment can add a column and every attribute is knowable at design time. If customers do, no deployment can, and you need a runtime-flexible store. - **Do queries span kinds?** "Show all items under 50 dollars, any kind" needs the shared attributes in one place. "Show the resolution of every monitor" only touches one kind. ## Option 1: one wide table, nullable columns Every attribute of every kind is a real, typed column; a kind's irrelevant columns are NULL. This is sometimes called single-table inheritance. Strengths: full typing, CHECK constraints, foreign keys, real statistics per column, no joins, indexes exactly where you need them. In most engines NULLs cost about a bit each in a null bitmap, so sparse rows are not the disaster people assume. Weaknesses: you cannot use NOT NULL for attributes required by only some kinds - it must become a CHECK such as `kind <> 'book' OR isbn IS NOT NULL`. The table becomes wide and self-documenting only via naming conventions. Engines have column-count and row-width limits. If new kinds arrive constantly, you are running DDL constantly. Good fit: a handful to a few dozen kinds with meaningful overlap, developer-defined. ## Option 2: subtype tables (class-table inheritance) A parent table holds identity and the attributes common to all kinds (`item_id`, `kind`, `name`, `price`). Each kind gets its own table keyed by the same `item_id`, holding only its own attributes, all typed and constrained, with a foreign key back to the parent. Strengths: every attribute is typed, NOT NULL works properly within a subtype, foreign keys and CHECKs are natural, and the parent gives a single target for other tables to reference and a single place for cross-kind queries. Statistics are per attribute, so the planner works well. Weaknesses: reading a full item is a join, and reading "the right" child requires either knowing the kind or a chain of outer joins. Two hundred kinds means two hundred tables - a real burden for migrations, ORMs and code generation. Keeping the parent's `kind` column consistent with which child row exists needs care; a common trick is to put `(item_id, kind)` as a unique key on the parent and have each child carry a `kind` column fixed by a CHECK and a composite foreign key, so a `book` row cannot attach to a `monitor` item. Good fit: a moderate number of stable, developer-defined kinds where the per-kind attributes genuinely matter to queries. ## Option 3: table per kind, no parent Each kind is an independent table. Queries per kind are ideal, but there is no common identity: other tables cannot hold a foreign key to "any item", ids may collide, and cross-kind queries are UNION ALL over 200 tables. Usually only right when the kinds truly are separate domains that just happen to share a word. ## Option 4: real columns plus a JSON column The attributes you filter, sort, join and report on stay real columns; everything else goes into one document-valued column per row. You keep row-per-item identity, avoid pivoting, and can index specific paths (via expression or generated-column indexes) or the whole document with an inverted index for containment queries. Weaknesses: nothing inside the document can be a foreign key, constraints only exist as CHECKs over extracted expressions, key names repeat in every row, updates typically rewrite the whole value, and the optimizer estimates path predicates poorly unless the path is materialised as a generated column with statistics. Good fit: a large sparse long tail that is mostly read as a whole rather than queried piecemeal. ## Option 5: attribute-value rows Justified when customers define attributes at runtime and you must be able to filter on an arbitrary one of them without deploying. Everything the pattern costs - typing, required-ness, foreign keys, statistics, pivoting - is the price of that capability. ## A defensible answer for 200 kinds Most catalogs are not uniformly heterogeneous. Extract the attributes shared across kinds (identity, name, price, availability, category) into a real, well-constrained parent table, because those are the ones queries filter and sort on. Then handle the per-kind tail with whichever of subtype tables or a JSON column fits: subtype tables when the kinds are few, stable and constraint-heavy; JSON when the tail is broad, sparse and mostly displayed rather than filtered. Reserve attribute-value rows for attributes a merchant defines through the UI. And build a promotion path: when a tail attribute starts being filtered on in bulk, it graduates to a real column.

  • With subtype tables, how do you stop a row in the book table from attaching to an item whose kind is 'monitor'?
    Put a unique constraint on `(item_id, kind)` in the parent even though `item_id` is already the primary key, then give each child a `kind` column pinned by a CHECK to its own value and a composite foreign key on `(item_id, kind)`. The database then rejects any attempt to attach a book row to a non-book item. Enforcing that exactly one child row exists is harder and usually left to the write path or a deferred constraint trigger.
  • Is a wide table full of NULLs actually wasteful in storage terms?
    Far less than people assume. Mainstream engines record nullability in a per-row null bitmap costing roughly one bit per column, and trailing NULLs may cost nothing at all, so a sparse wide row is not dramatically bigger than a narrow one. The real limits are the engine's maximum column count and row width, index proliferation, and the human cost of a table with 300 loosely related columns.

saying these in an interview costs you the question

  • Jumping straight to attribute-value rows because 'there are too many kinds' without checking who defines the attributes
  • Believing NULL columns cost a full column width of storage each
  • Choosing table-per-kind with no parent and then discovering nothing can hold a foreign key to 'an item'
  • Putting attributes that are filtered and sorted in bulk into the flexible store rather than into real columns
  • Treating the choice as permanent instead of designing a promotion path from flexible storage to real columns

context

resolving links…