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 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:
    Code:
    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:
    Code:
    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:
    Code:
    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. ^_^

  • #2
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    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.
    OracleGuy

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,111
    Thanks
    75
    Thanked 4,335 Times in 4,301 Posts
    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).


  •  

    Posting Permissions

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