I have got a rather complex relationship between several entities:
TeacherTable
|
TeacherClassLinkTable
|
ClassTable
|
StudentClassLinkTable
|
StudentTable
|
StudentTestResults
|
TestTable
|
TestModuleTable
This works for most things that I need to do with it but it fails when I try to find what modules are taken by a class. I am able to find out what modules have been taken by Students that are part of a class but Students can belong to multiple classes taking different modules in rare cases. So I would not necessarily get an accurate result to finding what modules are taken by a class. I therefore want to insert a new table which would be ClassModuleLinkTable. This would allow me to make that link easily, however it would form a loop in my database structure and I'm not sure whether my database would therefore remain in 3rd normal form.
TeacherTable
|
TeacherClassLinkTable
|
ClassTable----------------------------
| |
StudentClassLinkTable |
| |
StudentTable |
| |
StudentTestResults |
| |
TestTable |
| |
TestModuleTable--------------ClassModuleLinkTable
I don't think that this is a problem, and I don't actually think it's what I would call a loop or circular reference.
A circular reference is where e.g. table A has a non-nullable FK to table B, which has a non-nullable FK to table A (or the circle could be A to B to C to D to A). If both tables are empty you cannot actually add a row to either of them, as both require a reference to a row in the other. I'm not actually sure that this situation is against 3NF, but it's plainly a problem!
Your situation does not have a circular reference and so as far as I'm concerned it's fine.