How EAV Data Model helped us

In E-commerce domain usually products are meant to have multiple attributes for e.g Name, Description, Price, SKU, image, meta and so on. And this number of properties can increase/decrease over a period depending upon its requirement and usage. Which may eventually lead to the altering of the table for adding and deleting columns. And it may also lead to blank columns if that attribute is no longer in use.

Then we stumbled upon EAV. EAV data model provides flexibility in such manner that you do not need to alter database tables to add/remove new product attributes.

As per Wikipedia:

Entity–attribute–value model (EAV) is a data model to encode, in a space-efficient manner, entities where the number of attributes (properties, parameters) that can be used to describe them is potentially vast, but the number that will actually apply to a given entity is relatively modest. Such entities correspond to the mathematical notion of a sparse matrix.

In our case the product is a Doctor and primarily we had 2 major problem to address:

1. Minimise or eliminate the altering of table completely
2. And the attributes should have multilingual support

Table Structure:

Instead of Storing data like

Flat Table

EAV uses a row for each column

EAV Table

• Entity : In this case it is combination of doctor Id and Language.
• Attribute : name, Qualification, Specialisation etc
• Value : The value of attribute for that entity

ER Diagram

Feature of EAV Model

1. Focuses on the Entity.
2. Data is organised which makes retrieval and insertion of records is easy.
3. Can handle new Attribute/Properties of Entity without altering the table.
4. Only non-empty values are stored.

Summary

By using EAV we were able to handle almost 16 attributes of Doctor entity with just 4 columns where as in case of a Flat table there would have been 16 columns. And this number would have only kept increasing upon introduction of new attributes.