SQL Antipatterns

Solution: Model the Subtypes

If EAV seems like the right design, you should take a second look before you implement it. If you do some good old-fashioned analysis, you will probably find that your project’s data can be modeled in a traditional table design more easily and with greater assurance of data integrity.

There are several ways to store such data without using EAV. Most solutions work best when you have a finite number of subtypes and you know the attribute of each subtype. Which solution is best to use depends on how you intend to query the data, so you should decide on a design on a case-by-case basis.

Single Table Inheritance

The simplest design is to store all related types in one table, with distinct columns for every attribute ...