✏️ Explanatory Question

JSON column vs normalized tables vs EAV — the decision framework

👁 9 Views
📘 Detailed Answer
🟢 Easy
← Previous JSON aggregation — build nested, API-ready JSON directly from SQL
💡

Answer with Explanation

124

JSON column vs normalized tables vs EAV — the decision framework

Level: Hard — A design-judgment question; picking the wrong storage model creates years of technical debt.

Scenario: You are designing a system that stores data with some fixed fields and some flexible/variable attributes. The interviewer asks: "Would you use a JSON column, normalized tables, or the EAV pattern? Give me a clear decision framework, not just a preference." This tests architectural maturity.

The Three Approaches Recap

  • Normalized tables: Each attribute is a typed column (or a related table). Strong integrity, fast queries, but rigid schema.
  • JSON column: Flexible attributes in one JSON field. Schema-free, but weaker constraints and harder to query/index at scale.
  • EAV (Entity-Attribute-Value): One row per attribute. Ultimate flexibility, but join-hell and no data types.

The Decision Matrix

FactorNormalizedJSON ColumnEAV
Schema flexibilityLowHighVery high
Query performanceExcellentGood (with generated cols)Poor (join-hell)
Data integrity / typesStrong (FK, CHECK)WeakVery weak
IndexingNativeVia generated columnsAwkward
Aggregation / reportingEasyHarderVery hard
Best when attributes areKnown & stableFlexible & sparseTruly unbounded

The Decision Framework (ask these in order)

  • Are the attributes known and stable? → Use normalized columns. This is the default and the right answer most of the time.
  • Do you frequently filter/join/aggregate on the value? → Normalized column (or a generated column if it lives in JSON).
  • Are attributes flexible, sparse, or vary per row, but rarely queried directly? → JSON column.
  • Are attributes truly unlimited/unknown (thousands of possible keys)? → EAV — but consider a document DB instead.

The senior recommendation — hybrid: Put frequently-queried, important fields in real columns (typed, indexed, constrained) and use a JSON column for the long tail of flexible/optional attributes. You get performance and integrity where it matters, and flexibility where it does not. On modern MySQL, this hybrid almost always beats pure EAV.

The Hybrid Pattern in Practice

CREATE TABLE products (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    -- Hot, queried, important fields = real columns (indexed, typed)
    name        VARCHAR(255) NOT NULL,
    price       DECIMAL(10,2) NOT NULL,
    category    VARCHAR(50) NOT NULL,
    in_stock    BOOLEAN NOT NULL DEFAULT TRUE,
    -- Long tail of flexible, per-category attributes = JSON
    attributes  JSON,
    INDEX idx_category (category),
    INDEX idx_price (price)
);

-- Promote ONE hot JSON path to an indexed generated column when needed
ALTER TABLE products
  ADD COLUMN brand VARCHAR(50) AS (attributes->>'$.brand') VIRTUAL,
  ADD INDEX idx_brand (brand);

-- Structured fields use fast native indexes:
SELECT * FROM products WHERE category = 'laptop' AND price < 60000;

-- Flexible attributes still available via JSON:
SELECT name FROM products WHERE attributes->>'$.color' = 'red';

Red flag — avoid pure EAV in a relational DB: Teams reach for EAV to "future-proof" schemas, then drown in self-joins and lose all type safety. If you genuinely need unbounded, document-shaped data, a document database (MongoDB) or a JSON column is almost always better than EAV in MySQL.

Interviewer follow-up: "The product team keeps adding new filterable attributes every sprint. JSON or normalized?" → If they must be filtered/faceted, neither pure approach scales gracefully in MySQL alone. Store the source of truth in the hybrid model, and push heavy faceted search to a dedicated engine like Elasticsearch. Match the tool to the access pattern instead of forcing everything into one table.

← Previous JSON aggregation — build nested, API-ready JSON directly from SQL