mikebernat.com Web-Development, Design, Usability

Relational Database Design is one of the most powerful ways to ensure data integrity and a great way to kick-off any project. Very often the first thing developers do when starting a new project, or stub-project, is to design the database. This way the structure of the application is already in place and we just have to fill in the pieces with some server-side code. I've found when adding relational constraints to your database design you add in a very powerful error reporting tool that will let you know during the development process that you have allowed something to happen that shouldn't have. In this article, I go through, step by step, showing how to set up a simple relational database and discuss the benefits that are enjoyed.

Let's take a step back and describe what a relational database looks like. In any normal database design there are fields in one table that reference another table. For example, a books table might have a field labeled author_id which is meant to come from a table named authors. Creating hard-coded relations solidifies these associations and actually returns a MySQL error if violated.

As I hinted in the opening I have found this to be invaluable during the development and testing process as MySQL will immediately let me know that I have made a glaring error that otherwise may not have been noticed until after the service has launched. At that point the data could be irreparably corrupt and forced to start from scratch.

So let's get right to it. For the purposes of this article, I'm going to pretend I'm creating a simple Books and Authors website with a simple 2-table setup. The first step is to create our tables.

CREATE TABLE `library`.`books` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `name` VARCHAR( 150 ) NOT NULL , `author_id` INT UNSIGNED NOT NULL , PRIMARY KEY ( `id` ) , INDEX ( `author_id` ) ) ENGINE = InnoDB

CREATE TABLE `authors` ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar(50) collate utf8_bin NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Nothing too fancy here. Couple things to notice:

1. Each table MUST be using the InnoDB storage engine. InnoDB is currently the only main-stream storage engine offered by MySQL to support relational design. More on this in my article: MyISAM vs InnoDB
2. The `author_id` field in the `books` table MUST be indexed and the same datatype as the `id` field in `authors`.

The next step is to set up the relations. Open the `authors` table and take a look at the view. Under the table there should be a link titled 'Relation View' - Click it.

phpMyAdmin has a great gui for setting up relations and actions. If the `author_id` row below doesn't look like mine, make sure you have it indexed.

Here, I've setup a link on the `books` table and the `author_id` field. This will enforce the fact that any value inserted in this field MUST be present in the `authors.id` table as well. But what about these other settings?

ON DELETE:

• CASCADE:
  
  • This means if an author is deleted from the authors table, all of his books will also be automatically deleted.
  • This option is great to keep your data clean and reduce the number of delete quieries required when deleting an author.
    
• SET NULL:
  • Instead of deleting the book record when an author is deleted, books.author_id is set to NULL, effectively orphaning the book.
    
  • This feature is great if you want to be able to keep the books and come back at a later time to reassign them. Otherwise, without this feature, the books would still be referencing an author_id that doesn't exist.
  • Note: If you try to set this option and phpMyAdmin tells you to check your datatypes, make sure the field is allowing null values.
• NO ACTION:
  • When a delete query is issued on an author that has books, MySQL will not allow this and return a Foreign_Key Constraint error. 
  • It could be nice to identify this and re-word it to let the user know that if they would like to delete this author they need to re-assign his books or delete them all-together.
  • Note: If you use this option please remember to re-word the MySQL error to something the user can easily understand.
• RESTRICT:
  • Same as NO ACTION
  • From MySQL Manual: Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION and RESTRICT are the same.

ON UPDATE:

• For the most part the options described above are going to act in the same manner they did for ON DELETE as they will with ON UPDATE. I'll just run through some examples real quick.
• CASCADE:
  • If, for some reason, an author's id gets updated than CASCADE will update all his corresponding books with the new value. Extremely handy.
• SET NULL
  • Same as CASCADE except instead of updating it with the new value, it will set it to null. I'm sure there is a perfectly good use for this but I haven't run into it yet. If anyone can enlighten me please do :)
• NO ACTION / RESTRICT:
  • Same as ON DELETE, will throw an error if you try to update an author_id. I'm also having trouble finding a real-world example of when this could be useful

Once we have our simple relational database configured try to add a book with an author_id that doesn't exist. MySQL should give you an error like this:

Cannot add or update a child row: a foreign key constraint fails (`library/books`, CONSTRAINT `books_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

That about does it. If you have any questions or problems leave a message in the comments and I'll be happy to help.