Database Design – Is Matriculation Number a Good Primary Key?

database-designprimary-key

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.

Best Answer

I currently work in this field. This is a case where a surrogate key should be used.

Our software probably deals with a much wider range of scenarios than yours, but that extra flexibility may mean a lot for you in the future.

Student numbers (overall) may change quite frequently depending on how the numbers are assigned by the distribution authority.

When a new student is enrolled, a number or unique identifier may have to be internally assigned to the student before the authority has actually generated a number for the student (asynchronously). In this case, it may be necessary to use more than one field in the database... which crushes the primary key idea immediately.

Student numbers that change are just bad news for lots of reasons. Remember that the primary key in this table will be propagated to many parts of the system, and with many associated rows.
Performance is rarely a concern for single-student data operations. Depending on how the indexes are set up, using the student number as an alternate key may have a slight performance hit. However, at least in my experience, it's pretty rare that this is a significant user experience issue. You're more likely to run into problems with performance of batch processes and reporting that looks at groups of students (by school, by grade, by class, report cards, etc.).
The length and format of a student number varies by jurisdiction. Some may be numeric, some may be alphanumeric. Do you need to handle more than one format? If a numeric student number is used as the primary key now, you set yourself up for a world of hurt later if you need to change to alphanumeric (and not only from a database perspective!).

Also, if that happens (or if you use alphanumeric to start), performance will be diminished, as string comparison is always going to be less efficient than integer comparison.

That you can guarantee the student number is unique is good. Make sure to enforce it using appropriate uniqueness and validation constructs.

Use the student number (alone, or in combination with other business keys) whenever it's necessary to integrate between your application and another.

Your software may fit a narrow box of a situation where it looks "okay" to use the student number as a primary key. If that's the case, I cannot offer many negatives about that approach. However, not using a surrogate now sets you up for pain down the road if you need to venture even an inch out of that box.

Related Solutions

Sql-server – Best practice to avoid a composite key

To start with the correct primary key (single column vs multiple or artificial vs natural) is to use the primary key that is correct for the task.

Some people will tell you to always use an Artificial or Surrogate Key. This is a key that is a generated key and has nothing to do with the information it represents. For example an identity column or a GUID. Others will tell you that an artificial key is the wrong way to go and you want to use a Natural Key. This is a key that is constructed from one or more columns of the data itself. FYI a key with multiple columns is sometimes called a composite key. LastName, FirstName for example. Or in your case it looks like you are using a combination of the two. You have an integer ID (presumably artificial) + a year (presumably natural).

I'm not really going to go into the arguments here. You can Google them yourself. In your particular case I would leave your primary keys alone. In my opinion t makes sense to have SurveyId and FieldId columns that are your primary key. These are unique values (I assume) and the tables are basically lookup tables in this particular case. I would however move the Year column out of those tables and into the data table. If you don’t want to do this then take advantage of the fact that foreign keys can join to a ‘unique key’ as well as a primary key. A unique key is an index that enforces uniqueness but is necessarily the primary key. As an added bonus columns in a unique key can be nullable. In this case your structure would look like this:

CREATE TABLE survey (
   surveyID int primary key,
   student_name nvarchar(20),
   year smallint
)
GO

CREATE UNIQUE INDEX ix_survey ON survey(surveyID, year)
GO

CREATE TABLE fields (
   fieldID int primary key,
   field_name nvarchar(20),
   year smallint
)
GO

CREATE UNIQUE INDEX ix_fields ON fields(fieldID, year)
GO

CREATE TABLE data(
   surveyID int,
   fieldID int,
   year smallint,
   value float,
   primary key (surveyID, year, fieldID), 
   foreign key(surveyID, year) REFERENCES survey(surveyID, year), 
   foreign key(fieldID, year) REFERENCES fields(fieldID, year)
)
GO

I did take the liberty of changing the year column to a smallint which is more than large enough to hold a 4 digit year and only takes 2 bytes instead of 4.

Using the unique key you can enforce the fact that you want the year columns to be the same while not adding an unnecessary column (the IDs are already unique) to your primary key.

Mysql – How to model (a many to many) to many relationship

The tables student and major are good.

However following improvements can/should be made.

Merge the FKs in MajorStudent to a PK, getting rid of the id
Save a StudentFK and MajorFK rather than a MajorStudentFK in Exam, well one can discuss about this though but it'll be faster and in my consideration NOT redundant
Grade is not normalized. All attributes are supposed to be atomic and grade is not. Either make 4 columns (bad) or use the following

Exam(id[PK], studentId[FK], MajorId[FK]) Grades(id[PK], examId[FK], value)

I don't understand why one exam comes with four grades though.

Best Answer

Related Solutions

Sql-server – Best practice to avoid a composite key

Mysql – How to model (a many to many) to many relationship

Related Question