In the recent profile of GB4K.com, I talked about the use of the K2 component from the Folks at JoomlaWorks. While doing some customization, I decided to reverse engineer the K2 tables that are created and used by the component. I provide the diagrams for interested parties and developers looking to understand how K2 is designed. Keep in mind that these are logical diagrams, since Joomla and K2 have no intrinsic support for MySQL other than using the MyISAM engine, which doesn't support key constraints. Click on the image to get the .png version, or
this link for a .pdf.
One thing I noticed about K2, is that its tree traversal code can require a lot of queries to be run, especially if you have a lot of categories. Over on the K2 forums, one K2 user with a large number of subcategories was complaining about the substantial overhead involved in displaying the top level Category for their site. With over 1000 subcategories, the site was being hammered with an equal number of queries.
This is because by default K2 will do a query for every category to find its child categories and items. Since this is a recursive problem there's no inherent MySQL solution that would allow the developers to solve this problem in SQL (like the Oracle Start With ... Connect By syntax) so it's somewhat understandable that the JoomlaWorks developers provided a short circuit mechanism into the category configuration they call "Catalog Mode". The description of this configuration flag, which defaults to "No" is:
If you select 'yes' then only the items directly belonging to this category will be retrieved. If you select 'no' then items from all sub-categories will be retrieved. Setting this option to 'no' is ideal for news/portal/magazine/blog layouts.
This setting simply shuts off the recursion behavior for the category.
I have a small project that involves an implementation of Joomla (a fork of the popular PHP/Mysql based content management system Mambo). I started with Mambo and during the time I was configuring the site, the core developers of Mambo left to create the Joomla project. The reasons for this are discussed on their
OpenSourceMatters website.
Suffice it to say, I find an Entity Relationships diagram to be very helpful in understanding the architecture of a database driven application like Joomla. There wasn't one available, so I decided to reverse engineer the database, and created these diagrams, which break the core database down into a few functional areas.
UPDATED At one time my pdf version of this was hosted by Joomla, but in one of the many moves of source code, the links to it were broken.
You can get the pdf with all the views here.
User and Security Subsystem
Backend (modularity and extensibility) Subsystem
Content and Presentation Subsystem
Banners
Logging
