How Magento uses EAV in Database Architecture – Part 1

Database

A database is a term we all have heard a lot of times from different people, but what actually is a database? Talking in layman’s language we can say that it’s a storage mechanism for storing the data. But, in technical terms it is the backbone of all the applications, be it mobile applications, web applications or system applications. Databases store the interaction of the users and keep the data intact into a relational schema, for reusability.

Hence we can say that no application is a complete application without a database.

How Magento uses it

For an application platform like Magento, having a market share of about 30% in the e-commerce business, the database is a key element and needs to be scalable, optimized, secure, and customizable. Since Magento provides high scalability (one of the major reason for such high market coverage), accordingly the database should be highly customizable. In order to achieve all the above specifications, Magento selected the EAV-Model to be the best applicable database model for this scenario. Since there are many debates on the acceptability of the Eav as compared to Flat tables, you can weigh the pros and cons of it explained ahead in the blog.

The EAV model used by Magento has three components.

• E => Entity
• A => Attribute
• V => Value

Before we go into Magento database and architecture, let’s discuss them taking a real-life example.

• • Entity – Entity can be anything that has properties, currently what I see in front of me is a small whiteboard marker, here Entity is Marker.
  • Attribute – Attribute of a marker can be length, color, thickness, marker_type.
  • Value – For each of the attribute will have a value,
    • length => 6inch
    • color => black
    • thickness => .2mm
    • marker_type => permanent

This is Eav model in a nutshell.

How Magento uses a database?

So Magento has customized and optimized the database and added restrictions and functionality accordingly. Talking about the techies, each of them knows about data types as we have them in every language. MySQL already has different data types, and Magento also introduces data types as a layer over it. Data types such as int, DateTime, varchar, text, decimal are introduced in Magento to segregate the values based on the attribute type.

In Magento attribute can be of various types, such as:

• • • Textarea
    • Text
    • Date
    • Boolean
    • Multi-select
    • Select
    • Price
    • Image

Each of the above attribute types has a different method to save the values and a different architecture to store data, keeping reusability in mind.

(Note: Always consider the usage area in mind while creating any new attribute. For example, if you want the attribute that should filter the products on frontend you must always use the select type or the multi-select type.)

Let’s discuss the attribute type considering our above example, where we discussed a Marker pen.

• • • 1. length => 6 inches.

Length of the marker can be anything, so we can keep it as a text. (if we are using for only showing this attribute)

• • • 2. color => black

Now, this is something that the customer might consider while making the purchase of the marker and might use layered navigation or filters to get the particular one.

• • • 3. thickness => .2mm

Again, it can be just for showing purpose as it does not affect the user selection.

• • • 4. marker_type => permanent

Considering marker_type this might be something that can affect the purchase decision of the customer.

Hence, one should make attributes considering the scope of the attribute and usability and this gives us the scalability.

Readers, I know the above this is too complex to understand, so let me explain this a bit more using the same above example in the next topic.