Somehow, i've ended up with a design such that the tables are
interlinked in
a circular fashion ,e.g. A link to B, B to C, C to D and D back to A. Is
this
advisable? Are there pitfalls in such a design? Thanks.
ck
It's all about the semantics: there is nothing intrinsically wrong with
having more than one path between two tables as long as they are
describing different relationships: for example
Students --< TakePartIn >-- Classes >-(AreTakenBy)- Teachers
Students >-(LiveIn)-- Houses >-(HouseMaster)- Teachers
When you draw that all on paper there will be a "circle" of relationships:
it's possible to describe a set of all Classes which are taken by teachers
who are the housemasters of students who take part in "Home Economics" if
you needed to. Whether that means anything useful, of course, is a
completely different question!
Quite often though, it's a sign of improper analysis. For example, consider
Students --< TakePartIn --< FeePayments
where the PK for TakePartIn is (StudentNumber, ClassID). There is a
temptation to force a relationship between Students.StudentID and
PeePayments.StudentID, but that would be wrong, because it's already
implicit in the relationship between Students.StudentID and
TakePartIn.StudentID.
Each single relationship has to have a real-life meaning -- it's never
justifiable to create a constraint just because there happen to be
matching fields in the tables.
Hope that makes sense
Tim F
