I'm rather new to databases and am having a hard time trying to figure out how to structure my database. This database contains students, classes and all their grades both overall and in categories (such as tests, homework, etc.)
So far I have created the following tables:
Student: id|firstname | lastname (pk = id)
Class: id|name|room num (pk = id)
Categories: id|name|weight (pk = (id, name, weight))
Grades: student_id (fk)| class_id (fk)| category_id (fk) | name | grade_val
My question is if I wanted to save the students overall grade with this schema how would I do it without having a null foreign key and preserve normalization?
Currently to store their grade I would have to have an entry like Grade: 1, 1, null, 'Overall Grade', 95
in the database.
What is the correct way to do this? Add another table, an extra row in category like: Category: 1, 'Overall', 100
or something else?
Edit:
Currently the primary key on the grades table is just the combination of the other columns. pk = (student_id, class_id, category_id, name)
Overall Grade
be overridden by the teacher? If the value can always be calculated, you could hide the calculation within aVIEW