Design – Is using hashes for primary keys a good idea

Usually when you have a table with a multi-column primary key, it's the result of a join table (many-to-many) that became elevated to be its own entity (and thus deserves it's own primary key). There are many who would argue that any join table SHOULD be an entity by default, but that's a discussion for another day.

Let's look at a hypothetical many to many relationship:

Student * --- * Class

(a Student can be in multiple classes, a Class can have multiple students).

In between those two tables will be a junction table called StudentClass (or ClassStudent depending how you write it). Sometimes, you want to keep track of stuff like when the student was in the class. So you'll add it to the StudentClass table. At this point, StudentClass has become a unique entity...and should be given a name to recognize it as such e.g. Enrollment.

Student 1 --- * Enrollment * --- 1 Class

(a student can have many Enrollments, each Enrollment is for one class (or going the opposite way a Class can have many Enrollments, each Enrollment is for one Student).

Now you can query things like, how many students were enrolled in the Chemistry 101 class this past year? Or what classes was the student John Doe enrolled in while attending Acme University? This was possible without the separate primary key, but once you have a primary key for enrollment an easier query would be of these enrollments (by id), how many students received a passing grade?

The determination of whether an entity deserves a PK boils down to how much querying (or manipulation) you will do for that entity. Let's say for instance, you wanted to attach the assignments completed for a student in a class. The logical place to attach this entity (Assignment) would be on the Enrollment entity. Giving enrollment it's own primary key would make the Assignment queries simpler.

It all has to do with the two things basically:

1) The speed of lookup (where integers for instance fare much better)

2) The size of indexes (where string indexes would explode)

Now it all depends on your needs and the size of the dataset. If a table or a collection has like 10-20 elements in it, the type of the key is irrelevant. It will be very fast even with a string key.

P.S. May not be related to your question, but Guids are considered bad for database keys too (16 byte Guid vs. 4 byte integer). On large data volumes Guids do slow down lookup.