I have one table Exam
, and I want to store exam Date
. Each Exam
belongs to one of the Categories
described below:
Category 1. For Exam
A, there can be a Date
once in a year, e.g., 01-01-2017.
Category 2. For Exam
B, there can be more than one Date
during the same year, e.g., 01-01-2017 and 03-01-2017.
Category 3. For Exam
C, there can be a Date
range, e.g., from 01-01-2017 to 10-01-2017
Category 4. For Exam
D, the Date
can be chosen by a Student
, e.g., the whole year, i.e., 365 days
My approach
I am saving the Exam.Date(s)
in another table. I believe the relationship between Exam
and Date
is one-to-many. Said table would display data as follows:
Question
The problem with this approach is that, for Category
4, each Exam
will have 365 rows, so, is this approach correct?
Best Answer
I think I would approach this as the exam-dates table wouldn't have an entry for category 4, since it is an open-ended exam.
Perhaps, if you need to know when student A took an exam under category 4, then add a column for the date taken along with the student and student's grade?
So you could have
Exam
table that lists out the exams and other data like course id, etc.ExamDates
table that lists when exams are scheduled (and possibly other values like building, room number, etc.)Students
table with all of your student data.StudentExams
table that links exams to students, (and through exams back to courses). This would have a student ID, an Exam ID, a date the test was completed, and a grade.and probably some other tables like
Courses
table with data about the courseStudentCourses
table linking student to course