HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Is matriculation number a good primary key?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
numberprimarymatriculationgoodkey

Problem

I am trying to design a grade assignment database and i am facing the following dilemma.

I have a student table which has the following characteristics:

  • matriculation number which i know for a fact is unique.



  • first name



  • last name



  • course of studies(Bsc Mechanical engineering, Msc mechanical


engineering, Bsc computer science etc...)

  • email



  • remark Maybe some special notes about a particular student.



However there is also the following condition. Every student can at the same time belong to two different course of studies, that is a student can be Bsc and Msc mechanical engineering at the same same time. So after some thought i decided to create a separate table called course of studies, which has two columns an auto increment id(PK) and a course of study which will include all possible course of studies. Then connect the student and course of studies tables using a many to many relationship.

At the same time i thought that another implementation would be to use a surrogate key (auto increment id) on the student table and instead of creating a separate course of studies table i could simply incorporate it in the student table as a column and not have any problem with my primary key. Since if i had a student that belongs on two different course of studies i would have two different rows with a unique id

So my question to you is what do you think is the best implementation given the situation(Please explain why). I know that natural primary keys vs surrogate primary keys is a highly debated subject which doesn't seem to have a universal answer however given the situation what do you think is the best implementation?

Any additional details can be provided .

EDIT
There are two good answers that are saying more or less the same thing. I chose to accept the one with the more votes.

Solution

I would go with the surrogate key approach. I'm not familiar with matriculation number, but i expect it to be large enough so that having a surrogate integer might be the preferred alternative. Having a larger PK, means larger indexes, which means less performance for reads and writes, plus more space usage, and more space for referring tables too.
Go with the surrogate key.

Context

StackExchange Database Administrators Q#59447, answer score: 4

Revisions (0)

No revisions yet.