Friday, February 13, 2009

EAV - The Good and Bad

The Entity-Attribute-Value data model (commonly referred to as EAV), is a model used when dealing with Entity or Relationship type specific attributes and associated values.

I've often read about the principle in application development to "encapsulate what varies". Essentially the desire is to find software solutions that are able to handle changes in tht application's requirements without the need for frequent code modification. When the need arises to change the attributes persisted with an entity, the EAV data model encapsulates these changes exceedingly well.

While application developers like this quality some DBA's do not. Since part of the database's schema design is now abstracted away into the EAV tables, the application users have the power to choose attributes and values that will be used at will. While this can be a good thing, there are several problems that arise from this flexibility.
  1. Application users will not always agree on a naming convention for attributes. Duplicated Attributes with similar names appear over time. This is highly undesirable since the users are essentially corrupting the data model's conceptual schema with bad attribute names.
  2. There is no way to implement checks or foreign keys in the RDBMS tier for the attributes' value field to prevent invalid data.
  3. Users are able to assign invalid attributes to an entity type. Alternatively there is no way to ensure that the users will add all valid attributes that an entity type should have.
  4. As EAV tables grow in size they become SELECT statement performance killers.

Unfortunately for the DBA, the DBA is usually given the tedious task of correcting the above mentioned problems. And while the DBA may be successful in correcting some of these problems, he or she does not have the controls necessary to prevent these problems from reoccurring in the future.

So while the EAV data model is an attractive tool that application developers can use, consideration should be given to this model's weaknesses. Mitigating these weaknesses in the application tier will make the application more robust and will make the task of maintenance easier for the DBA.

No comments: