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

Synopsis: Entity-Attribute-Value

Learn to identify the Entity-Attribute-Value antipattern challenges, including its impact on data integrity and querying. Understand when its use may be justified and explore alternative modeling strategies to handle variable attributes more effectively in relational databases.

We'll cover the following...

“How do we count the number of rows by date?” This is an example of a simple task for a database programmer. It involves basic SQL syntax:

MySQL
SELECT date_reported, COUNT(*)
  FROM Bugs
 GROUP BY date_reported;

However, the simple solution assumes two things:

  • Values are stored in the same column, as in Bugs.date_reported.

  • Values can be compared to one another so that GROUP BY can accurately group dates with equal values together.

What if we can’t rely on those assumptions? What if the date is stored in the date_reported or report_date column, or in any other column that’s different on each of its rows. What if dates can take a variety of different formats and the computer can’t easily compare two dates?

We may encounter these problems and others when we employ the antipattern known as Entity-Attribute-Value (EVA).

Objective: Support variable attributes

Extensibility is frequently a goal of software projects. We would like to design software that can adapt fluidly to future usage with little or no additional ...