The Entity-Attribute-Value model or EAV is also sometimes referred to at the Object-Attribute-Value Model, or even the Open Schema. This is a data model that is often used in instances where the amount of attributes, properties, or parameters that can be used to define an entity are potentially limitless, however the number that will apply to the entity is some what modest.
The easiest way to understand the function of the Entity-Attribute-Value model design is to try to understand row modeling, as Entity-Attribute Value models are a universal form. Let’s think of a department store database. These databases are responsible for managing endless amounts of products and product brands.
It is innately obvious that the product names wouldn’t be hard-coded as the names of the columns in a table. Alternatively one department’s product descriptions in a product table may function as follows: purchases/sales of an individual item are recorded in another table that would have separate rows with a way to use the product ID for referencing.
An Entity-Attribute-Value design normally involves a solitary table with three columns, these columns most often contain data referring to; the entity, an attribute, and a value for that attribute.
In this design one row actually stores a single fact, in a traditional table that has one column per attribute, one row stores a set of facts. The Entity-Attribute-Value design is applicable when the number of parameters that could apply to an entity is significantly more then those that truly apply to a single entity.
Perhaps the most notable example of the EAV model is in the production databases we see with clinical work. This includes clinical past history, present clinical complaints, physical examinations, lab test, special investigations, and diagnoses. Basically all of the aspects that could apply to a patient. When we take into account all of the specialties of medicine, this information can consist of hundreds of thousands of units of data.
However, most people who visit a health care provides have few findings. Physicians simply do not have the time to ask a patient about every possible thing, this is just not the way in which patients are examined. Rather then using the process of elimination against thousands of possibilities the health care provider focuses on the primary complaints of the patient, and then asks questions related to those complaints.
Now let’s consider how some one would attempt to represent a general-purpose clinical record in a database like those we discussed earlier.
By creating a table or even a set of tables with thousands of columns would not be the best choice of action, the vast majority of the columns would be unacceptable, also the user interface would be obsolete with out an extremely elaborate logic that could hide groups of columns based on the data that has been entered in the previous columns.
To complicate things further the patient record and medical findings continue to grow. The Entity-Attribute-Value data model is a natural solution for this perplexing issue, and you shouldn’t be surprised to find that larger clinical data repositories do use this model.