...

View Full Version : Database Normalization Opinion



Chris Hick
08-09-2011, 06:33 AM
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;


cities table sql:


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 ;


states table sql:


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 ;

oracleguy
08-09-2011, 08:01 AM
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.

Old Pedant
08-09-2011, 08:07 AM
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).



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum