Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2008

    Unanswered: Natural or surrogate key

    So to follow up on another thread here
    I wonder what to choose as the primary key for a new "lookup" table I'm creating. What I have now is the following table:
    CREATE TABLE `mc_routes` (
      	`route_id` int(10) unsigned NOT NULL auto_increment,
      	`route_name` varchar(255) NOT NULL,
      	`route_grade` enum('3','4','5a','5a+','5b','5b+','5c','5c+','6a','6a+','6b','6b+','6c','6c+','7a','7a+','7b','7b+','7c','7c+','8a','8a+','8b','8b+','8c','8c+','9a','9a+','9b','9b+') NOT NULL,
    	`sector_id` INT(10) unsigned NULL,
      	`area_id` int(10) unsigned NULL,
      	PRIMARY KEY  (`route_id`),
    	UNIQUE KEY (`route_name`, `route_grade`, `area_id`),
    	FOREIGN KEY (`sector_id`) REFERENCES mc_sectors (`sector_id`),	
      	FOREIGN KEY (`area_id`) REFERENCES `mc_areas` (`area_id`)
    )  ENGINE=InnoDB;
    As you can see, the grades start at "3" and go up until 9b+. Currently the most difficult climb in the world is about 9b. In the future it could increase. Not sure how much though.

    However, because of the reason mentioned in the other thread, I'm going to create a separate table for the grades. However, I'm unsure what to do. Either use the natural key:
    CREATE TABLE `mc_grades` (
    	`grade` VARCHAR(3) NOT NULL,
    	PRIMARY KEY (`grade`)
    ) ENGINE=InnoDB;
    or a surrogate autoincrementing id, like:
    CREATE TABLE `mc_grades` (
    	`grade` VARCHAR(3) NOT NULL,
    	PRIMARY KEY (`grade_id`)
    ) ENGINE=InnoDB;
    The advantage of the surrogate key would be that in case I do need to change the grades in the future, it's quite easy to do so since I only have to change a dozen lines in the grades table.

    And the second question is, what to do with the field route_grade in the mc_routes table. Leave it as the enum field? Or change it to a varchar when I use the natural key for the grades table or use an int when I use a surrogate key for the grades table.

    I know the general subject of natural vs surrogate keys has been discussed quite often and I have read a couple of threads. But it seems there's not a real consensus on what to do, or a "it depends" situation. So I'm unsure what to do in my specific example. Any help is appreciated.
    Last edited by Mattiman; 04-30-11 at 13:59. Reason: change a typo

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    in this particular situation, let me pose the following scenario

    suppose you had a bunch of grades, each represented by a surrogate key

    1 = '3'
    2 = '4'
    3 = '5a'
    4 = '5a+'
    5 = '5b'
    6 = '5b+'
    and so on

    then you say "The advantage of the surrogate key would be that in case I do need to change the grades in the future, it's quite easy to do so "

    i submit that it isn't

    under what circumstances would you want to change a single grade called, say, "5a+" to some other name? what would you call it, "5aplus"? "hardest 5a but not quite 5b"? these changes are trivial, and yes, you'd only have to change the name, the key value 4 wouldn't have to change

    but really, is this likely going to happen? changing only one grade name?

    more likely, you'd be changing all of them to a completely different grading nomenclature, and then the change probably ~isn't~ going to be trivial, and the surrogate key won't buy you anything

    but consider if you were using the grade name itself as the key

    the most significant advantage of this is that you won't always have to do a join to the grades table, just to find out what key value 11 means -- test yourself, quick, what grade is that?

    see what i mean? you really wanna always do a join in all your queries?

    and as for the ENUM, get rid of it, and define a proper foreign key

  3. #3
    Join Date
    Aug 2008
    Thanks for your reply. It gives some things to think about.

    About the changes: it will probably not happen quickly. Otherwise I wouldn't have chosen the enum field in the first place. The thing that I can think of is that I might have to expand the scale. So add another grade, say 9c. Not sure if that happens in 5 or 20 years.

    There is one thing with the grades though. I use the so called "french" scale: 5b, 5c, etc. But there are many. Just take a quick look at this page and the comparison table down below
    Grade (climbing) - Wikipedia, the free encyclopedia
    so I can imagine that I might want to do something with those other grading scales in the future. At the moment I don't think so, since the french grade is kind of the golden standard. But for example Americans use the Yosemite scale. So who knows, maybe I could change the application for American users to show the US scale?

    But you are right. Such a change will not be trivial anyway.

    About the enum field: I'll change it.

Posting Permissions

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