An exception for a circular reference database - is this one?

Question

I have 4 tables, linked in a circular reference - I remember from college that I was told this is bad however there are exceptions...I am hoping that this is one of them :)

My database contains 4 tables; teachers, classes, subjects and teachers_classes.

The following sums up my relationships:

• A teacher can have many classes
• A class can have many teachers (so teachers_classes eliminates the many-to-many here).
• A class can have many subjects
• A subject can only have 1 class
• A teacher can have many subjects

If you can visualise this, my ERD looks like a square (or circle)... I have already built my simple app, and was prompted by someone else to check this problem.. please someone tell me ths is an exception and why? I cannot remember any of the stuff I was taught about this, but my application appears to work totally fine for what I want it to do!

Answer 1

Circular references can be bad for a couple of reasons:

1. In the case where the relationships must exist (i.e., a teacher must have a class and a class must have a teacher, both from a business and technical requirement perspective), you run into a chicken-or-the-egg scenario: you can't add a teacher without a class, nor can you add a class without a teacher.
2. It makes it difficult to figure out what's at the "top" of the heirarchy (since, truthfully, there isn't a "top")

Assuming that you can have teachers without classes and/or subjects without classes, it sounds like one of those two would be the "top" (from a business perspective, I'd assume that it would be subjects).

If what you have is working, I don't see an issue with the design, nor do I see an alternative way of designing it.

---

Edit after comment

There isn't an issue with one-sided dependency (that's just what a plain ol' non-nullable foreign key is).

I feel like I should point out something as well, based upon your comment: the objections to circular dependencies are technical, not logical. If the business says that there can be no teachers without classes and no classes without teachers, that's fine; you just can't model the data that way or you'll never be able to add anything. You have to define which one of those objects--classes or teachers--is allowed to exist in isolation (from a technical perspective, not a business one).

You're somewhat saved by the fact that you have a M:M relationship between teachers and classes, because this forces you to make both able to exist in isolation (since the connection is made in a link table, rather than in the participant tables themselves.

Because of this, you do not have a true circular dependency. Your business logic is circular, but that's fine, since you have complete control over how it operates. Your layout either looks like this:

   Teacher <----- TeacherClass -----> Class
      ^                                 ^
      |                                 |
      |                                 |
TeacherSubject --------------------> Subject

(If a teacher can have multiple subjects)

Or this:

   Teacher <----- TeacherClass -----> Class
      |                                 ^
      |                                 |
      |                                 |
      \----------------------------> Subject

(If a teacher can have only one subject)

Or this:

   Teacher <----- TeacherClass -----> Class
      ^                                 ^
      |                                 |
      |                                 |
      \----------------------------- Subject

(If a subject can have only one teacher)

In the first case, both Teacher and Class are top-level entities, since neither of them points to anything else (the link tables accomplish this). In the second, only Class is a top-level entity.

As long as there's a top-level entity somewhere on the path, you're fine. In the first, you can add records in this order:

Teacher -> Class -> (TeacherClass -> Subject) -> TeacherSubject

(I enclosed TeacherClass -> Subject in parens because you could add those in any order)

In the second, you can add them in this order:

Class -> Subject -> Teacher -> TeacherClass

In the third, you can add them in this order:

Class -> Teacher -> (TeacherClass -> Subject)

So, from a technical perspective, you don't have a true circular dependency.