Search⌘ K
Home
Courses
SQL Antipatterns: Avoiding the Pitfalls of Database Programming

Antipattern: Use a Generic Attribute Table

Explore the Entity-Attribute-Value (EAV) antipattern known as the generic attribute table. Understand how storing attributes as rows creates difficulties in querying, especially with multiple joins, and why it poses data integrity challenges. This lesson helps you recognize EAV limitations and consider better database modeling strategies.

We'll cover the following...

The solution that appeals to some programmers when they need to support variable attributes is to create a second table, storing attributes as rows. See the diagram below showing the two tables.

Each row in this attribute table has three columns:

  • The Entity: Typically this is a foreign key to a parent table that has one row per entity.

  • The Attribute: This is simply the name of a column in a conventional table, but we have to identify the attribute on each given row.

  • The Value: Each entity has a value for each of its attributes.

For example, a given bug is an entity we identify by its primary key value 1234. It has an attribute called status. The value of that attribute for bug 1234 is NEW.

This design is ...