Hi All,

I have been asked to design a University database (for a small uni project) and this is what I have so far.




I've designed the database so that a student can only enroll on a signle programme. Each programme has multiple modules (eg A business degree would have an Accounting, Human resources module etc).

The problem I have now is that I want to be able to record each students grade for each module in a new table. I was wondering what the best way of doing this was.

Many thanks

Folly

with a 1n<->n1 join from table_student to tbl_grade via a linking table

+----------+ 1     N +---------------+  N        1 +----------+
| students |---------| studentGrades |-------------|  grades  |
+----------+         +---------------+             +----------+

I would need to include the module in there somewhere?

I would have it as just a single table, like

Student
--studentID
--name
--...etc

Grades
--moduleID
--studentID
--grade

The only reason i w ould do it as Tripy suggests is to normalize the 'grade' field, which if it is a letter (e.g. A, B, C etc) and you wanted to convert all to numbers (e.g. A=1, B=2, C=3 etc) then you would only change one row for each. But if it is stoerd as a percentage for example it would save more space and time to store as i have suggested.

The only reason i would do it as Tripy suggests is to normalize the 'grade' field

It was my idea of not directly using the grades table, yeah.