Many times, database developers face certain circumstances while defining data requirements is that number of attributes are not fixed and hence dynamic. Those several attributes describe the object, but only few of them actually apply to each one. In this case, several number of attributes can be used to narrate an object, but only limited attributes really apply to everyone. To tackle this situation of unknown attributes the EAV data model comes to the rescue.
As technocrats, we are aware of the fact that tables are wonderful data structures that hold data in all shapes and sizes. EAV data model consists of tables of skinny shape instead of fat ones. So what this skinny table means? In simpler words, the table having small number of columns!! That’s it. Let me elaborate the concept of EAV table in detail first so that you will not be trapped in ‘skinny’ word’s jargon.
EAV pattern stores only non-empty values in database, where every attribute-value (or key-value) duo narrates one attribute of a given entity. Hence the reason EAV tables are usually called as “long and skinny” is that “long” denotes to many rows narrating entity, and “skinny” refers to the less number of columns.
Object attributes are saved in a table with three columns: entity, attribute and value. The entity denotes data item being narrated, for example a product such as a vehicle. The attribute refers to data that narrates an entity, for example a product will have a cost, weight and many more features. The value is the value of that attribute, for instance product may have an attribute price of rs 3 lakhs. In addition to this, values can be separated based on data type, hence there would be distinct EAV tables for strings, integer numbers, dates and long text. Breaking the types is done to support indexing and let the database accomplish type validation checks wherever probable.