Entity Attribute Value Model

Today, we are going to talk about a Database design model, known as the Entity Attribute Value model aka EAV model. EAV Database models are those models that are used when the number of attributes or columns is not known for some records of entities in the Database. So, the journey of the article is as follows:

1. Entity Attribute Value Model (EAV model).
2. EAV Model structure.
3. Pros and Cons of Using the EAV Model.
4. An example implementation of the EAV Model.

Entity Attribute Value Model (EAV Mode)

As discussed in the above definition, EAV models describe the not known attributes associated with entity records in a Database. So, what does it mean no known attributes here? As we all know during any application development, its DB tables are designed, and some columns/attributes/fields are defined for each DB table. So, now what if we want to add one more field/attribute to an entry of a DB table at run time, Can we do it using the traditional approach?
If we go with the traditional approach, we will make an altered query and insert a new column into that table.

Let’s discuss some consequences of using this approach.

1. Space Wastage: The above approach leads to a lot of wastage of space, and creates unnecessary null value entries in the table.
   For ex: you have one player table, and it already consists of more than one million players’ records, and it has some fields like id, name, age, player_game, etc.

 Player
 |-----------------|--------------------|
 | Column          |          Type      |
 |-----------------|--------------------|
 | id              |          number    |
 | name            |          varchar   |
 | age             |          number    |
 | player_game     |          gameType  |

Now, In this table for some 1000 players among 1 million, we want their hobby as their new attribute, and we go with this approach.
So, we will run an altered query on the above table, which will create a new column for each record. Now, for some 1K players, this field is usable and consists of some value, but for the rest of the players it takes a null value, and a null value also consumes some space in the form of NDB clusters in the DB storage system as written here. Now you can assume, how much storage space is wastage as we are introducing a new column in the table.

2. Downtime: As we insert a new column in the table (which already consists of millions of records), it will also consume some time to make a db operation (indexing and inserting a new null value column) and make the table and table records compatible with the new column. This downtime leads to a major down in the cost of the system. You can read more here.

So, to get rid of the above Major problems, EAV Models are being introduced for database design. Let’s go with the structure of the EAV Model with the same example as above.

EAV Model Structure

EAV model’s structure totally depends on three major DB tables which are entity, attribute, and value table. Let’s understand the significance of each table and structure.

Fig: EAV Model Structure

1. Entity: Entity basically represents the object such as Users, Tournament, Library, etc. You can understand an entity as a database table, and it consists of multiple records in the DB.
2. Attribute: It represents the extra attribute/property for any entity. For example, the User entity has some extra fields such as user_description, user_profile, etc.
3. Value: This is very crucial and basically shows value corresponding to an attribute, and links the value with the attribute, entity, and type of entity (we can take it in the Attribute table also).

Pros of Using the EAV Model

1. Using the EAV Model, we can overcome the situation of creating an unnecessary extra column in the table, which helps us to reduce space wastage from the table.
2. Using EAV Mode, We can reduce the downtime that comes during the creation of a new column.

Cons of Using the EAV Model

1. Although, we are overcoming the situation of inserting a new column, not much.
2. We are already creating multiple tables for attributes. This multiple-table creation is already taking up a lot of space.
3. As we are creating multiple tables, and linking them with each other using Foreign key concepts, while fetching records from the table, there will be multiple joins between the tables, which will increase the time complexity.

An example implementation of the EAV Model

Let’s move with an example of the EAV Model. Let’s suppose, you have a player entity, You want to create an EAV model for it so that we can some extra attributes to this entity without adding a new column to the already created table.

As we already discussed in the above EAV Structure diagram, we need an attribute and value table other than an Entity table. Let’s see the below model structure of the Player EAV Model.

Fig: Player EAV Model

1. EntityType: This is an enum that basically reflects the type of entity in the attribute, which shows current attribute is associated with this type of entity. Now, we can have multiple entities, and we can update the name for which we want to create the EAV model.
2. DataType: This is an enum that basically reflects the type of data in the attribute, which shows current attribute is associated with this type of data. An attribute can be of type String, Boolean, Select, etc.
3. Options: This table is basically created to indicate when an attribute type is SELECT because the SELECT type of attribute can have multiple options. For example, if an attribute name is Gender, it can have multiple options like Male, Female, or Other. So, here Male is an option, Female is an option.
4. Attribute: For attribute, we have already discussed in EAV Model structure. it has some extra fields for validations like isRequired, regex, etc.
5. Player(Entity): It’s an entity that we have already discussed in the EAV Model structure.
6. Value: This table links the attribute entity and option if that attribute has any options.

Thus, we have understood the concept of the EAV Model with a small example of the Player EAV Model. Look at the tables, and try to implement this in any Language.

Attached are some references from where you can get a deeper knowledge of it.

1. EAV Zero to EAV Hero!
2. Entity Attribute Value

Thanks, Waiting for your feedback.