Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Oct 2010
    Location
    Florence, MS
    Posts
    486
    Thanks
    10
    Thanked 33 Times in 32 Posts

    Database Normalization

    NOTE TO ADMINS: I believe I may have posted this in the wrong section. I believe it should be moved to mysql.

    Ight, what I am doing is trying to optimize my database as thorough as I possibly can before I begin the next steps of developing the site. What I would like is an opinion of what I already have and ways of improving it.

    Here are the tables I have as of right now:





    Users
    1. user_id (primary key)
    2. email_id (foreign key)
    3. rank_id (foreign key)
    4. user_info_id (foreign key)
    5. username
    6. password
    User_Info
    1. user_info_id (primary key)
    2. user_id (foreign key)
    3. zipcode (foreign key)
    4. first_name
    5. last_name
    6. date_of_birth
    7. about_me
    Email
    1. email_id (primary key)
    2. user_id (foreign key)
    3. email_address
    4. status
    5. activation_key
    Rank
    1. rank_id (primary key)
    2. user_id (foreign key)
    3. rank
    4. description
    Zipcode
    1. zipcode (primary key)
    2. user_info_id (foreign key)
    3. city
    4. state
    EDIT:

    I also thought that maybe I could do this as well in which the user_id in the Users table would be the same as the primary keys of the user_info, email, and rank tables What do you guys think? I don't think I thought this bottom one as thorough as much as I could:



    Users
    1. user_id (primary key)
    2. email_id (foreign key)
    3. rank_id (foreign key)
    4. user_info_id (foreign key)
    5. username
    6. password
    User_Info
    1. user_info_id (primary key)
    2. zipcode (foreign key)
    3. first_name
    4. last_name
    5. date_of_birth
    6. about_me
    Email
    1. email_id (primary key)
    2. email_address
    3. status
    4. activation_key
    Rank
    1. rank_id (primary key)
    2. rank
    3. description
    Zipcode
    1. zipcode (primary key)
    2. user_info_id (foreign key)
    3. city
    4. state
    Last edited by Chris Hick; 08-02-2011 at 10:30 AM.
    Notice: If you post a problem and it gets fixed, please remember to go back and place it as solved. ;)
    I always recommend the HEAD First series of books for learning a new coding language. ^_^

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,652
    Thanks
    80
    Thanked 4,640 Times in 4,602 Posts
    Seems like over-normalization, to me.

    Do you really expect users to have multiple email addresses? Many system use an email address as the primary key (user id) for a user, just because email addresses are guaranteed to be unique.

    Even if you expect multiple email address per person, you have your design backwards: The userid would be the primary key and then in the email table you would use it as the foreign key (there wouldn't be any primary key in the email table).

    As your design exists, you are saying "for each email address, there will be several users". While I grant that maybe a husband and wife might share one email address, I'd expect that you'd then treat them as a single user.

    But I'm just saying what I perceive as "normal". If you really have a rationale for many users per email address, go for it.

    The same thing applies to your USER_INFO table. You really expect that each userid will be associated with many users??? That's what your design says. (Come to think of it, if that's true, then why isn't the email address associated with USER_INFO instead of USERS???)

    But finally, we get to your ZIPCODE table, and it makes no sense at all. As it is, you have it doubly linked to the USER_INFO table. That means that each record in the zipcode table can't possibly be associated with more than one USER_INFO record. As a general rule, you should never design a database that involves required one-to-one tables. (Okay for one-to-one to occur just because of data patterns, but not by design.)

  • #3
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    Thanked 328 Times in 324 Posts
    Building on what Old Pedant said, your design is over normalized. I've seen this before, it is kind of like what happens when someone first learns CSS layouts and overuses DIVs.

    Unless you need to have more than one first and last name associated with a user, I would merge the users and user_info table together. Your zipcode table shouldn't have a link back to the users table as Old Pedant said.

    Again if you want to support having more than one email associated with an account (a much more likely use case than having multiple people) I would get rid of the email_id column and just make a composite unique restriction between the user_id and the email_address columns.

    (Ok, maybe what I said isn't all that more than what Old Pedant said that should just emphasize how correct he is on this.)
    OracleGuy


  •  

    Posting Permissions

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