Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    May 2012
    Posts
    14

    Unanswered: Database Model, Foreign Keys Correct?

    Hello.

    I am new to MySQL, I've been researching databases and creating a database model which supports users, connections (friends), support tickets, feedback, log and roles (permissions).

    I have setup the tables to use InnoDB, my main question is if I am using foreign keys correctly.

    My database model is available from the link below.

    Database Model (Requires MySQLWorkbench)
    Database Model SQL Script

    Any help would be appreciated. Thank you.
    Last edited by EpicKris; 06-03-12 at 22:22. Reason: Added SQL script link

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by EpicKris View Post
    Requires MySQLWorkbench.
    too bad i don't gots

    if you could do a SHOW CREATE TABLE for each table, and post the results as text, that might let more people see your table design
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2012
    Posts
    14
    Quote Originally Posted by r937 View Post
    too bad i don't gots

    if you could do a SHOW CREATE TABLE for each table, and post the results as text, that might let more people see your table design
    I edited my original post to include this.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, i've had a look at your foreign keys... several of them are missing, and some of them won't work, e.g.
    Code:
    FOREIGN KEY (`user_id` , `connection_user_id` )    
    REFERENCES `global`.`global_users` (`user_id` , `user_id` )
    also, a number of your primary keys are wrong, e.g.
    Code:
    PRIMARY KEY (`connection_id`, `user_id`, `connection_user_id`, `relation_id`)
    plus, you have some redundant indexes which are redundant, e.g.
    Code:
    INDEX `global_users` (`user_id` ASC) , 
     UNIQUE INDEX `user_id_UNIQUE` (`user_id` ASC) ,  
    PRIMARY KEY (`user_id`)
    i think what you might want to do is actually test your table creation scripts and work your way through the error messages
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2012
    Posts
    14
    Quote Originally Posted by r937 View Post
    okay, i've had a look at your foreign keys... several of them are missing, and some of them won't work, e.g.
    Code:
    FOREIGN KEY (`user_id` , `connection_user_id` )    
    REFERENCES `global`.`global_users` (`user_id` , `user_id` )
    also, a number of your primary keys are wrong, e.g.
    Code:
    PRIMARY KEY (`connection_id`, `user_id`, `connection_user_id`, `relation_id`)
    plus, you have some redundant indexes which are redundant, e.g.
    Code:
    INDEX `global_users` (`user_id` ASC) , 
     UNIQUE INDEX `user_id_UNIQUE` (`user_id` ASC) ,  
    PRIMARY KEY (`user_id`)
    i think what you might want to do is actually test your table creation scripts and work your way through the error messages
    Okay, I don't fully understand this (sorry, still new with databases). So with the foreign keys, how should they actually be set up. With the primary keys and redundant indexes, why are they wrong?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by EpicKris View Post
    So with the foreign keys, how should they actually be set up.
    the FK must reference either a PK or a UNIQUE key

    i think you understand the mechanism okay, it's just that you don't always choose the right columns


    Quote Originally Posted by EpicKris View Post
    With the primary keys and redundant indexes, why are they wrong?
    if you're gonna use an AUTO_INCREMENT, that alone should be the PK, not in combination with other columns

    and redundant indexes are just that, redundant -- overhead on inserts and deletes, useless on selects

    best advice i can give you is to actually run the CREATE statements, a couple of tables at a time, and work through any error messages you get

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2012
    Posts
    14
    Quote Originally Posted by r937 View Post
    the FK must reference either a PK or a UNIQUE key
    Thank you for the fast reply!

    So taking the first two tables as an example (global_users and global_users_meta).

    Should the FK be created in the global_users_meta table from the column user_id to the global_users table's user_id column.

    Also, does this mean the global_users_meta user_id column, should or shouldn't be a PK?

    Quote Originally Posted by r937 View Post
    and redundant indexes are just that, redundant -- overhead on inserts and deletes, useless on selects
    I'm not sure what an index is to be honest.



    I'll create the tables shortly and attempt to fix any problems I may have. Just want to squeeze out as many as I can before trial and error.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here's your first table --
    Code:
    CREATE  TABLE IF NOT EXISTS `global`.`global_users` (
      `user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'User ID' ,
      `user_slug` VARCHAR(45) NOT NULL COMMENT 'User slug' ,
      `user_salt` VARCHAR(45) NOT NULL COMMENT 'User salt\nProvides additional protection for password and PIN protection' ,
      `user_pass` VARCHAR(45) NOT NULL COMMENT 'User password' ,
      `user_pin` VARCHAR(45) NOT NULL COMMENT 'User PIN' ,
      `user_date` DATETIME NOT NULL COMMENT 'User registration date' ,
      PRIMARY KEY (`user_id`, `user_slug`) ,
      UNIQUE INDEX `user_slug_UNIQUE` (`user_slug` ASC) ,
      UNIQUE INDEX `user_id_UNIQUE` (`user_id` ASC) )
    ENGINE = InnoDB;
    the PK here should be user_id alone, because it's auto_increment

    the UNIQUE index on user_id is redundant because primary keys are already unique

    as an aside, since user_slug is also unique, you could actually get rid of user_id and make user_slug the PK


    here's your second table --
    Code:
    CREATE  TABLE IF NOT EXISTS `global`.`global_users_meta` (
      `user_id` INT UNSIGNED NOT NULL COMMENT 'User ID' ,
      `user_first_name` VARCHAR(45) NOT NULL COMMENT 'User first name' ,
      `user_last_name` VARCHAR(45) NOT NULL COMMENT 'User last name' ,
      `user_avatar_url` VARCHAR(45) NULL COMMENT 'User avatar URL' ,
      `user_psn` VARCHAR(45) NULL COMMENT 'User PSN\nPlayStation Network' ,
      `user_xbox_live` VARCHAR(45) NULL COMMENT 'User Xbox Live' ,
      INDEX `global_users` (`user_id` ASC) ,
      UNIQUE INDEX `user_id_UNIQUE` (`user_id` ASC) ,
      PRIMARY KEY (`user_id`) ,
      CONSTRAINT `global_users`
        FOREIGN KEY (`user_id` )
        REFERENCES `global`.`global_users` (`user_id` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;
    the FK is correct, provided that user_id in the users table is the sole PK column

    again, the two redundant indexes on user_id in this table are redundant
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    May 2012
    Posts
    14
    So should I only have one index per table basically? And can I have and FK and PK on a table column or just an FK?

    Also, if I say removed a user from the global_users table, using FKs how can I remove all instances associated with that user from the global_users_emails table (say they had two associated emails)?

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by EpicKris View Post
    So should I only have one index per table basically?
    basically, no, you should have one index for every query search requirement, including and especially for join conditions


    Quote Originally Posted by EpicKris View Post
    And can I have and FK and PK on a table column or just an FK?
    yes, a column can be both PK and FK


    Quote Originally Posted by EpicKris View Post
    Also, if I say removed a user from the global_users table, using FKs how can I remove all instances associated with that user from the global_users_emails table (say they had two associated emails)?
    look up ON DELETE CASCADE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jun 2012
    Location
    Denver, CO, USA
    Posts
    20
    You can check out this beginner's tutorial on MySQL foreign keys to learn more about them. It also tells you why they foreign key columns have to be indexed:
    Foreign Keys Tutorial

    For deeper information, see the MySQL Documentation:
    MySQL :: MySQL 5.1 Reference Manual :: 14.6.4.4 FOREIGN KEY Constraints

    It sounds like you should also look up some information on database indexes. just like r937 says, you should have an index whenever you are going to use that column in WHERE statements. In a nutshell, an index is like an index in a book. It organizes the values in a column (or columns), and tells you where to find it in the table. In this way, MySQL can find things faster. This is especially useful in JOINS because if you are joining two tables with 1000 rows (and that's not that big), checking if each row matches some criteria, that's 1000*1000 checks or 1 million. For bigger tables, a "full scan" like that takes forever. An index allows you to test much faster if the rows match, so it speeds things up (think factors of 1000). The down side is that each time a row is added to a table, the index has to be rebuilt to include it, but if you are doing any JOINs, it's worth it.

  12. #12
    Join Date
    May 2012
    Posts
    14
    I've not had much time recently to take a look at this, but finally sat down and read up more on foreign keys, if my understanding is correct the following should work with the code I posted originally. (updated SQL Script)

    Using the first three user tables as an example.

    If I update a user's ID it will reflect in the other tables?

    If I delete a user from the global_users table it will be removed from the other tables?

    If there were multiple entries in the global_users_emails table for the same user, they would all be removed if the user was deleted from the global_users?



    Thanks for your reply, I think I understand it now.

  13. #13
    Join Date
    Jun 2012
    Location
    Denver, CO, USA
    Posts
    20
    You can pick the behavior of how foreign keys work when a parent table is updated/deleted. The options are:

    CASCADE
    SET NULL
    RESTRICT (or NO ACTION in MySQL does the same as RESTRICT)

    Check out the tutorial link that I sent you for a description of what each of those means. As an example, if you set ON UPDATE CASCADE ON DELETE CASCADE, it would have the behavior you said.

  14. #14
    Join Date
    May 2012
    Posts
    14
    That's exactly what I've done. I've read the tutorial you linked me, although could you give me an example where SET NULL or RESTRICT would be used? I've also heard the there's one called DEFAULT, but heard this does not actually work at present, is that true?

  15. #15
    Join Date
    Jun 2012
    Location
    Denver, CO, USA
    Posts
    20
    NO ACTION is the default, but it's just a synonym for RESTRICT.

    What I typically use is:
    ON DELETE RESTRICT ON UPDATE CASCADE

    This means that when you update the parent, the child gets updated too (ON UPDATE CASCADE).

    The ON DELETE RESTRICT means if you try to delete a parent with children, it will actually throw an error. In order to delete a parent with children, you have to delete the children first, then it will let you delete the parent (now that it has no children). This will stop you from inadvertently deleting things without knowing every row that gets deleted.

    ON UPDATE SET NULL means that if the parent is modified the child column actually gets set to NULL (I can't think of a good use case for this, but I'm sure there is one).

    ON DELETE SET NULL means that if the parent is deleted, the child row stays but the column value is set to null. You don't delete the child data, but it allows you to delete the parent.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •