What is the Structure of the Entity-Attribute-Value Table?

Earlier we covered the facts that the EAV table consists of thee columns in which data is recorded. Those columns were the entity, the attribute, and the value. Now we will talk a little more in-depth about each column.

Ads

• The Entity, sticking to the scenario of clinical finding, the entity would be the patient event. This would contain at the very least a patient ID and the date and time of the examination. 
  .
• The Attribute, or often referred to as the parameter, is a foreign key into a table of attribute definitions. In our example it would be the definitions of the clinical findings. The attributes table should contain the attribute ID, the attribute name, description, data type, units of measurement, and columns aiding input validation. 
  .
• The Value of an attribute, this depends on the data type.

Entity-Attribute-Value Database

This database is most commonly called the EAV database; this is a database where a large portion of data is modeled as EAV. Yet, you may still find some traditional relational tables within this type of database.

• We stated earlier what the EAV modeling does for certain categories of data such as clinical findings where attributes are many and few. However where these specific functions do not apply we can use a traditional relational model instead. Using EAV has nothing to do with leaving the common sense and principles of a relational model behind. 
  .
• The EAV database is basically un-maintainable without the support of many tables that store supportive metadata. These metadata tables usually outnumber the EAV tables by about three or more, they are normally traditional relational tables. 
  .
• The Entity in clinical data is usually a Clinical Event as we have discussed above. However for more general purposes the entity is a key into an Objects table that is used to note common information about all of the objects in the database. The use of an Object table does not need EAV, traditional tables can be used to store the category-specific details of each object. 
  .
• The Value brings all values into lings, as in the EAV data example above as well, this results in a simple, yet still not scalable structure. Larger systems use separate EAV tables for each of their data types, including the binary larger objects, this deals with the metadata for a specific attribute in identifying the EAV table in which the data will be stored. 
  .
• The Attribute, in the EAV table this is no more then an Attribute ID, there are normally multiple metadata tables that contain the attribute related information.

Issues Associated with the EAV Model

There have been a number of issues with the Entity-Attribute-Value model brought to light throughout its lifetime. We will briefly discuss those now. It is important that we clarify first that these issues arise when metadata is not used with the EAV model, for metadata is vital for its functionality.

Here are some of the issues:

Flaccidity. The litheness is wonderful, still there is a time where we no longer have any structure at all. Normally you can not rely on the built in database features like the referential integrity any longer. To ensure that the column takes the values within an acceptable range only you need to code the integrity checks inside of the application. This does not aid in making the model maintainable.

Designer issues. Adding attributes as you go is tolerable for a prototype. Yet if you are unaware of what data you want to use from the go, you are just looking for problem.

The technology of the relational databases will be inaccessible and will have to be recreated by a development team, this could include system tables, Graphical query tools, fine grained data security, incremental back-up and restore, and exception handling, partitioned tabled, and clustered indexes. All of which are currently non-existent.

Ads

The actual format is not supported well by the DBMS internals. Standard query optimizers for SQL do not handle the EAV formatted data that well, and a lot of time will need to be dedicated to performance tuning for an acceptable production quality application.

As you can see from above there are still a few issues that need to be addressed by developers in order to make the EAV optimal. Regardless of those issues we have also learned that if we use metadata with the EAV we can avoid many if not all of these issues.

First Page: Entity Attribute Value (EAV)