Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
Thread: Database Normalization Opinion
08-09-2011, 06:33 AM #1
- Join Date
- Oct 2010
- Florence, MS
- Thanked 33 Times in 32 Posts
Database Normalization Opinion
I am creating this topic as more of an opinion discussion about taking my three databases to the Third Normal Form. What is your opinion about the below tables? Do you think its overkill? Efficient?
zipcodes table sql:
CREATE TABLE `zipcodes` ( `zipcode` int(5) NOT NULL, `cityID` int(5) NOT NULL, PRIMARY KEY (`zipcode`,`cityID`), UNIQUE KEY `cityID` (`cityID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
CREATE TABLE `cities` ( `cityID` int(5) NOT NULL AUTO_INCREMENT, `name` varchar(40) CHARACTER SET utf8 NOT NULL, `stateID` int(2) NOT NULL, PRIMARY KEY (`cityID`), UNIQUE KEY `stateID` (`stateID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;
CREATE TABLE `states` ( `stateID` int(2) NOT NULL AUTO_INCREMENT, `name` varchar(40) CHARACTER SET utf8 NOT NULL, `abbreviation` varchar(2) CHARACTER SET utf8 NOT NULL, PRIMARY KEY (`stateID`), UNIQUE KEY `abbreviation` (`abbreviation`), UNIQUE KEY `name` (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=4 ;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. ^_^
08-09-2011, 08:01 AM #2
- Join Date
- Jun 2002
- Thanked 328 Times in 324 Posts
For 3NF it technically isn't overkill. But it depends on how much data you need to store. Though if I'm not mistaken (it's late) for your zipcodes and cities tables having those unique constraints on the cityID and stateIDs will prevent you from having more than one entry in that table for a given city or state.
08-09-2011, 08:07 AM #3
Yes, clearly both the UNIQUE keys are wrong.
But other than that it makes sense. You can clearly have many zipcodes per city (and of course many cities per state).