Natural or surrogate key

Mattiman · #1 (**permalink**) 04-30-11, 12:58

So to follow up on another thread here Return count even when no values
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:

Code:

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:

Code:

CREATE TABLE `mc_grades` (
	`grade` VARCHAR(3) NOT NULL,
	PRIMARY KEY (`grade`)
) ENGINE=InnoDB;

or a surrogate autoincrementing id, like:

Code:

CREATE TABLE `mc_grades` (
	`grade_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`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.

r937 · #2 (**permalink**) 04-30-11, 13:25

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

Mattiman · #3 (**permalink**) 04-30-11, 13:53

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech