EAV, JSON column, wide table or document?
| Dimension | Entity-attribute-value (EAV) | Emergent schema (JSON columns) | Wide tables | Document databases |
|---|---|---|---|---|
| In one line | One row per fact: entity, attribute, value | Known fields as columns, the rest in one JSON column | One column per attribute, mostly NULL | Each record is a self-contained JSON-like document |
| Adding an attribute | Insert a row into the attribute catalog; no DDL | Just write the new key; no DDL | ALTER TABLE ADD COLUMN; in PostgreSQL a nullable column is a metadata change, with no table rewrite | Just write the new field; no DDL |
| Types | Typed value columns or one value table per type, chosen from metadata; often everything ends up as text | JSON’s string, number, boolean and null; dates are strings until you cast them | Any SQL type, per column | BSON types in MongoDB, including dates, 64-bit integers and Decimal128 |
| Constraints | None per attribute in the schema; enforced by triggers that read metadata, or by the application | CHECK constraints on expressions; JSON Schema via MySQL’s JSON_SCHEMA_VALID or PostgreSQL extensions | Ordinary NOT NULL, CHECK and foreign keys on each column | Optional $jsonSchema validation per collection; no foreign keys |
| Indexing | (attribute, value) indexes, one attribute at a time; multi-attribute filters become joins | GIN over the whole column, expression indexes per key, or generated columns; MySQL indexes only generated columns and arrays | A B-tree per column you filter on; SQL Server adds filtered indexes on sparse columns | Any field, including nested fields and arrays (multikey indexes) |
| Query shape | Pivot with one CASE or join per attribute | Operators and casts: props->>'plan', props @> '{...}', JSON path | Plain SELECT col WHERE col = ... | Field paths in find and aggregation; $lookup for joins between collections |
| Planner statistics | Per column, so all attributes share one histogram | None on keys inside the column unless you add an expression index or CREATE STATISTICS | Per column, as for any table | MongoDB races candidate index plans in a short trial; a cost-based ranker covers some queries since 8.3 |
| Sparse data on disk | Only present facts, but each is a full row with IDs and a row header (about 23 bytes in PostgreSQL) | Only present keys, but every row repeats its key names | A NULL costs one bit in PostgreSQL’s null bitmap; SQL Server sparse columns shrink NULLs at extra cost per non-NULL value | Only present fields, but every document repeats its field names (BSON stores each name) |
| Limits | Practically unlimited attributes | Large documents work, but any update locks the whole row; keep them small | 1,600 columns per table in PostgreSQL; 1,024 in SQL Server, or 30,000 with sparse column sets | 16 MiB per document in MongoDB |
| Reporting and BI | Pivot first, into views, materialized views or exports | Tools see one opaque column; extract keys into views or columns | Works directly | Usually flattened into a warehouse first |
| Pick it when | Attributes are user-defined data with thousands of possibilities and a handful per entity | A relational app has a varying tail of fields you don’t yet understand | Attributes are known, at most a few hundred, and you report on them | Whole records vary and are read and written as one unit |
| Seen in | WordPress meta tables, Magento catalog, clinical data repositories, RDF triple stores | PostgreSQL jsonb, MySQL JSON, SQLite JSON functions, Snowflake VARIANT | Survey and feature tables, SQL Server sparse columns | MongoDB, Firestore, Cosmos DB, DocumentDB |
Rule of thumb
Start with columns for every field you know you’ll query, and put the unpredictable rest in a JSON column. Watch which keys get used and promote those to real or generated columns. Reach for EAV only when attributes are themselves data that users define, and you’re ready to build the metadata layer it needs; switch to a document database when whole records, not just a tail of fields, vary.
Sources
- Dinu and Nadkarni: Guidelines for the effective use of entity-attribute-value modeling for biomedical databases (Int J Med Inform, 2007)
- Magento 2 source: Catalog module db_schema.xml (typed catalog_product_entity value tables)
- WordPress Common APIs Handbook: Metadata (meta table structure)
- Pham, Passing, Erling and Boncz: Deriving an Emergent Relational Schema from RDF Data (WWW 2015)
- PostgreSQL docs: JSON types (GIN indexing, row-level locks on update)
- PostgreSQL docs: ALTER TABLE (adding a column without a table rewrite)
- PostgreSQL docs: CREATE STATISTICS (expression statistics)
- PostgreSQL docs: Statistics used by the planner
- PostgreSQL docs: Database page layout (row header, null bitmap)
- PostgreSQL docs: Appendix K, PostgreSQL limits
- supabase/pg_jsonschema on GitHub: JSON Schema validation for json and jsonb
- MySQL 8.4 Reference Manual: The JSON data type
- MySQL 8.4 Reference Manual: JSON schema validation functions
- SQLite docs: JSON functions and operators
- Snowflake docs: Considerations for semi-structured data stored in VARIANT
- Heap: When To Avoid JSONB In A PostgreSQL Schema (repeated keys, missing statistics)
- Microsoft Learn: Use sparse columns (SQL Server)
- MongoDB manual: BSON types
- BSON specification (element names stored with each element)
- MongoDB manual: Schema validation
- MongoDB manual: Multikey indexes
- MongoDB manual: $lookup (aggregation)
- MongoDB manual: MongoDB limits and thresholds (16 MiB BSON document size)
- MongoDB manual: Query plans (multi-planner trial period, cost-based ranker)
- Microsoft Learn: Maximum capacity specifications for SQL Server (columns per table)