...

View Full Version : Database Normalization



Chris Hick
08-02-2011, 08:58 AM
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
user_id (primary key)
email_id (foreign key)
rank_id (foreign key)
user_info_id (foreign key)
username
password
User_Info
user_info_id (primary key)
user_id (foreign key)
zipcode (foreign key)
first_name
last_name
date_of_birth
about_me
Email
email_id (primary key)
user_id (foreign key)
email_address
status
activation_key
Rank
rank_id (primary key)
user_id (foreign key)
rank
description
Zipcode
zipcode (primary key)
user_info_id (foreign key)
city
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
user_id (primary key)
email_id (foreign key)
rank_id (foreign key)
user_info_id (foreign key)
username
password
User_Info
user_info_id (primary key)
zipcode (foreign key)
first_name
last_name
date_of_birth
about_me
Email
email_id (primary key)
email_address
status
activation_key
Rank
rank_id (primary key)
rank
description
Zipcode
zipcode (primary key)
user_info_id (foreign key)
city
state

Old Pedant
08-02-2011, 08:53 PM
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.)

oracleguy
08-03-2011, 12:54 AM
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.)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum