Need help on table designing
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.
Exam A: 8 mandatory subject with infinite optional subject.
Exam B: 6 mandatory subject with infinite optional subject.
Exam C: 1 mandatory subject with 4 optional subject.
Feature to keep in mind:
- Each subject's result need to be searchable (eg: Find A for Math in Exam A)
- 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 it's own field. It works but very hard to expand (add more subjects).