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