Torkil Johnsen

My personal piece of cyberspace

Joomla 1.6 database schema

Posted in Databases,joomla on Jul 11th, 2010

joomla 1.6 database schema thumbnailAs some might remember I made a visual representation of the Joomla 1.5 database schema back in 2006. I have now set up an EER representation of the Joomla 1.6 database schema too, that you can download for free. This schema was made after the 1.6 beta 2 release, and it was built using MySQL Workbench, which is both GPL and available as a free download for multiple platforms.

First of all: If you find any errors in this schema, make sure you leave a comment for me at the bottom. Because of the lack of conventions for naming primary key columns, I am left to do a lot of guesswork here when drawing up table relations, so there may be errors. Pretty much the only table that’s easy to read is the menu table, which has descriptive column comments. Make sure you read the notes below the download links too.

Update: With the release of 1.6 beta 5, I just did a diff between the schema for 1.6 beta 2 and 1.6 beta 5, and they are identical.

DOWNLOAD available in different formats:

Schema modifications

Here are some modifications that I personally have done in various places in the schema to be able to reproduce it visually in MySQL Workbench, and make it more readable. This does mean that that this schema is not 100% identical to an actual Joomla 1.6 installation, because of these changes. For instance it also has foreign key relations drawn in, whereas MySQL’s MyISAM tables don’t support those. This schema should therefore only be used as a visual supplement to Joomla development.

Here are the changes that makes this schema differ from an actual Joomla 1.6 installation:

Invisible relations

This is not something I have changed, but something I deliberately did not touch. For claritys sake, some foreign relations to the users table have been left out. For instance created_by, modified_by, locked_by, created_user_id, modified_user_id and checked_out all reference the field users.id. If I was to set up all these relations, the schema would become so crowded with relationship indicators that it would be virtually unreadable.

There is also a “relation” here between the tables viewlevels and usergroups, but that’s not solved in SQL: All usergroups that belong to a viewlevel are put into an array that is JSON encoded and stored in the viewlevel.rules field. This is not a foreign key in SQL, so the relation is not show in the schema.

Changed column types

If a column is referencing another column in the database (like a foreign key), these columns need to be the same datatype. A lot of the time, this is not the case in Joomla, for some strange reason. To represent real foreign key relations in MySQL Workbench, this needed to be changed, or else MWB would not even allow the relations to be made at all. We have had issues like these all the way back since Joomla 1.0. Hopefully they are picked up and fixed soon, because fixing this should not be a problem for backwards compatibility.

Here are the columns I have had to change:

  1. modules_menu.moduleid has been changed from an INT to an INT(11), since it references modules.id
  2. In two places, asset_id foreign key was of datatype INT, but referencing asset.id it should have been INT(10), so those foreign key relations have changed, and this affects the following tables; categories, content
  3. Similar to asset_id, catid columns were also of datatype INT, whereas they should have been INT(11) since they are referring to categories.id, which is an INT(11). This occured in the following tables: banners, contact_details, content, newsfeeds, weblinks
  4. user_usergroup_map.user_id, messages_cfg.user_id, messages.user_id_from, messages_user_id_to all reference user.id, hence they can’t be an unsigned INT, but has to be a signed INT.
  5. Multiple occurences of user_id references user.id, so they must be INT, not INT(11). This affects the following tables: user_profiles, session (although the field is named userid in the session table, without the underscore).
  6. schemas.extension_id has been changed from INT(11) to INT, since it references extensions.extension_id
  7. update_sites_extensions.update_site_id changed from INT to INT(11), since it references update_sites.update_site_id, which is an INT(11). Both columns in update_sites_extensions got a NOT NULL attribute added, since they make no sense without it.

Changed column ordering

Column ordering in some tables has been changed so that foreign keys are listed just below primary keys, as I feel that this is cleaner and makes the relations easier to read. The changes were so many that I gave up trying to write a complete list.

All in all, this schema badly needs an overhaul, to say it mildly. I have written a short blog post explaining why I think the Joomla database schema smells.


Creative Commons License
This work by Torkil Johnsen is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.