An often asked question on the Nooku Framework mailing list is how to deal with relations in databases. Nooku Framework doesn’t support relations out of the box yet, but fortunately the InnoDB engine in MySQL does!

There are three important rules that must be met in order to create relations:

1. Both tables must be InnoDB tables and must not be TEMPORARY tables.
2. Keys must have similar internal data types.
3. Both foreign and referenced keys need to be indexed.

A basic example

Let’s see the basics through an example:

Assume that we have an articles table with two columns: article_id and title. We have an authors table as well with the columns: author_id and name. Here is the SQL code for the tables:

CREATE TABLE IF NOT EXISTS `articles` (
`article_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`title` VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (`article_id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;

CREATE TABLE IF NOT EXISTS `authors` (
`author_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (`author_id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;

We want to assign authors to articles. An author can belong to more than one articles, and an article can belong to more than one authors. So this is a many-to-many relation. That’s why we need a relation table too, which has article_id and author_id columns:

CREATE TABLE IF NOT EXISTS `articles_authors` (
`article_id` INT UNSIGNED NOT NULL,
`author_id` INT UNSIGNED NOT NULL,
FOREIGN KEY (`article_id`)
REFERENCES `articles`(`article_id`)
ON DELETE CASCADE,
FOREIGN KEY (`author_id`)
REFERENCES `authors`(`author_id`)
ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;

As you can see, we specified the foreign keys with CASCADE and RESTRICT options. Let’s try to delete an article. Notice that relations in the articles_authors table are deleted with it automatically. Now let’s try to remove an author. If the author is referenced in the relation table, then MySQL refuses to delete it. This ensures referential integrity.

Keeping referential integrity

By using InnoDB, you can prevent accidental removal of referenced rows. Other than its roboust data management features, it has usability advantages too. For example some of the database management tools support foreign keys too. For example in Sequel Pro, an arrow is displayed next to the column’s value, and it takes you to the referenced row when you click on it. This little feature can be useful many times.

InnoDB has become the default engine in MySQL 5.5 and has many features over MyISAM. I am highly recommending every developer to consider using it in their applications.

This was just a basic introduction to foreign keys, it is capable of much more! If you are interested, visit the MySQL Reference Manual.

One of the first things Nooku developers experience with Nooku Framework is the magic with database tables. It’s a redemption for Joomla developers that there is no need to create table classes which describe the tables’ structure. Nooku Framework is smart enough to deal with primary and unique keys out of the box. And to go further, it can handle composite keys too, which is rarely supported by other PHP frameworks.

index.php?option=com_harbour&view=boat&id=4
index.php?option=com_harbour&view=boat&slug=queen-mary-2

A look under the hood

Let’s look deep into how this works under the hood. In the first step, the database class fetches column and index information from the database. In the second step, the model requests the parsed information from the table class and creates a state for each key.

… continue reading …