Should a composite primary key out of 3-4 attributes or a unique artificial ID should be used for the situation below?
I agree with your analysis. Using (MatriculationNumber, ExamID, Date) as the key of TestResult will, indeed, allow each person to take the test once per day at most.

What to do about it depends on the problem domain - what is and is not allowed in real life. If a person is not permitted to resit an exam immediately your initial model is fine. You've indicated you would like to accommodate this, so further data must be captured to support it.

The easiest change would be to stop using DATE and start using DATETIME1. This will allow separate results down to the resolution of the clock. Inside the computer that's about microseconds. In real life, say, one minute. Can a student complete an exam twice inside sixty seconds? No - good; dust hands; job done.

Let's say we must retain the DATE column, because &lt; reasons >. Then some other attribute must be added. I'll propose a new column called attempt_number. And because I must know this value to uniquely identify a row it becomes part of the table key. It now has a four-column composite key on (MatriculationNumber, ExamID, Date, attempt_number).

What values should my proposed column allow? One option is it resets each day. On Monday I have attempts 1, 2 and 3. On Tuesday I have attempts 1 through 7 (busy day). On Wednesday I start again at 1 and pass on attempt 4. Another option is that it resets per exam - I start with attempt 1 on Monday and I pass at attempt 14 on Wednesday. Yet another is it resets per student: this exam took attempts 43-45 and 47-57 because I have already had 42 goes at other exams before I started this one and I attempted a different exam on Monday afternoon.

There is no right or wrong option here. Each is valid. Each makes some queries easier and some harder (e.g. "how many times in total did Joe sit exam Alpha" versus "how many times did Joe sit exam Alpha on Tuesday"). The choice would be governed by the real-world practices you are modeling.

There is one further option for values of attempt_number - that they are global across all students, exams and dates. The first time anyone sits any exam it is given attempt number 1, the next is 2 and so on. attempt_number is, in fact, unique through the whole table. This is a good point to introduce some nomenclature.

A key is any column or set of columns whose values uniquely distinguish one row from all other rows in that table. Because multiple sets of columns can be unique each set is called a candidate key. In formal relational theory a relation must have at least one candidate key and can have more. Relational DBMSs (the physical implementation of relational theory) however typically allow tables (the implementation of relations) to exist without any key defined. Keys that consist of columns that occur naturally in day-to-day user conversation are termed natural keys. Every student will know her matriculation number. Every examiner will know the exam's ID. If you say "this is my 4th attempt today," that will be undestood. These could be used in natural keys. Sometimes we introduce new columns for performance or design considerations. If these new columns summarise and stand in for other candidate keys they are called surrogate keys. They are system-generated an internal. They are not well-know to people in the domain. No-one would understand if you exclaimed "I passed on ResultID 8720982!"

Thinking further about an attempt_number that is global across the whole TestResult table i.e. every row has a different value. Since knowing one value uniquely identifies a row it is now a candidate key. Since it stands in for the natural key (MatriculationNumber, ExamID, Date, &lt; something else >) it is also a surrogate key. This is what I think you mean with your ResultID column. If we implement ResultID in this way, my proposed attempt_number is redundant and can be forgotten.

Surrogates have the advantage that they are shorter than the natural keys they represent. This saves space at the "child" end of a foreign key, for example. Sometimes it is just not possible to uniquely identify an entity by natural values. A person is the canonical example. They do have great flexibility as we as programers define them and can define their range and semantics to suit whatever purpose we choose. The drawback of surrogates is that UI input, report output and data transfers invariably need the "natural" values, so the foreign keys have to be traversed in these circumstances.

For the example given, I would implement ResultID as a surrogate ID. It's values will be global across the table. This will give flexibility to extend the schema easily in the future. I would also change DATE to DATETIME so a believable natural key also exists.


1 Don't ever name a column the same as a datatype. This column will be exam_start_time or somesuch.