Search⌘ K
AI Features
Home
Courses
The Art of PostgreSQL

Entity Attribute Values

Explore the Entity Attribute Value (EAV) anti-pattern in PostgreSQL and understand why it complicates data management and querying. Learn the risks of poor normalization, such as typos and incompatible data types, and how EAV impacts SQL efficiency. Gain insight into better design practices, including using jsonb columns as a flexible alternative for volatile attributes.

We'll cover the following...

Failure to follow normalization forms open the door to anomalies. Some failure modes are so common in the wild that we can talk about anti-patterns. A bad design choice would be the EAV model.

The entity attribute value (EAV) is a design that tries to accommodate a lack of specifications.

Creating a table

In our application, we have to deal with parameters, and new parameters may be added at each release. It’s not clear which parameters we need, we just want a place to manage them easily, and we’re already using a database server, after all. So there we go:

PostgreSQL
begin;
drop schema if exists eav cascade;
create schema eav;
create table eav.params
 (
   entity    text not null,
   parameter text not null,
   value     text not null,
   
   primary key(entity, parameter)
 );
commit;

Inserting data

You ...