How Magento Database Structure Will “Take Over” Your Job

What if you’ve coded a website to sell stuff online (clothes, for example) and then you need to build another online store for your other stuff, like cosmetics?

The reuse of the code can be complicated and time-consuming for you, as a developer. Aware of these difficulties, Magento has developed an extremely effective database structure with EAV pattern.

As you might know, Magento is a dynamic platform which offers a highly convenient solution for reusing code. To understand the importance of EAV, let’s take a look back at the conventional way of building an online store before Magento database structure was born.

Normally the database structure looks like this:

In this case, you have under Product or Catalog a list of folders containing its specified information. This database structure will work fine only if your customers don’t ever need to change their products information. But that’s not likely to be true, and you’ll have to deal with a ton of work with any minor change!

And EAV came to life.

EAV stands for Entity (such as Product or Catalog), Attribute (basically it represents all the information belonging to an Entity) and Value (value of an attribute).

Here’s how EAV works

It splits Attribute and its value from Entity. For example, we’ll have attributes (name, description, price, size…) and all their corresponding values out of Product table.

Let’s see how EAV is applied in Magento in the map below:

The thing that helps Magento to better control attributes is the classification of the attribute value data by types, such as varchar, decimal, int…When creating a new attribute, you’ll be asked which type of attribute you want to input. Each type will have its own way of validation.

The innovation brought by EAV has helped Magento database structure to be able to operate all kinds of extraordinarily complex business models.

Take a look at how Magento develops a Configurable Product from Simple Products to see clearly how it works:

Firstly, we have a Catalog_product table (id, sku, type_id, is_active, sort_order, visibility, created_at, updated_at): (1,’sku001’, simple,1,0,0) (2, ’sku002’, simple,1,0,0) [3,’sku003’, configurable,1,0,1]. There are two Simple Products with their visibility value set to 0 – which means they don’t appear on the front end, and a Configurable Product.

Next, we’ll set size attributes for Products: eav_attribute [id, code, label, type, is_required, is_unique, note]: [5,’size’,’Size’, varchar,1,0] and catalog_product_var_char [id, attribute_id, store_id, entity_id, value]: [98,5,1,1,’M’] [99,5,1,2,’L’].

Notice the size attribute value of our first Product [sku001] is M and of the second Product [sku002] is L.

Finally, we’ll put these two Simple Products in the third Configurable Products by joining them in the table catalog_product_link [id, product_id, linked_product_id] [1,3,1] [2,3,2]. This would bring the third Product with 2 choices of size [M, L] on the front end.