× 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 Joomla data model – Analysis of the WordPress, Joomla, Drupal and Magento data models – Part 2

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 second part will describe the details of the Joomla data model. The Joomla CMS is another popular CMS that have many features that goes beyond the simple content editing and production.

The Joomla data model

The Joomla CMS is a widely used for the creation of blogs and web content portals. Created from a division of the Mambo project, it has many similarities with WordPress and its official website is: http://www.joomla.org. Officially, Joomla can be installed with MySQL and/or the MySQLi extension and similarly to what happens in WordPress the Joomla data model of does not contain foreign keys. Figure 1 presents the data model used in version 1.6 which has 35 tables. Like WordPress, you can choose the prefix that will be used for the table names during the installation. In this example the prefix jos_ was used by the suggestion of the installer.

Figure 1.
 The data model of Joomla 1.6.

There are several points of the model and features of Joomla that can be analyzed. The main points to be highlighted are the lack of standardization, problems of nomenclature, types of data, lack of standardization, and completeness of the model.

The Joomla data model has 35 tables divided into groups such as content publishing, components, menus, templates and more. The main model table has the suffix _content and is responsible for storing information about the content (such as posts on a blog). This table focuses too much information in it and has almost 30 columns. Obviously, not all columns will be filled when creating a new post and many of these columns have values   that clearly could be better used if they were at another table. For example, the pair of columns publish_up and publish_down, both DATETIME, store the date the post was published and the date it was taken down. Thus the data model will only store the initial date of publication and the last date on which the post was taken down. These characteristics are important, but even in cases where a post is inserted and removed from the site several times there is no storage of history that shows all the dates on which the post has changed. This pattern is repeated over several other tables in the model.

Another point that stands out is the lack of Boolean data types, which store values   0/ 1 or TRUE/FALSE. In many tables there are columns that apparently could benefit from the Boolean data type such as the table _contact_details which contains a column called published which was created with the data type TINYINT (1). In addition, there is also the excessive use of columns of the TEXT data type designed to enable data storage limited only by server resources and not by a fixed number of characters. The excessive use of columns of type TEXT can eventually become a problem due to the large number of characters stored and the need for specific backups for these tables. Not to mention that the indexing and searching for this type of data should be special, something that is not addressed directly in Joomla.

From the standpoint of modeling, in some situations the model meets certain requirements fairly well, such as managing users, groups and permissions. On the other hand, some tables should be implemented as plug-ins or extensions and not directly in the standard model as is the case of assessment (rating) for a post for which data are stored in the entity _content_rating. Another example of tables that would be better placed in a plug-in or extension is related to the creation of polls (polls), whose data are stored in tables _polls, _polls_menu, and _polls_data.

Another aspect to be considered in the model are the tables responsible for storing  advertising information (_banner, _bannerclient, and _bannerfinish) that probably came as a remnant of the Mambo database model which Joomla originated. Currently the model for advertising in blogs has evolved over the older banners style so these tables ended up in oblivion because of features and requirements of campaigns with sponsored links and integration with affiliate programs.

Although it is not difficult to understand the model once you become accustomed to it, in some situations is difficult to get used to the odd nomenclature and the non-traditional terms. For example, there isn't the word comment on any table or column. In the Joomla data model this form of user interaction with the post is called message. Also, the term template in Joomla is used strictly for menus and makes no direct reference to how to customize the layout of the site.

Again, as well as WordPress, Joomla data model has no other domain integrity or stored procedure that restricts what is inserted in the database. Though not mandatory, this practice opens room for problems such as SQL injection where a malicious user may try to invade the site by means of special characters. Moreover, the lack of data integrity may invalidate the permit application, for example, put the value -2 in the count column of the _categories table. There are also some table columns where the NULL value is allowed, requiring an additional check on the application.

In summary, one can say that the Joomla’s database model is strong and meets needs for blog posting but it is a model that carries much of the Mambo project. The highlight will be the focus of the database storing information of both content and users, settings, comments, ratings, menus, etc. Surely this model can be improved in the aspects of nomenclature standardization, typing, and other standards. And this improvement is the responsibility of the community of developers and users of Joomla. Unfortunately, profound improvements in the database are more complex to implement as the project is becoming increasingly mature and popular.


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 second part discussed some technical aspects of the Joomla data model focusing on how the data types, tables and other aspects may difficult the modification of this model when there is need to handle it.

In part three, we will see the Drupal data model.
To see the first 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