4

For a small beginner project to start with databases and system development, I'm trying to develop a system that represents the classic case of an administration system for students and exams. This is based on the following diagram:
enter image description here The two simple entities student and exam don't cause me any problems, but I'm struggling to find the best way to solve the many-to-many relationship between the two. I realize that I have to create an additional table for this, but I'm not sure if I should choose a composite key of 3-4 attributes or an artificial key with a unique ID. I have worked out both approaches a little, here are the results:

Composite Key:
enter image description here

With this solution, the idea is to identify a test result via the MatriculationNumber, ExamID and Date. In my opinion, the date must also be added to the key, since it is possible that exams that have not been passed must be taken again and managed in the system. Therefore, MatriculationNumber and ExamID would not be sufficient to uniquely determine the two completed exams. However, I also came up with an extreme case: What happens if an exam and the catch-up date (for whatever reason) take place on the same day and both have the same grade, say 5.0? Then it is not possible to distinguish these two things. The whole thing would become even more difficult if even a third attempt had to be taken into account.


Artificial ID:
enter image description here
In this case, a new, artificial ID is to be created in order to uniquely determine each result. However, there are no implicit advantages to the first solution, which, for example, allows only one entry for each exam and student and tag. However, I would like to be as flexible as possible with my solution, and not let the design of the database dictate my professionalism.


In general I would like to know from you experts which of the two solutions fits better for my application. It should be possible to obsolete several attempts of the same exam of a single student, even if they could fall on the same day. I don't want to let the database dictate or limit the subject. It should also be possible to build a REST-API based on what I find rather difficult in the case of the composite key, at least after what I have read on the net. In your opinion, what is the best practice for this problem?


EDIT
To describe the overall situation even better, a few additional details: For the implementation I would like to use a postgreSQL database. Because it is only a simple introduction example, where no billions of records are stored or time-critical operations are needed, the focus is not on performance at all. However, I find it more important to be able to expand the application and business logic later, for example by storing documents or other information. I don't want a feature to destroy the complete structure of my database or API. My main concern here is that it can happen to me that I have to make such rudimentary changes if I want additional information in the solution with the merged key. On the other hand, I still lack a clear statement or justification why I should introduce an artificial ID and what are the advantages of it

  • 2
    Firstly John, welcome to DBA.SE. We rarely get first posts of this quality - kudos for that - this is very well structured. Secondly, what are you most concerned about? Performance? Future development? What are you judging to be important about this decision? – George.Palacios Dec 3 '18 at 12:10
  • Oh and secondly, is this for a particular RDBMS? Or just a general structuring question - try and remember that the more general you are, the less likely you are to get a helpful response. – George.Palacios Dec 3 '18 at 12:14
  • You asked about the situation when a student (re-)takes the exam on the exact same date. If the first exam has some form of identification (ExamID) in the form of PHY_EX1_VAR1 (Physics, Exam 1, Variant 1), then each subsequent test (re-take) would be a new variant (i.e. PHY_EX1_VAR2,PHY_EX1_VAR3, ...). The ExamID would possibly have to contain some form of characteristic, to allow you to differentiate between the normal exam and the re-takes. (That being a kind of "Business Logic" in your design.) You could add a Retake column to the TestResult table and add that to the index. – hot2use Dec 3 '18 at 12:17
  • @hot2use just to play devils advocate here, I'd argue the natural key in that situation just changes to MatriculationNumber, ExamID and a DATETIME (rather than a DATE) A student can't possibly sit two exams at the same time, right? – George.Palacios Dec 3 '18 at 12:19
  • @George.Palacios Yes, but if you are going to evolve this database design further, then you'll be possibly storing the exams (PDF; Scan; Binary) in the database and these exams are going to be possibly used/taken multiple times. The dates they are taken aren't very relevant when storing the exam, but the variants of the exam would be. It could be an additional column and doesn't have to be part of the ExamID. – hot2use Dec 3 '18 at 12:26
2

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

0

I recommend using the artificial key ResultID, for these reasons:

  • It will be easier to pinpoint a single TestResult row (or a number of rows) for doing SELECT, UPDATE or DELETE. For example, with a single-column PK you can use WHERE ResultID IN (id1, id2, ...) or ResultID IN (... subquery ...). Using IN can't be done on all values of a multi-column PK at once.
  • It will be easier to define new relations to TestResult, whether self-referencing or not. Suppose you need to administer "Retake results" also in the TestResult table that point back to the previous result. With a single-column PK, just add a column PreviousResultId. With the 3-colum PK, you'd be set up to create a 3-column relational mess (IMO).
  • 2
    To play devils advocate here, the PK doesn't need to necessarily be the method of searching the table. IE PK on the 3 columns, with an additional ID column (Hybrid solution). – George.Palacios Dec 3 '18 at 14:19
  • True, if the DBMS supports it (I know SQL Server supports a FK to a Unique column, for other DBMSes I wouldn't know). Still I would shiver a little when I would see it... – Peter B Dec 3 '18 at 14:30
  • 2
    I would argue using a natural key is more desirable in terms of pure DB design than a surrogate - but that's an argument that's been around as long as the concept of a database. – George.Palacios Dec 3 '18 at 15:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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