Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
The real question is: can a the matriculation number change for a single student? If there is only a slight chance that this happens, an artifical PK is probably the better choice. Regarding the other question: moving the course of studies to a separate table is definitely the better approach. It is a classical 1:N relationship and which PK you choose for the student should not influence the decision on properly storing the course of studies in a separate table. –  a_horse_with_no_name Feb 23 at 13:20
    
The other thing I always consider: is the field your considering as PK controlled by your system or is it an "external" key? External keys are bad choices for PK as your system is not "in control" or "making the rules". Better to include an external key as an attribute. –  Colin 't Hart Feb 23 at 14:18
    
@a_horse_with_no_name I don't believe it is going to change. But if you are asking me whether i am 100% that it will never change then the is answer is no i am not sure. On the other hand i cannot think of any natural key that the designer can use as primary and be 100% sure that it will never change. Regarding the relationship i am a bit confused. I did say that a student can have more than one course of studies at the same time however a course of study may belong to more than one students. Isn't that a many to many relationship? –  Sotiris Feb 24 at 16:01
    
@Colin 't Hart you are correct the PK is not controlled from my system rather than the university. So i am guessing this is an argument in favor of a surrogate key. –  Sotiris Feb 24 at 16:03

3 Answers 3

up vote 4 down vote accepted

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.

share|improve this answer
    
At the moment the matriculation number consists of an eight digit positive integer. However like i said in another comment i wouldn't bet my life it will stay like that forever, although i have no reason to believe otherwise. To answer another of your questions no i do not have to take into consideration alphanumeric matriculation numbers. However since flexibility is and should be an important guideline in database design i can see why you chose the surrogate key approach. Thank you for your answer –  Sotiris Feb 24 at 16:19
    
@Sotiris: You're welcome. Let me know if you have any other questions about this. –  Jon Seigel Feb 24 at 17:14
    
There is infact one more thing that is not clear to me. I believe the relationship between Student and course of studies is many to many. However another user said this is a typical example of a 1:N relationsip. I personally disagree because in a 1:N relationship the N part corresponds to exactly one record in the first table, e.g a Person can have multiple phone numbers but a phone number belongs to exactly one person. In my example however a student can have many course of studies but a course of study may be assigned to more than one students. What is your opinion? –  Sotiris Feb 25 at 16:17
    
@Sotiris: I didn't go into this in my answer on purpose because it's a side question to the main point... There are other design issues that need to be solved, too. The object relationship of students-to-courses should be many-to-many. But what was proposed above creates a one-to-many with a normalization problem. The courses of study should be in a new table by themselves, and a second new table will hold the associations between the courses and students. –  Jon Seigel Feb 25 at 18:32
    
true it is a side question but the implementation will depend on how i will define the student table. So if i understood you correctly you will put Course of studies in a new table and connect course of studies and student with a junction table which will have the primary keys of the aforementioned tables as foreign keys(this is how a many to many relationship is implemented right?). Thank you for your time –  Sotiris Feb 25 at 19:36

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.

share|improve this answer
2  
You will need a unique index on the matriculation number anyway so the index size is not an argument against using it as a PK. And this also depends on the DBMS and index type. For clustered indexes in SQL Server or MySQL having large PK values is indeed a problem. For non-clustered indexes (Postgres, Oracle) not so much –  a_horse_with_no_name Feb 23 at 13:04
    
Yeah you're correct. But in case that the table is clustered, index size matters. Unique is implemented with non-clustered indexes, at least this is the case in SQL Server. –  MiNT Feb 23 at 13:13
1  
Yes, but you still wind up with two indexes (the clustered PK index and the non-clustered unique index) even though a single one would be enough. And you can define the PK as non-clustered as well (which probably isn't such a bad idea anyway: use-the-index-luke.com/blog/2014-01/…) –  a_horse_with_no_name Feb 23 at 13:17
    
You will end up having a dozen of indexes anyway, having +1 in this case will not affect performance that much, it will slow down a bit data modifications, but in other cases it might help reads. But overall, you will gain more than you'll loose. Don't forget any relationships this table might have. –  MiNT Feb 23 at 13:24
    
@MiNT From what it seems surrogate key is the way to go in that particular case. I will however update my initial question with all the available information about the database. I am not sure whether this will change something but you never know. Thank you for your answer and for sharing your experience –  Sotiris Feb 24 at 16:23

What identifies a student in the reality you are supposed to be modelling? It seems that Matriculation Number identifies a student, i.e. Matricularion Number is your "natural key" for students.

One thing for sure is that a surrogate key does not identify a student. From a data integrity and usability point of view what is important is that the uniqueness of Matriculation Number is assured (by making it UNIQUE and NOT NULL) and that the required dependencies on Matriculation Number are properly implemented to avoid potential anomalies and incorrect data. For example, I assume that {Matriculation Number} -> {firstname, lastname, email} and therefore to satisfy BCNF the Matriculation Number should be a key of a table containing those attributes.

A surrogate key won't help you achieve these things. Adding arbitrary numbers to things doesn't make them any "more unique" than they were before.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.