Opening Scenario
You're building a product catalog. Products have common fields (name, price, category) but wildly different attributes: a t-shirt has size, color, and material; a laptop has RAM, CPU, storage, and display resolution; a book has ISBN, author, and page count. You have 200+ product types with different attribute sets.
Your first thought is a products table with a dozen nullable columns. That gets unmaintainable fast. A colleague suggests an attributes JSON column. Another suggests separate product_attributes rows with a key/value structure. A third says to use a document database instead.
You need to store semi-structured data in a relational database. What do your options cost?
Question Progression
Q1: What is the Entity-Attribute-Value (EAV) pattern, and why do most teams regret it?
Question: The product_attributes key-value suggestion is a classic pattern called Entity-Attribute-Value (EAV). What does it look like, and what goes wrong?
Reasoning: EAV stores each attribute…
In the full tutorial
- Closing: When to Reach for This