Supporting Data Integrity

When we use EAV, we sacrifice many advantages that a conventional database design would have given us.

We can’t make mandatory attributes

Returning to the example stated above, to should also require that the date_reported attribute has a value. In a conventional database design, it would be simple to enforce a mandatory column by declaring the column NOT NULL.

In the EAV design, each attribute corresponds to a row in the IssueAttributes table, not a column. You would need a constraint that checks that a row exists for each issue_id value, and the row must have the string date_reported in its attr_name column.

However, SQL doesn’t support a constraint that can do this. So, you must write the application code to enforce it. If you do find a bug with no reported date, should you add a value for this attribute? What value should you give it? If you make a guess or use some default value for a missing attribute, how does that affect the accuracy of your boss’s reports?

You can’t use SQL data types

Your boss tells you that they are having trouble running their report because people have entered dates in different formats or sometimes even strings that aren’t dates. In a conventional database, you can prevent this if you declare the column with the DATE data type.