Some Database Basics

At the request of a client I've been writing a Joomla module that will allow them to import data from a CSV file into the database used by another module. I thought it would be a fairly straight forward job until I started development, and then.... well let's just say the original developer doesn't seem to know the difference between a flatfile and a relational database model.

That said, from a user's point of view, they've done a good job. So in order to avoid detracting from their product, I'll change a few details! In this article, I'll not only show the right way to lay our tables out, but also how this particular developer opted to do it.

So let's say we wanted to sell food, and a wide variety of food at that (think ASDA online or similar). Obviously

It's reasonable to think we'd have a few tables including;

• Categories
• Products

We'll stick with these two as they are sufficient for the examples I want to show

So, Categories would probably contain

Fairly simple so far. Just to clarify  primary key is a unique identifier used to refer to the record from other tables. It also means you can have two categories called 'Deserts' (although it'd probably confuse your customers)

Now we have our product table, we'll keep this basic though;

Rather than storing the Category Name in the Products table, we store it's Primary Key. So in the Products Table the field 'Category ID' acts as the foreign key. A foreign key is linked to the relevant Primary Key by the Database software, which simplifies a lot of things (see later in the post)

Where our unnamed developer went wrong, was right here. They had a relational database set up, with a separate table for their Category, but then opted to store both the Foreign Key and the Category name in the products table.

For the life of me, I cannot work out why! It's half way between a flatfile and a relational database, and simply makes no sense to me at all! It also had the knock on effect that if you mis-spelt the category name, you had to re-edit every 'Product' to which it had been applied. Now imagine what it's like in this scenario when there are nearly 15 related tables, from a developers point of view the component was an absolute mess. Had the component been designed to sell food, I'd have felt less frustrated, but the target markets is people selling a product in which I have no experience whatsoever. So not only was I trying to work out the database schema, I had to read a quick 'howto' on selling the end-product to ensure I didn't miss anything important.

To me, it highlights both a major strength and weakness of Joomla. Because anyone can write a module, you attract people who are experts in fields other than programming (say selling cars) who know exactly what they need. The downside is that because they are not developers, it can quickly become very frustrating!

The author of the component has done a relatively good job, and it's borne out of the experience in his/her chosen area. Whilst the database would have been more effectively laid out, I cannot say I'd have managed to create such an effective piece of software without serious consultation with someone in that market. What would be perfect for me, is if we could teach more people the very basics of Databases and programming