Note: This post is extremely advanced and is aimed at Joomla administrators with a solid technical background.
We love Joomla. We think that it’s one of the best CMS’s out there, if not the best CMS. That’s why we think it’s good to constructively criticize it from time to time so that it remains where it is right now (and hopefully become better).
Let’s start…
One of the most annoying characteristics of Joomla, and of any CMS in general, is how database tables are linked. For example, let’s take a look at the #__categories (the table containing the categories). That table is linked to many other tables through the ID field. For example, we know that a particular article belongs to a certain category because the ID of that category is referenced in the catid field in that particular’s article row (in the table #__content). Now, all this seems fine and clean, and it’s considered to be standard practice when it comes to database architecture. But, is it the best practice? (Yes, we know, we have italicized the word best twice so far in this post!)
We don’t think so!
To answer the first question that you might have on your mind: “No, we have not finally lost our senses” (that’s a quote from Alice in Wonderland, except that the not is not there). In order to prove that, let us tell you what happened to us today…
We were migrating a Joomla website (from Joomla 1.5 to Joomla 2.5) today, and, as most of you already know, preserving the IDs of the articles in the #__content table is nearly impossible if you want to do a clean migration. Now, for small websites with little or no extra features, this might be harmless, but the website that we were migrating today had one feature that caused us some trouble, and that feature was JComments (the excellent Joomla commenting tool). Each comment in JComments was linked to the article’s ID, but since the article ID association could not be maintained during the migration, we had data integrity issues: comments were no longer linked to the right articles (some were not linked to any articles!).
A database administrator would say that this is very normal in a relational database, but we beg to differ. You see, the problem is that during the migration, the new article IDs were generated automatically, because the article ID is an Autonumber field in the database (e.g. the database will automatically assign the next number in the sequence for each new article [The first article’s ID is 1, the second’s ID is 2, etc…]). But what if Joomla doesn’t use Autonumber at least in its main tables (such as the #__content table, the #__menu table, the #__categories table, etc…) and use a hash that is calculated based on a unique combination of 2 fields (such as the title and the creation date)? That hash will remain static and unique in time and space (OK, we’re getting a bit philosophical here…) so any move of the data anywhere will not affect the table relationships in the database, because all the tables will be using that unique (and static) hash to reference items in other tables.
Now, when would that hash be created? When the entry is created in Joomla, of course! For example, when you create a new article, Joomla will automatically create its hash based on its creation date and its title.
We know that this concept sounds too good to be true – but it is true, and it can work. The problem is that most database architects will refuse even looking at it because it’s “not the way we do things” and because “there might be some serious repercussions” (we’re using their terminology). Yes, it’s not the way we do things, and there might be some repercussions, but isn’t this whole concept worth at least a try?
Now let’s get back to you, our dear reader! If you’re having data integrity problems after the migration of your Joomla website because of the above issue, then fear not, we can help! We can write a customized script that will remedy your data. All you need to do is to contact us and rest assured, we won’t charge you much and we will get the job done!