I am currently developing a database for a school management system... There are tables for
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
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
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.