...EAV gives you enough rope to hang yourself and in this industry, things should be designed to the lowest level of complexity because the guy replacing you on the project will likely be an idiot.
maple_shaft, Stack Overflow
The Entity–Attribute–Value (EAV) model is a data model to encode, in a space-efficient manner, entities where the number of attributes (theoretically infinite) varies among entities.
| Entity | Attribute | Value |
|---|---|---|
| ⟨Product 001, Launch Date⟩ | ⟨2024-03-27⟩ | |
| ⟨Product 001, Name⟩ | "UltraGadget Pro" | |
| ⟨Product 001, Color⟩ | "Midnight Blue" | |
| ⟨Product 001, Weight⟩ | ⟨Grams⟩ | "150" |
| ⟨Product 001, Battery Life⟩ | ⟨Hours⟩ | "24" |
| ⟨Product 001, Waterproof⟩ | "True" |
- Entity - The object or thing you're storing data about.
- Attribute or Characteristic:
- Typically represented as a foreign key linking to an attribute definitions table.
- Attribute Definitions Table might include columns for:
- Attribute ID: A unique identifier for each attribute.
- Attribute Name: The name of the attribute.
- Description: A brief description of the attribute.
- Data Type: The data type (e.g., string, integer, date).
- Input Validation Columns: May include:
- Maximum string length.
- Regular expression for validation.
- Predefined set of acceptable values.
- Value of the Attribute: The specific data or detail assigned to an attribute.
In theory this approach works great!
There are apparent advantages like:
- You can add and remove attributes dynamically without database modifications.
- Adaptability to changes.
- It's very space-efficient.
Disadvantages are not as clear cut, especially when you're just staring and not dealing with large datasets and complex queries. But, with more data, the disadvantages become more apparent:
- Poor performance.
- Querying is complex and slow.
- It takes a lot of work to enforce data integrity.
- It takes a lot of work to enforce validation rules.
- It takes a lot of work to enforce constraints.
- It takes a lot of work to enforce relationships between entities.
When to Use EAV.
Never.
It's not worth it. If possible, use NoSQL - Document Store (but to be fair, all popular document-oriented databases are not that old (MongoDb 2009, CouchDB 2005, Dynamo 2012, Cosmos 2017 (since the early 2010s under different names)).
Every few months a Newbie re-invented the EAV fallacy and thinks they are so clever.
Joe Celko on comp.database.theory
Been there, done that. One of the stupidest technical decisions I've ever made.