Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login

You must be logged to download. Click here to login


MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation


MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

The Magento data model – Analysis of the WordPress, Joomla, Drupal and Magento data models - Part 4

See in this article an analysis of the data models of the four main platforms used for publishing content on the Web

The popularization of the Web as a platform for publishing content is a major resource for the dissemination and creation of new ideas, projects, applications, virtual shops and other ways to have an online presence. At a time when many believe that content is king and that the transmission medium is the message conveyed, much of what is produced in different types of web sites like blogs, institutional sites, online stores, applications, portals and others is supported by a single web technology pillar: the platforms for generating and publishing content.

Driven by the free software movement, the main platforms for content generation and publication, also known as CMS (Content Management System), are gaining more followers either through the catalog of services provided by hosting companies or by users who decide build their your own web hosting infrastructure. Among the main features that have popularized the adoption of CMS we can cite: the free and open licensing model, the ease of use for both users browsing the website and for those who create content, the management and dissemination content and the possibility of almost infinite customization by inserting components and templates freely available and produced by the community.

Currently there are several solutions for content management freely available for those wishing to publish web content. To simplify the nomenclature, these tools will be referenced only as CMS throughout this article. The most popular CMS are: WordPress, Joomla, Drupal and Magento. These take the form of software frameworks ready for use after the customization and configuration of certain features. Its main elements are the web page design templates ready for use, the administrative panel, access security, what you see is what you get editor for content, configuration files, images, and also a data model, whose analysis is the main objective of this article.

Knowing the data model of a CMS is important in various situations. For instance: when you want to do some customization, improve performance, perform maintenance on the database, migrate the platform to ensure that resources are being well used and other tasks that support the web site built with CMS. Also, knowing the technical details related to the CMS data model used can facilitate the execution of tasks on a daily basis and also increase the level of experience and expertise of the professionals involved.

This last part will talk about the data model of Magento, a complex and very powerful CMS that can also be used as an ERP system. This part will also present a simple technical comparison of some features regarded the four CMS data models studied in this article.

The Magento data model

The Magento is a CMS designed exclusively for e-commerce. It is so complex that it can be used for other purposes such as ERP, CRM and POS, as it has many characteristics of management systems. For example, it has the resources to fiscal control, accounting and sales team, something that usually is not handled directly by e-commerce platforms. The data model of Magento 1.12 has 192 tables divided into five major subsystems named: System, Product, Customer, Sales and EAV. These five major subsystems are divided into more subsystems that combine the tables. Magento 1.12 is only supported in MySQL with InnoDB engine and has several primary and foreign keys to ensure the integrity of the relationships.

If all tables in the data model of Magento were placed in a diagram that shows all the columns and relationships one would be need 12 A4 pages to print the full model. So, Figure 1 shows only the name of the tables in the data model.

Figure 1. The simplified Magento 1.12 data model.

When you have a data model so long as the Magento 1.12, it is recommended to work with parts of the model instead of the entire model as a whole. Each Party shall cover only a subsystem that details a specific part of the CMS. For example, Figure 2 shows a part of the Customer subsystem comprising the sub-sub systems Review, Rating, Poll and Wish List.

Figure 2. Part of the Magento Customer subsystem and its divisions Review, Rating, Poll and WishList.

With so many tables and subsystems it would be interesting to put a prefix to easily identify which subsystem a particular table belongs to. This happens with some tables of the model, but not all. For instance, the tables that start with the prefix _eav belong to the subsystem EAV, but not all tables are well organized and divided this way. Another criticism is the use of data types: there are no columns of data type bit and there are no types of columns that store binary data (BLOB, CLOBS and others). There are also a lot of columns in different tables that accept NULL values. Most of the columns that have primary keys use the auto-numbering of the database itself, known as identity or sequence.

A very important concept for those working with the Magento’s data model is known as an entity. This concept is not related to what is meant by an entity in the entity relationship diagram. In Magento, an entity refers to some tables that are related to store specific details. For example, Figure 3 shows the groups Category Entity and Product Entity that are part of the subsystem Product. In this context, Category Entity represents a category (a classification of products) and a Product Entity represents a product.

Figure 3
. Part of the Magento's Product subsystem and its divisions Category Entity and Product Entity.

Note that for Magento store a product category it depends on six tables: catalog_category_entity, catalog_category_entity_datetime, catalog_category_entity_decimal, catalog_category_entity_int, catalog_category_entity_text, and catalog_category_entity_varchar. Similarly, the representation of a product depends on six other tables. The auxiliary tables are basically the same, because the only difference is the data type of the value column, which can be discovered by analyzing the table name. For example, the table vatalog_category_entity_text has the data type text for the value column. This strategy of entity definition is repeated in various parts of the data model.

In general, the Magento data model is too complex, highly inefficient, occupies much more disk space than necessary and probably generates many points of slow access when there are many data stored in the tables. However, this way of modeling entities has an ulterior motive: Magento uses this strategy to enable a high level of customization that is only achieved when working this way.

Comparison between the CMS' data models

The choice of using a CMS like WordPress, Joomla, Drupal or Magento cannot be so simple for those who are not familiar with these tools. To help in the decision, Table 1 presents a comparison of simple technical details of each of the CMS discussed. The comparison criteria involve the DBMSs supported, the number of tables, the license, and a recommendation for use. The values   placed for criterion recommendation to use are only suggestions, since each CMS has several components, extensions, plug-ins, add-ons, and other features that can include various functionalities and create new tables in the database. For example, WordPress has several extensions that allow it to be an online shop as well as extensions more cosmetic which allow the use of rating tags for posts, traffic reports, and podcast players among other features.

Table 1. Technical comparison between the CMS discussed in this article.


The production and content management for the Internet is currently carried out by content management tools known as CMS. These tools are ready platforms that can be customized to provide a suitable layout and also to meet other needs not covered by CMS or its components.

Because CMS is a ready platform to be used, they need a database for storing data related to the content, layout and its internal configurations. Based on the currently used major CMS (WordPress, Joomla, Drupal and Magento) this article presents their data models and discusses some technical aspects that may be of interest to those who work using these platforms for content management.

The article details various aspects of models such as typing, naming, organization, relationship, use of constraints, modeling techniques, storage and availability of each of the data models. From the explanations and criticisms of the models provided by this article, readers can begin to familiarize yourself with the layout and the data storage mechanism used by each CMS because this knowledge is useful when there is a need to implement new features in CMS or if it is necessary to perform some administrative task in the database that contains the CMS' data model.

This last part explained some details and characteristics of the Magento data model, the more complex CMS among those explained in this four-part article. This part also presented a simple comparison of technical characteristic of the CMS in order to aid the developer when he/she is considering what CMS to use for web content publishing.

To see the third part, access:






Mauro Pichiliani has the Master of Science degree on collaborative systems by the Aeronatics Institute of Technology (ITA) in Brazil. He is a specialist on database technologies with more than 8 years of experience on the industry...

What did you think of this post?
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!

> More info about MrBool Credits
You must be logged to download.

Click here to login