Joomla! Coding Practices: Tables and Primary Keys
When you’re defining one or more tables to use in your component, it’s always a good idea to put some thought in how you’ll name them. First of all, we have of course the #__ prefix, which Joomla! replaces with the user defined prefix. The default is jos_, which stands for ‘Joomla! Open Source’, a leftover from the Mambo days.
Next, we want to prevent name collisions. Component names in Joomla! are unique, so using that for the first part of your table names will fix that: #__mycomponent_. Don’t use #__com_mycomponent_, that’s unnecessary luggage.
Let’s take a classic example: a component that manages a book collection, called com_library. The table name should reflect the contents of the table. This is always plural: you use a table to store multiple items.
Even if your component uses only one table, you shouldn’t use #__mycomponent. You might need more tables later, eg #__library_authors and #__library_publishers.
Finally, you always need to define a primary key. Most people simply use id for this, or something like bId for the books table, aId for the authors etc. A better idea is to use the table name, in singular, followed by id.
#__library_books -> library_book_id
#__library_authors -> library_author_id
There are some very important advantages to this naming strategy. It’s easier for you or other people reading your code, what tables belong to what component, and what’s in them. Relations between tables are now very clear as well. #__library_books for example has two additional fields called libray_author_id and library_publisher_id, which of course link an author and a publisher to the book.
But more importantly, it’s easy to write code now that can detect those relations automatically. As your com_library becomes smarter, you could add data mapping functionality that can understand the relations between tables, without the need to hardcode them.
Finally, in most situations, you’ll want to use auto_increment for primary keys. As we’re all moving to PHP5.2 and along with that, hopefully MySQL 4.1 or 5, we can make use of new feature: the SERIAL keyword. SERIAL is just an alias BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. This allows your primary keys to be as big as possible (and it’s easier to remember as well).
In Joomla!, we’re still stuck with backwards compatibility, but hopefully, these coding conventions will be picked up in a future version. Component developers can already start using them. The possibilities are endless.