What is the Entity-Attribute-Value (EAV) modeling pattern in a relational database, and what does it cost you compared with ordinary typed columns?
answer
- Attributes become rows, not columns
- One generic value column = no types, no FK, no NOT NULL
- Missing row cannot be NOT NULL-checked
- Statistics blended across all attributes
- Read = pivot, write = N upserts
basics
~20 sEAV stores each property as a row (entity id, attribute name, value) instead of as a column. It buys schema flexibility without DDL, but you give up data types, NOT NULL, foreign keys, CHECK constraints and useful optimizer statistics, and every read needs pivoting.
solid answer
~50 sEAV replaces one-column-per-attribute with one-row-per-attribute: a table of (entity_id, attribute_id, value), usually plus a catalog table describing the attributes. Teams reach for it when attributes are user-defined, sparse, or unknown at design time, because adding a property becomes an INSERT instead of a migration. The cost is that you opt out of nearly everything the engine normally enforces. The value column has one generic type, so there is no per-attribute type checking; a missing required property is just a missing row, so NOT NULL cannot express it; a value that logically references another table cannot be a foreign key; per-attribute CHECK rules cannot be declared. All of that moves into application code, where it is enforced inconsistently. There is a planner cost too: statistics describe the whole value column, not each attribute, so estimates for predicates on a specific attribute are guesses and join orders go wrong. And reads need self-joins or conditional aggregation to reassemble one row per entity.
code
sql · 19 linesCREATE TABLE attribute (
attribute_id bigint PRIMARY KEY,
code text NOT NULL UNIQUE,
datatype text NOT NULL,
is_required boolean NOT NULL DEFAULT false
);
CREATE TABLE entity_value (
entity_id bigint NOT NULL REFERENCES product(id),
attribute_id bigint NOT NULL REFERENCES attribute(attribute_id),
value_text text,
value_number numeric,
value_date date,
PRIMARY KEY (entity_id, attribute_id),
CONSTRAINT one_value CHECK (
(value_text IS NOT NULL)::int
+ (value_number IS NOT NULL)::int
+ (value_date IS NOT NULL)::int = 1)
);go deeper
Be able to draw the three-column table, say why someone would use it (attributes not known up front), and name two things you lose: types and constraints.
Explain the concrete losses one by one - type checking, NOT NULL, foreign keys, CHECK - and describe how a read pivots rows back into columns and why that is costly.
Add the optimizer angle (statistics on a blended value column, bad cardinality estimates), row-count growth, write amplification, and the mitigations: typed value columns, an attribute catalog with one write gate, materialised pivots, promoting hot attributes to real columns.
Frame it as a deliberate trade of declarative integrity and query planning for runtime flexibility, scoped to the part of the model users truly own; discuss where the enforcement boundary moves and what organisational cost that carries over years.
## The shape A normal relational table has one column per attribute, and each column carries a declared type plus constraints. EAV turns attributes into data. The core table holds: - `entity_id` - which object the fact belongs to - `attribute_id` or attribute name - which property this is - `value` - the property value, in a generic type Real implementations usually add an attribute catalog (`attribute_id`, name, declared datatype, required flag, allowed values) and sometimes split the value into typed columns (`value_text`, `value_number`, `value_date`) with a rule that exactly one is non-null. ## Why people reach for it - Attributes are defined by end users or administrators at runtime, not by developers at design time. - The attribute set is huge and sparse: thousands of possible properties, a handful per object (clinical observations, product catalogs spanning many categories). - The organisation treats DDL as expensive or risky, so "add a row" feels safer than "add a column". Those motivations are real. The mistake is applying EAV to attributes the developers actually know about. ## What the engine stops doing for you **Types.** A single `value` column must hold dates, numbers and text. If it is text, `'12'` sorts before `'9'`, arithmetic requires casts, and a typo can put `'N/A'` where a number belongs. Casting at query time also blocks straightforward index use and can raise conversion errors depending on the order the planner evaluates predicates. **Required-ness.** "Every product has a price" is not expressible: an absent price is simply an absent row. NOT NULL only constrains rows that exist. Enforcement moves to application code or triggers. **Foreign keys.** If an attribute's value is really a reference to `supplier_id`, it cannot be declared as a foreign key, because the same column also holds free text and dates. Referential integrity becomes convention. **Domain rules.** Per-attribute CHECK constraints, enums and uniqueness ("only one primary email per user") have nowhere to live except code. **Statistics and estimation.** The optimizer collects distribution statistics per column. With EAV, every attribute shares one column, so the histogram is a blend of unrelated domains. A predicate on a rare attribute and a predicate on a very common one look identical to the planner, cardinality estimates are wrong, and multi-attribute queries get bad join orders and bad access-path choices. ## Query and volume cost Reassembling an entity means turning N rows into one row of N columns: either N self-joins, or a `GROUP BY entity_id` with conditional aggregation. Filtering on two attributes means intersecting two row sets, and each additional predicate is another pass over the value table. Row counts explode: one million entities with thirty attributes is thirty million rows, plus index entries. Writes are equally awkward: creating an object is N inserts, editing it is N upserts, and there is no single row whose existence means "this object is valid". The damage spreads outside the database as well. ORMs cannot map it naturally, reporting and BI tools cannot consume it, and every consumer reimplements the pivot logic. ## When it is defensible - Genuinely open-ended, sparse, user-defined attributes that you never filter, sort or aggregate on in bulk. - Measurement domains where the "attribute" is really a dimension of a fact: `(subject_id, metric_id, measured_at, numeric_value)`. Note that this version is typed, has a real foreign key to a metric table, and is queried per metric rather than pivoted - it is EAV's disciplined cousin, not the anti-pattern. - Low-volume metadata where the cost never materialises. ## Mitigations when you are stuck with it 1. Typed value columns plus a CHECK that exactly one is non-null. 2. A real attribute catalog with datatype, required flag and allowed values, enforced by one write gate rather than by every caller. 3. Keep core attributes as real columns and let EAV hold only the customer-defined long tail. 4. Index `(attribute_id, value, entity_id)` so single-attribute lookups are covered. 5. Materialise a pivoted view or table for reads and reporting. 6. Promote frequently filtered attributes to real columns once they prove themselves. The judgement an interviewer wants is that EAV is a deliberate trade of integrity and query efficiency for runtime schema flexibility, taken only for the part of the model that genuinely needs it.
- How would you enforce that a required attribute is always present in an EAV design?You cannot do it with NOT NULL, because absence is a missing row rather than a null column. The options are a trigger or constraint trigger that counts required attributes on write, a deferred check at transaction commit, or a single application-level write gate that is the only path allowed to insert values. All three are weaker than a column-level NOT NULL because they can be bypassed by direct SQL or by a second code path.
- Why does an EAV table give the query optimizer trouble even when it is well indexed?Statistics are per column, and every attribute shares one value column, so the histogram mixes unrelated domains. The planner therefore cannot tell that 'status = active' matches half the entities while 'serial_number = X' matches one. Estimates are off by orders of magnitude, which produces wrong join orders and wrong nested-loop versus hash decisions once several attributes are filtered at once.
It is like replacing a form with printed fields by a shoebox of index cards, each card reading 'field name: value'. Adding a new field is free; validating and reading a whole form back means sorting the whole box.
saying these in an interview costs you the question
- Claiming EAV is 'more normalized' - it is orthogonal to normalization and actually destroys domain integrity
- Saying you can enforce a required attribute with NOT NULL on the value column
- Assuming indexes make EAV perform like real columns, ignoring the pivot and estimation cost
- Treating it as the default answer to 'the schema might change later' rather than to genuinely user-defined attributes
- Not knowing that a foreign key cannot be declared from a polymorphic value column