Complex DB tables problem

Question

hi everyone .. I am developing a student results website in which :-

1. Every year has its own subjects ( 1st year has sub1 , sub2 , sub3 and 2nd year other subjects and so on ) which will be added , deleted by the admin of the site .

I designed three tables :-

1. "Students" : contains info about students and their current year .
2. "Subjects" : info about subjects .
3. "Years" : info about years in the school .
4. "Years_Subjects" : relation between the years and its subjects .

The problem now is How can i store a result ?? how can i design the results table to contain degrees for different subjects ???

Thanks

Answer 1

Results will be a table that has the following values (results) and the following foreign keys Student, Subject, (possibly year)

Have a degrees table and an association table between subject and degree

Results  -> Student
         -> Subject
         -> Year?

Degree  <- DegreeSubject -> Subject

Answer 2

I think you want a Results table like this.

Results
--------
Student_ID (FK to Students)
Years_Subjects_ID (FK to Years_Subjects)
Score

I don't think it would be best to have a FK to Years and another FK to Subjects, because you won't be able to easily prevent a row that represents a subject that was not offered in a particular year.