Database normalization for grade management system

Question

I am working on a grade management system web app for a university and I came up with this database schema enter image description here

How the app work:

1. In the user table I am going to keep all the users data and the id_user_data will tell what kind of user is(student, teacher or administrator)
2. The administrator can add new users, new specializations (like computer science, mathematics) and new subjects (like algebra, databases)
3. In the grades table I am going to keep all the users grades with a FK to subject which is going to keep track of subject name and the teachers id which

I am a beginner in databases and I would be glad if you can give some advice if the database looks alright or not.

Answer 1

I think you should review your model for things that aren't necessarily a direct 1-1 relationship, some examples:

• Can a student also be a teacher? At some schools graduate students may also teach and give grades on introductory courses even while they are taking courses themselves.
• What is a subject vs an instance of a class section in that subject? Classes with many sections can have more than one teacher. Students get their grades from the their teacher but on their transcripts it shows a grade for the course(?)
• What if I take the subject a second time? If I failed the subject the first time and take it again, should the first grade totally disappear?
• What is the data in the grade table? You already a have a grade value, should this data be in another table of related data e.g. quiz grades, lab grades?
• Students can have multiple majors/minors and teachers can have multiple degrees, so a single specialization may be limiting