2

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:

enter image description here

Question

The problem with this approach is that, for Category 4, each Exam will have 365 rows, so, is this approach correct?

1
  • 2
    for case 4 - You do not need store all 365 days, just store already chosen dates. Sudnet1 choose 01 Feb, Student 3 - 01 May, so table store only 2 records. And application allow Students choose any date from Calendar, but not from table
    – a_vlad
    Feb 9, 2017 at 18:02

3 Answers 3

1

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

  • an Exam table that lists out the exams and other data like course id, etc.
  • an ExamDates table that lists when exams are scheduled (and possibly other values like building, room number, etc.)
  • a Students table with all of your student data.
  • a 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

  • a Courses table with data about the course
  • a StudentCourses table linking student to course
  • and so on...
0
0

Got another approach- Using two column EXAM_START_DATE and EXAM_END_DATE to save date in place of only 1 column EXMA_DATE.

  • For Category A - there will be only one entry.

EXAM_START_DATE : 01-01-2017 EXAM_END_DATE : 01-01-2017

  • For Category B - there will be two entries.

Row 1) EXAM_START_DATE : 01-01-2017 EXAM_END_DATE : 01-01-2017

Row 2) EXAM_START_DATE : 03-01-2017 EXAM_END_DATE : 03-01-2017

  • For Category C - there will be only one entry.

EXAM_START_DATE : 01-01-2017 EXAM_END_DATE : 10-01-2017

  • For Category D - there will be only one entry.

EXAM_START_DATE : 01-01-2017 EXAM_END_DATE : 31-01-2017

enter image description here

0

From first rows we clearly can see that dates and exams have many-to-many relationship:

  • exam B has 01-01-17 and 03-01-17

  • on 01-01-17 there are two exams A and B

So standard way is to have separate tables for exams and dates and then have junction table to handle relationship.

For example:

Exam Id  ExamName  ...other data
1        A
2        B
3        C
...
DateId  Date      ...other data
1       01-01-17
2       02-01-17
...
ExamId  DateId
1       1
1       2
2       1
...

If dates don't have other data, then Date can easily be added in column in Exam (first table) table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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