Introduction of EAV database model

The EAV database model, fully written as Entity-Attribute-Value Pattern, is a responsive model for system construction that requires frequent increasing customization of table properties.

EAV is a structured database design in Magento. The strength of this database model is highly scalable, not redundant.

The problem sets out

Example one : We need to design a database for a product management system of a phone store.

Normally, we will design a Products table with information (name, description, price, quantity, ram, rom ..)

After completing the system and putting into operation the system, the customer will request additional information such as (color, operating system, cpu ..), oke, it’s okay, we just add those columns to the DB.

But if customers want to sell their phones, they also want to sell computers, cameras, televisions, speakers … how can they process now, can they just add the necessary attributes of each product to the product table? The table is swollen to hundreds of columns, there are many columns used in this product but not used in other products, it will be in the state NULL => So this DB design is not guaranteed.

Example two

As well as the first problem: here we need to manage user information:

Initially the user table will have some information as follows

Then the system is required to store more data and that data arises while using the system, which means that the request for admin can create more columns on the system.

And the old DB can not meet, then we can use the EAV database model to solve this problem.

Using EAV model to solve the problem

In this section we use the EAV model to solve the problem of user management with 3 tables of data.

• First of all, we create a users table: including fields that must be present and not changed but (name, email, password …) this table in EAV model is called Entity table (entity).

• Next, we create a table user_fields: this table in the EAV model is called an Attribute table with fields (id, field_name is’) this table is used to store the property names of the user, for example, to save phone If the user or adding the address of the user, we need to insert that property into this table

• The final table is the table user_values: this table has the columns user_id, user_field_id, value. Used to store the user values ​​corresponding to the properties in the user_fields table, for example in the table user_fields with the phone property, what is the table user_values ​​used to store the phone value of the user.

The design is done, now let’s see how it works:

We need to manage more information about the user’s card_number

For example we need to insert user information as follows.

1. Add a user with basic information of the user table

   => A generated record is (id = 1, name = ‘Nguyen Thinh’, ’email’ = ‘ [email protected] ‘)

2. Add ‘card_number’ to the user_fields table

   => a generated record is (id = 20, field_name = ‘card_number’)

3. Add the value card_number = ‘01919122’ of the user whose id is 1 to the table user_values

   => a generated record is (user_id = 1, user_field_id = 20, value = ‘01919122’) When we need to get the information of the user, just get in the users table combine user_fields table and user_values ​​to get the information news needed.

Thus, we have used the EAV model to solve some of these articles. Our data can guarantee the customization of table properties.

End

Through this article, we also know the basic usage with EAV database model from which we can apply to the problem with ‘Dynamic’ data requirement. Thank you for watching.