Entity-Attribute-Value Models: Uses and Failure Modes

Learning outcomes

The Entity-Attribute-Value (EAV) model stores attributes as rows rather than columns. It can support highly dynamic metadata, but it often creates weak typing, difficult constraints, complex queries, and poor discoverability. EAV is neither always wrong nor a default solution for flexibility.

Basic EAV shape

EntityAttributeValue(  entity_id,  attribute_code,  value_text)

Instead of:

Part(  weight_kg,  max_pressure_bar,  color)

Example rows

entity  attribute          value101     weight_kg          4.2101     color              red102     max_pressure_bar   20102     manufacturer_code  ACME

Why EAV is attractive

• new attributes require no table DDL;
• different entity types can carry different attributes;
• sparse data uses rows only when values exist;
• metadata can drive forms dynamically.

The type problem

If every value is text:

"4.2""red""2026-08-10""true"

the database loses strong type semantics.

Typed EAV variants

value_textvalue_numbervalue_datevalue_boolean

can restore some typing, but now each row must use exactly one value column and queries become more complex.

Constraint problem

How do you express:

max_pressure_bar > 0serial_number UNIQUE per manufacturertemperature_c BETWEEN -50 AND 300

when attributes are rows with metadata-defined meaning?

Required attributes

A normal NOT NULL column enforces presence directly. In EAV, “every Pump requires max_pressure_bar” usually requires metadata plus cross-row validation.

Uniqueness

“Every Part has a unique SKU” is easy as a relational column and awkward if SKU is an EAV attribute.

Query complexity

Find Parts where color=red and weight>4:

JOIN EAV once for colorJOIN EAV again for weightfilter/cast both values

Simple predicates become self-joins or conditional aggregation.

Index complexity

Indexes may need combinations such as:

(attribute_code, value_text)(attribute_code, value_number)

but data distributions differ wildly across attributes.

Statistics problem

Optimizer statistics on a generic value column mix unrelated domains. “red,” “blue,” dates, product codes, and names may share one storage column, reducing estimation quality.

Metadata table

AttributeDefinition(  attribute_code,  data_type,  required_flag,  unit_code,  validation_rule)

helps govern EAV but does not recreate all benefits of normal DDL automatically.

Legitimate EAV niches

• user-defined custom fields;
• very sparse scientific/clinical observations;
• metadata repositories;
• dynamic survey answers;
• systems where attribute definitions are themselves primary business data.

Bad EAV use

Core stable attributes such as:

customer_idwork_order_numberstatus_codeopened_atsku

should not be moved into EAV merely to avoid migrations.

EAV versus JSON

EAV versus subtype tables

If there are only a few known Part families with stable fields, subtype tables are often clearer:

PumpPart(part_id, max_pressure_bar, flow_rate)CablePart(part_id, conductor_count, gauge)

Hybrid approach

Keep stable core columns relational and allow only explicitly “custom” attributes in EAV or JSON:

Part core columnsPartCustomAttribute values

WorkshopHub decision

Practice: EAV or not?

Summary and next lesson

EAV is appropriate when attributes themselves are dynamic business metadata, but it is costly for stable, constrained core data. JSON is often simpler for document-shaped flexibility; subtype tables are better for a small number of known variants. The final lesson combines these ideas into hybrid relational/document designs.

References

• Bill Karwin, SQL Antipatterns.
• Martin Fowler, writings on dynamic properties and metadata.
• Database documentation on JSON and generated columns.