The exception for the round reference database is this?
I have 4 tables that are linked by a circular reference - I remember from college that I was told it was bad, but there are exceptions ... I hope this is one of them :)
My database contains 4 tables; teachers, classes, subjects and teachers.
Below are my relationships:
- A teacher can have many classes
- A class can have many teachers (so classes_classes eliminates many-to-many here).
- A class can have many objects
- An object can only have 1 class
- A teacher can have many subjects
If you can visualize this, my ERD looks like a square (or a circle) ... I already created my simple application and I was prompted to check this issue. please tell me what is the exception and why? I don't remember any of the things I was taught, but my app seems to work great for what I want it to do!
a source to share
Circular links can be bad for several reasons:
- In the case where the relationship must exist (i.e. the teacher must have a class and the class must have a teacher, both from a business and technical point of view), you are faced with a chicken-or-egg scenario: you cannot add a teacher without a class, and you also cannot add a class without a teacher.
- This makes it difficult to figure out what is at the "top" of the hierarchy (since, frankly, there is no "top")
Assuming you could have teachers without classes and / or subjects without classes, it looks like one of the two would be "supreme" (from a business perspective, I would assume they would be subjects).
If you have a job, I don't see a design problem, and I don't see an alternative way to design it.
Edit after comment
There is no one way dependency issue (this is just what the normal ol 'non-member foreign key has).
It seems to me that I should point to something as well, based on your comment: the objections to circular dependencies are technical, not logical. If business says that there can be no teachers without classes and there are no classes without teachers, that is fine; you simply cannot model the data that way, otherwise you can never add anything. You must determine which of these objects - classrooms or teachers - is allowed to exist in isolation (from a technical point of view, not a business one).
You are saved a bit by the fact that you have M: M relationships between teachers and classes, because that forces you to make them exist in isolation (since the join is made on the link table, not the member tables themselves.
Because of this, you don't have true circular dependency. Your business logic is circular, but that's fine since you have complete control over how it works. Your layout looks like this:
Teacher <----- TeacherClass -----> Class
^ ^
| |
| |
TeacherSubject --------------------> Subject
(If the teacher can have multiple subjects)
Or that:
Teacher <----- TeacherClass -----> Class
| ^
| |
| |
\----------------------------> Subject
(If the teacher can only have one subject)
Or that:
Teacher <----- TeacherClass -----> Class
^ ^
| |
| |
\----------------------------- Subject
(If a subject can only have one teacher)
In the first case, both Teacher
and Class
are subject to the upper level, because none of them does not indicate anything else (table links operate it). In the second, it Class
is only a top-level object.
As long as there is a top-level object somewhere along the way, you're fine. In the first case, you can add records in the following order:
Teacher -> Class -> (TeacherClass -> Subject) -> TeacherSubject
(I wrapped TeacherClass -> Subject
in parens because you can add them in any order)
In the second case, you can add them in the following order:
Class -> Subject -> Teacher -> TeacherClass
In the third case, you can add them in the following order:
Class -> Teacher -> (TeacherClass -> Subject)
So, technically speaking, you don't have a true circular dependency.
a source to share