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...)
- 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.