1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

Setting-Up a Relational Database in MySQL

Discussion in 'MySQL' started by hurricanesoftwares, Aug 29, 2008.

  1. hurricanesoftwares

    hurricanesoftwares New Member

    Joined:
    Aug 29, 2008
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Working as a Sr. Developer in a Software Company
    Location:
    On Earth
    Home Page:
    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.
    Code:
    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
    
    Code:
    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
    
    [​IMG]

    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.

    [​IMG]

    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.

    [​IMG]

    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)

    I hope it will help :happy:
     
  2. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,646
    Likes Received:
    86
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    Nice article.
     
  3. hurricanesoftwares

    hurricanesoftwares New Member

    Joined:
    Aug 29, 2008
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Working as a Sr. Developer in a Software Company
    Location:
    On Earth
    Home Page:
    Good to know that you liked it. Did it helped ?
     
  4. codestorm

    codestorm New Member

    Joined:
    May 20, 2008
    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    0
    Location:
    India
    Simple and nice tutorial, but this is only helpful to those who use phpMyAdmin.
     
  5. Oyetunde

    Oyetunde New Member

    Joined:
    Sep 15, 2008
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    student
    Location:
    Bauchi
    i like your work.
    i just started to learn mysql using the phpmyadmin and i'm a little confussed.
    can you help me with materials on phpmyadmin for relatioal database
    thanks
     
  6. SAint

    SAint New Member

    Joined:
    Jul 1, 2008
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    Hello house please i need help on how to setup a Web server. i have tried the help menu on the operating system and the front page.........

    thanks
     
  7. hurricanesoftwares

    hurricanesoftwares New Member

    Joined:
    Aug 29, 2008
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Working as a Sr. Developer in a Software Company
    Location:
    On Earth
    Home Page:
    Hi,

    I would recommend you go by mysql tutorials first. Then create simple databases and perform operations like insert, update, delete, rename, drop, alter etc. from phpmyadmin and sql queries both.
    You can find various mysql tutorials on Google.
     
  8. jspguy

    jspguy New Member

    Joined:
    May 2, 2005
    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    0
    The same article is also found here - ht*p://www.mikebernat.com/blog/Setting-Up_a_Relational_Database_in_MySQL
     

Share This Page