join - Ideal solution for the following case scenario in database -

there 50 exams written around millions of students online, 1 person may or may not write more 1 exam. person can write single exam more 1 time ( retries ) ..

so of below solution better case, i okay better solution these 2 well

option 1. store each exam in single table :

subject 1

+----------------+---------+ |   student id   |  marks  | +----------------+---------+ | 1              | 85      | | 2              | 32      | | 2              | 60      | +----------------+---------+ 

subject 2

+----------------+---------+ |   student id   |  marks  | +----------------+---------+ | 1              | 85      | | 2              | 32      | | 2              | 60      | +----------------+---------+ 

like above each table have student id if particular person has taken exam , , have multiple occurrences of student id if has taken more once.

option 2 :

+----------------+---------+---------+ |   student id   | subject |  marks  | +----------------+---------+---------+ | 1              | subj1   | 85      | | 2              | subj1   | 32      | | 2              | subj1   | 60      | | 1              | subj2   | 80      | | 3              | subj2   | 90      | +----------------+---------+---------+ 

with values in single table.

which better in terms of performance , storage perspective.

my various que

i think best here following:

1. table student information students

2. table exam information exams

3. table exam_try reference student , exam tables, , fields date_of_exam , result_of_exam

4. 2 indexes on foreign keys in table exam_try

5. depending on situation - index on date field (for example, need planning work examiners)