Somehow Improve the relationships between tables in the following sample

database-design

I am currently developing a database for a school management system… There are tables for GRADE, SECTION and SHIFT… 

GRADE
====
id PRIMARY KEY

SECTION
====
id PRIMARY KEY

SHIFT
====
id PRIMARY KEY

And yet another table which comprises such data called GROUP through foreign keys which additionally has a primary key…

GROUP
====
id PRIMARY KEY
grade_id FOREIGN KEY REFERENCES GRADE(id)
section_id FOREIGN KEY REFERENCES SECTION(id)
shift_id FOREIGN KEY REFERENCES SHIFT(id)

Now where I have an desing stigma and would like advice from a designer with more experience… A table called SUBJECT which comprises a foreign key referencing GRADE and a primary key (A certain grade always has the same subjects)…

SUBJECT
====
id PRIMARY KEY
grade_id FOREIGN KEY REFERENCES GRADE(id)

Now I have yet another table called CLASS referencing SUBJECT, PROFESSOR and GROUP through a foreign key too in addition to its primary key…

CLASS
====
id PRIMARY KEY
subject_id FOREIGN KEY REFERENCES SUBJECT(id)
professor_id FOREIGN KEY REFERENCES PROFESSOR(id)
group_id FOREIGN KEY REFERENCES GROUP(id)

I wonder if it is ok that if drilling down one level on CLASS table references makes possible to access presumably the same GRADE (Through SUBJECT and GROUP) in two different ways. I don't include the code for the tables because I do think it would make this question somewhat long and of course there are more tables. My database is already implemented and works with the proper application but I would like to see if design could be improved. Thanks in advance. *Note : I know I am using reserved words for table names in my case elaboration, but only for illustrative purposes, also did not include all the tables… Too I have an STUDENT table which as a reference to a GROUP, that is why I included a primary key on such table. I hope now my question is better explained, I am new to this kind of site.

Best Answer

Asssuming that I have understood the problem, the following picture describes (a part of) your database, in which an arrow from A to B corresponds to a foreign key from table Ato table B:

enter image description here

Actually, there is a redundancy in that Grades are referred both by Groups and Subjects and each class must have the same grade if it is accessed through one of the two relations.

I think that in a case like this you have two options:

  1. Keep this schema, and check, maybe with a trigger, that when you insert a class it refers to a course and a subject with the same grade, or

  2. change the schema, removing from Subject the foreign key to Grade, and, if you need to find the grade of a certain subject, joining that table with classes, courses and grades. In this way you simplify the situation, but you could create subjects not tied to specific grades.

I have a light preference for the first solution: in both of them, however, it is important that the objects in tables Grade, Subject, Course and Classes be inserted with great attention to not cause inconsistencies. The best way to do this is to define in the database stored procedures to operate on more than one object and/or perform all the proper checks, and require that the application(s) use only them, without inserting explicitly records.

Related Question