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:
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:
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:
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
ExamID
) in the form ofPHY_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
, ...). TheExamID
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 aRetake
column to theTestResult
table and add that to the index.