Thread: Natural or surrogate key
04-30-11, 13:58 #1Registered User
- Join Date
- Aug 2008
Unanswered: Natural or surrogate key
So to follow up on another thread here http://www.dbforums.com/mysql/166606...no-values.html
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;
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;
CREATE TABLE `mc_grades` ( `grade_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `grade` VARCHAR(3) NOT NULL, PRIMARY KEY (`grade_id`) ) ENGINE=InnoDB;
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
04-30-11, 14:25 #2SQL Consultant
- 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
04-30-11, 14:53 #3Registered User
- 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.