EAV means Entity-Attribute-Value, a data model that stores facts as tiny triples: thing, property, value. It is handy when each thing may have different fields. It is also easy to abuse. Use it for sparse, changeable data. Do not use it as a lazy replacement for a normal relational schema.
TLDR: EAV is like a giant box of labeled sticky notes. A product can have color = red, size = XL, and battery life = 12 hours, even if other products do not have those fields. In one ecommerce case, a team used EAV for 500 product attributes and cut schema changes by about 80%. But their reports became slower, so they kept prices, orders, and customers in normal relational tables.
What EAV Means
EAV stands for Entity-Attribute-Value.
- Entity: the thing you are describing.
- Attribute: the property name.
- Value: the actual value.
Think of a cat named Muffin.
- Entity: Muffin
- Attribute: color
- Value: orange
In a database, that might be one row. Another row could say Muffin has weight = 5 kg. Another says mood = judging you. Very realistic.
The simple idea is this: instead of making a new column for every possible field, you store each field as a row.
The Normal Relational Way
A relational database usually stores data in tables with fixed columns.
A product table may look like this:
- id
- name
- price
- sku
- weight
This is clean. It is fast. It is easy to query.
If you want all products under $50, no problem. If you want total sales by month, also fine. Relational databases are great at this stuff. They have indexes, joins, constraints, and strong rules.
But then the product team appears.
They want shoes to have heel height. Laptops need RAM size. Coffee beans need roast level. Dog sweaters need neck size. Suddenly your nice table looks like it lost a fight with a spreadsheet goblin.
This is where EAV starts to look useful.
How EAV Stores the Same Data
Instead of adding 200 columns to a product table, EAV uses a separate table.
It might have these columns:
- entity_id
- attribute_name
- value
So a laptop might be stored like this:
- Product 101, RAM, 16 GB
- Product 101, screen size, 14 inches
- Product 101, battery life, 12 hours
A coffee product can use different fields:
- Product 202, roast, medium
- Product 202, origin, Colombia
- Product 202, grind, whole bean
No table change needed. That feels nice. For about five minutes.
The Catch Is Query Pain
The catch is that EAV can make simple questions weird.
Want all laptops with 16 GB RAM and battery life over 10 hours? In a normal table, that is easy. In EAV, you may need to join the attribute table to itself. Maybe twice. Maybe more. It gets annoying fast.
Honestly, it feels like asking a librarian for one book, then being handed 300 note cards and a wink.
EAV also makes data types messy. Is the value a number? Text? Date? Boolean? If everything sits in one value column, you must handle that somehow. If you do not, sorting can go sideways. For example, text sorting may put 100 before 20. Nobody asked for that chaos.
EAV vs Relational Database
Here is the simple comparison.
- Relational tables are best for stable facts. Orders, invoices, users, payments, and stock counts belong here.
- EAV is best for optional facts. Product specs, medical observations, custom form fields, and profile extras can fit well.
- Relational tables are easier to report on. SQL stays readable.
- EAV is easier to change. New attributes can be added without changing the schema.
- Relational tables protect data better. You can enforce types and rules more easily.
- EAV needs discipline. Without rules, it turns into a junk drawer with a database badge.
A good system often uses both. Keep the core data in relational tables. Put rare or custom fields in EAV. This is not betrayal. This is adult supervision.
EAV vs JSON Data Models
JSON is another common choice for flexible data.
With JSON, you can store custom fields in one column. For example:
{
"ram": "16 GB",
"screenSize": "14 inches",
"batteryLife": 12
}This is easy for developers to read. It also maps nicely to APIs. Many modern databases can search inside JSON fields. Some can index JSON paths too.
So why use EAV at all?
EAV can be better when you need to search, filter, and manage attributes as records. You can attach metadata to attributes. You can track who changed a value. You can give attributes types, labels, units, and permissions.
JSON can be better when each entity owns its own blob of extra data. It is tidy for app settings, event payloads, and API responses. It can be fast enough when queries are simple.
Expect to waste time if you pick JSON and later need deep reporting across millions of values. You may end up writing odd queries that run 4 seconds slower than the old report. Then someone says, “Can we export this by region?” That is when coffee becomes a support tool.
When EAV Is a Good Idea
Use EAV when the fields are many, sparse, and often changing.
Good examples include:
- Product catalogs with different specs by category.
- Medical records with many test types and observations.
- Custom forms where users define their own questions.
- Feature flags or settings with many optional values.
- Asset management where each asset type has special traits.
EAV shines when most entities use only a small slice of all possible attributes. If you have 1,000 possible fields but each product uses 12, a normal table would be mostly empty. EAV avoids that sea of nulls.
When EAV Is a Bad Idea
Do not use EAV for everything. Please. Future you has enough problems.
Avoid EAV when:
- You need fast analytics on many fields.
- The fields are stable and known.
- You need strict data rules.
- Your team is not ready to manage attribute metadata.
- You want simple SQL for daily reporting.
Putting orders, payments, and addresses into EAV is usually a mistake. Those things have clear shapes. Give them real columns. Your database will thank you by not turning every query into a tiny escape room.
How to Use EAV Without Regret
If you choose EAV, add guardrails.
- Create an attribute table. Store names, labels, types, units, and allowed values.
- Use typed value columns. Have separate columns for text, number, date, and boolean values.
- Index common searches. Do not wait until reports crawl.
- Limit free-form attributes. Too much freedom creates duplicate fields like Color, colour, and product color.
- Keep core data relational. Names, prices, IDs, and dates deserve proper columns.
The Simple Rule
If the data has a stable shape, use a relational model. If the shape changes a lot, consider EAV or JSON. If users need custom fields and reports across them, EAV may win. If the app just needs to store flexible payloads, JSON may be simpler.
EAV is not magic. It is a tool. Used well, it saves schema changes and handles messy real-world variation. Used badly, it makes every query feel like assembling furniture with missing screws.
The best design is often boring. Core facts go in relational tables. Extra facts go in EAV or JSON. Reports get planned early. Everyone sleeps better.