What EAV Looks Like
The entity attribute value model stores data as triples: an entity id, an attribute name, and a value. Instead of a products table with typed columns, you get one tall table where each property is a separate row. It promises infinite flexibility: add an attribute without changing the schema.
Why It Is Tempting
- New attributes need no migration.
- It models sparse or user defined fields easily.
Why It Hurts
- No type safety: values are stored as text, so the database cannot enforce that a price is numeric.
- No constraints: foreign keys, not null, and uniqueness on attributes are impractical.
- Painful queries: fetching an entity with ten attributes requires ten self joins or a pivot.
- Poor performance: the optimizer cannot reason about a generic value column.
Better Alternatives
- Use a JSON column for genuinely flexible data while keeping core columns typed.
- Use proper columns for known attributes and reserve flexibility for the truly dynamic tail.
Key idea
EAV buys schema flexibility but sacrifices types, constraints, and efficient queries, so prefer typed columns plus a JSON column for the dynamic tail.