Review modeling patterns: polymorphism, EAV and soft delete

DecisionCore4 min read20 min hands-on#sql#data-modeling

The idea in 30 seconds

Three patterns appear in almost every codebase: one table that points at several kinds of thing, a generic attribute/value table, and rows marked deleted instead of removed. Each is convenient and each quietly gives up something the database would otherwise guarantee. Know what you give up, and the alternatives that keep it.

Helps to know first: Constraints and data integrity, Joins and relational querying

The situation

The shop wants three features: comments on both products and orders, flexible product specifications (screen size, capacity, refresh rate), and the ability to close a customer account without losing their order history. An AI assistant proposes all three at once.

Review taskComments, product attributes and account deletion

Pull request: “Flexible schema for comments, specs and soft delete.”

CREATE TABLE comments (
  id                integer PRIMARY KEY,
  commentable_type  text,      -- 'product' or 'order'
  commentable_id    integer,   -- id in that table
  body              text
);

CREATE TABLE product_attributes (
  product_id  integer,
  attribute   text,            -- 'size_inches', 'refresh_hz', ...
  value       text
);

ALTER TABLE customers ADD COLUMN deleted_at timestamptz;   -- set instead of DELETE

Would you approve it? Write the review you would leave.

Your call

Model review

Request changes. All three work on day one and lose a guarantee the database could have kept. We tried each on the practice data.

1. comments can point at things that do not exist. A foreign key references one table, so commentable_id cannot have one. Our test accepted a comment on order 999, which does not exist, and one whose type was misspelled 'prodcut'. Both are orphans no query will ever find. Instead, give each target its own nullable foreign key and require exactly one:

CREATE TABLE comments (
  id          integer PRIMARY KEY,
  product_id  integer REFERENCES products (id),
  order_id    integer REFERENCES orders (id),
  body        text NOT NULL,
  CHECK (num_nonnulls(product_id, order_id) = 1)
);

We tested this version: a comment on order 999 fails the foreign key, and a comment on both targets or on neither fails the check. (With many target types, separate tables such as product_comments and order_comments are often cleaner.)

2. product_attributes stores every value as text with no rules. Our test stored refresh_hz = 'fast' next to 144, and the query value::int >= 120 then failed outright: invalid input syntax for type integer: "fast". Nothing enforces types, required attributes or allowed names, and every query needs a self-join or pivot per attribute. Prefer real columns for attributes you know and query, or a typed jsonb column for genuinely open-ended specs (see JSON and semi-structured data).

3. deleted_at without other changes breaks uniqueness and every query. With UNIQUE (email) still in place, a customer who closed their account can never sign up again: our test re-insert of the same email failed with a duplicate key error. Make uniqueness apply only to live rows with a partial unique index:

CREATE UNIQUE INDEX customers_live_email ON customers (email) WHERE deleted_at IS NULL;

And every existing query that reads customers must now add WHERE deleted_at IS NULL, or deleted customers reappear in lists, counts and emails. Consider a view of live customers, or moving closed accounts to an archive table, before choosing soft delete.

Compare it with yours. Did your review catch the main problems?

Choosing a pattern

Need	Pattern	Keeps	Gives up
A row belongs to one of several kinds of parent	One FK column per kind + `CHECK (num_nonnulls(...) = 1)`	Referential integrity	A column per kind
	`type` + `id` columns (polymorphic)	Flexibility	Every foreign key guarantee
Attributes that vary by product	Real columns	Types, constraints, simple queries	A migration per new attribute
	`jsonb` column	Flexibility, one row per product	Easy constraints (needs explicit checks)
	EAV table	Unlimited flexibility	Types, constraints and readable queries
Hide records without destroying history	`deleted_at` + partial unique indexes	History in place	A filter in every query
	Archive table	Clean live table	A move step, and joins for history
A fixed set of values	`CHECK (status IN (...))`	Simplicity	Changing the set needs a migration
	Lookup table + foreign key	Values with extra data (labels, ordering)	One more join

Check yourself

Why can’t a polymorphic commentable_id column have a foreign key?
Show answer
A foreign key references exactly one table, but the column’s meaning depends on another column’s value. Nothing checks that the referenced row exists.
I could explain this without looking
A soft-deleted customer cannot sign up again with the same email. Why, and how do you fix it?
Show answer
The unique constraint still counts the deleted row. Replace it with a partial unique index ON customers (email) WHERE deleted_at IS NULL, so uniqueness applies only to live rows.
I could explain this without looking
Name two things an EAV table gives up compared with real columns.
Show answer
Typed values (everything is text), and constraints such as required or unique attributes. Queries also need self-joins or pivots for every attribute.
I could explain this without looking

Sources

Where each claim on this page comes from.

PostgreSQL documentation: 5.5 Constraints

Primary· §5.5.1 Check Constraints, §5.5.5 Foreign Keys

A foreign key column references exactly one table; a check constraint can require any condition across columns.

PostgreSQL documentation: 9.2 Comparison Functions and Operators

Primary· Table 9.3 Comparison Functions

num_nonnulls returns the number of non-null arguments.

PostgreSQL documentation: 11.8 Partial Indexes

Primary· §11.8, Example 11.3

A unique index can be made partial with a WHERE clause, so uniqueness applies only to the rows matching it.

The situation

Choosing a pattern

Check yourself

Sources

Go further