The Entity Attribute Values (EAV) database pattern has been around for a long time. The purpose of an EAV is to store data where the shape is not known at the time the database schema is created. In essence, you simulate the SQL Engine by creating tables to define data. Then you create additional tables to contain the data defined.
Before we go too much further, let me caution you that an EAV is not a good substitute for database design. If the shape of data can be known ahead of time, you will find it much easier to handle a database that does not use EAVs. EAVs can be difficult to use in reports, especially if you are reporting on multiple entities, each with it’s own set of EAVs. I’ll get to that in a little bit.
I have found a few applications where an EAV made sense. A Customer Relationship Management database is an example. There are many properties you may wish to track about people in which you are interested. However, the properties important to me may be radically different than those you manage. Often, these are relegated to an EAV, because they may differ from one user to another. You may need to track pet names, while another person wants to track favorite colors. Another person might track siblings, or birth order.
Different people tracking different data in EAVs makes it more difficult to report. This becomes more clear when you want to report on data having different EAV properties. If you wanted a report of all persons in your CRM, but the EAV data is different for each, it makes it difficult to include EAV data in a single record. You must pivot the EAV values to make new columns for each person, and each EAV definition. Of course, it will often be empty if you don’t gather the same EAV information for each individual. Since we often report on detail rows, and EAVs are another dimension of each row, that is a reason to avoid EAV data if it is not needed. We’ll talk about some options for this tomorrow.
You can get some detailed specifications on the EAV database pattern at Wikipedia. If your interested in a database completely based on EAVs you can look at the EAV/CR design. This design was intended to allow you to store objects in a relational database. To me it is counter-intuitive. You are using a relational database to store data about data in a relational form, rather than storing the data relationally. If that is all you really want, I’m sure there are better methods.
Cheers,
Ben