Need help on database designing

Question

I am currently out of idea how to design this table so I would really like some suggestions. Description as follows:

Table will hold 3 exam result.

1. Exam A: 8 mandatory subject with infinite optional subject.
2. Exam B: 6 mandatory subject with infinite optional subject.
3. Exam C: 1 mandatory subject with 4 optional subject.

Feature to keep in mind:

1. Each subject's result need to be searchable (eg: Find A for Math in Exam A)
2. Basic total calculation (eg: calculate how many As in Math for Exam A)

Just inserting data I would be able to think of something however when putting the features into the mix, it just won't work.

My last resort is having a single table with: studentid, exam, subjectcode, result. This will work as in searchable and calculable however I have a feeling of a very messy and huge database in the long run.

My current design (given by my friend): Each subject and its result have its own field. It works but very hard to expand (add more subjects).

Any recommendations?

Answer 1

Possible table structure (leaving out column definitions):

Exam
---------
Exam_ID
Exam_name 
number_of_req_subjects
number_of_opt_subjects  <---- -1 could be infinite

Subject
-----------
Subject_id
subject_name

exam_subject
------------
exam_subject_id
exam_id
subject_id
required

exam_result
------------
exam_result_id
exam_subject_id
result  

To get the number of A's for Math in Exam A:

SELECT count(exam_result_id)
FROM exam_result er, exam_subject es, subject s, exam e
WHERE er.exam_subject_id = es.exam_subject_id
AND es.subject_id = s.subject_id
AND es.exam_id = e.exam_id
AND e.exam_name = 'A'
AND s.subject_name = 'MATH'

(I know using joins would be better than where to join the different tables, but I'm being a bit lazy).

The subjects result being searchable...we don't have enough information. In fact, my answer may be completely off, but is as close as I think I can get it with the given information at the moment. Making something searchable is just a matter of creating a sufficiently useful select statment.