Database Relationship Loops

[DevastatioN]

Hello,

I was taught that having a loop of relationships is a bad thing to have. My dilemma comes from the following:

I have a table let's say that has user data, which contains a field for Province, and City they live in. Now, clearly City and Province have to also be related. This would effectively create a relationship loop as follows:

tblEmployee: ID, Name, Province (Linked to tblProvince), City (Linked to tblCity)

tblProvince and tblCity are also linked.

One way to stop this, is simply to not include the Province in the main table... snice once you enter the city, it's obvious which province the employee belongs to. However on the main form, a user will most likely wish to "Filter the city, based on province selected".

This is entirely hypothetical, but I ran into something similar in real practice. Which is better? To create the relationship loop in the database, or to find a workaround?

Attached is a tiny example database of what I did to make this work without creating a relationship loop, but I was wondering how bad a relationship loop really is, in this type of situation where it sounds very logical.

Another example I have run into, let's say we have tblInstructors, tblStudents, and tblClasses. Clearly classes have students and an instructor, and everything is joined properly (Junction table between students and classes, one to many between classes and instructors etc.). But now let's add the fact that Students and Instructors are all military members and have a "rank" associated with them. Each instructor has a rank, and each Student has a rank. This is easily stored in a tblRanks, however linking this table to both tblStudents and tblInstructors creates a relationship loop.

The problem here is, tblRanks isn't only the ranks, but contains info on the ranks as well. How can you generate a query that shows each student and their rank, the classes they have taken, the instructor that taught that course, and that instructors rank.

So what do you guys think? (Attached is my City/Province workaround).

 

[The_Doc_Man]

The city-province loop is not a loop, it is a hierarchy. The layout

#1: Person (many/one) >> City (many/one) >> Province
#2: City (many/one) >> Province

There is no loop because (if designed correctly), the arrows between City and Province point the same way. This is a transitory relationship. In theory you don't need to express the City >> Province relationship on #1 because it is already expressed in #2. You could design a query to embody #2, then build another query for #1 and the query for #2, thus finding person, city, and state in a single record of a two-stage query, without any ambiguity.

For contrast, let me show you a loop and tell you how to manage it.

Suppose you have a fraternal organization for which there is a rule: Every member must either have a sponsor who is a member of the organization or must be one of the organization's founders (who have no sponsors).

tblMembers
MembID, PK, autonumber
MembName, MembAddr, MembPhone, MembJoined (Date), etc.
IsFounder (Y/N)
SponsorID, FK to MembID, Long Integer

On your data entry form, you would of course require that in order to store a record, either IsFounder must be Y or SponsorID must not be zero.

Obviously, if SponsorID is anything other than zero, you must link it back to an entry in the same table where it appears. To do this, you would go to the relationships window and add this table twice. The second time you do, it will be shown as tblMembers(2). This is not a second table, but a second reference to the table. Then you can draw a one-many type of relation between the SponsorID (one) and MembID. (Reflecting that one member can be the sponsor for more than one member.)

If the relationship is designed first, queries you build will easily allow you to perform the back-link translation from SponsorID to member name, or whatever.

Now let's do a more complex loop. Let's do a geneology table.

tblPersons
PersID, PK, autonumber
Person demographics, address, phone, stuff.

tblRelationTypes
RelTypeCode, PK, autonumber
RelName, text description of relation
RevName, text description of relation from the other direction

tblRelations
PersAID, FK to tblPersons
PersBID, FK to tblPersons
RelTypeCode, FK to tblRelationTypes
RelDateStart, date relationship started
RelDateEnd, date relationship ended or 0 if still applicable.
Other flags and qualifying information.

In this case, the loop uses a junction table, tblRelations, in which one relation points to tblMembers and the other points to tblMembers(2). That is, tblRelations is a junction table between two records in tblPersons, where there can be any number of such relations for a given person "A" and any number of such relations for a given person "B" as needed. Obviously, the total number of relations for all persons A will almost balance the total number of relations for all persons B. But they don't have to balance exactly, since you have to start somewhere. The persons at the "top" of the heirarchy will only have relationships as person A and the persons at the "bottom" of the hierarchy will only have relationships as person B.

More complex loops are possible - and manageable - as long as no query tries to take more than one step along the relationship at a given time. Where you descend into madness is if you have a function that traverses relationship steps until it reaches a terminal situation (i.e. top or bottom of a sequence).

 

[Pat Hartman]

DevistatioN, in your ranks example, the problem is that instructors and students are both people and belong in the same table. In many cases an instructor may at some point also be a student which is more justification for using a single table. You can handle roles by adding an Instructor flag to the instructor records. Everyone can be used as a student but only Instructors can be assigned the instructor role for a class.

 

[Atomic Shrimp]

Might be better, in the long run, to create a 'role' field, in case roles other than 'student' or 'instructor' arise in future (I'm thinking there might be things like 'assistant', 'consultant', 'temporary instructor', 'guest', etc)

 

[DevastatioN]

For that example that is the best way, to have a field it seems. I guess you would manipulate the forms to make it so that the instructor is attached to the course itself, and the students are attached elsewhere. I imagine both Instructor and Student have to go through the junction table, otherwise you have a different type of loop I assume.

tblCourse to tblPeople (Intructor) and
tblCourse to tblJunction to tblPeople (Students)

Which would not be too good either. Note the instructor needs to be selected when the course is added, before any students can be added.

I guess the Add Course form has adds people to the junction table as instructors only, and the add students form adds people as students only. And queries can separate them for later use?

 

[Atomic Shrimp]

That sounds like it might work - put a field called 'role' or something in tblJunction, so when you create the course, you:
--add a record to tblCourse to describe the course
--add a record to tblJunction to link the record in tblCourse and tblPeople, with 'instructor' in the role field
--add multiple records in tblJunction linking the record in tblCourse and tblPeople with 'student' in the role field

-that way, you can, if you need to, add more than one instructor for a course - and instructors can also be students on a course someone else is instructing.
(actually, instructors could end up being listed as students on their own courses - so you might need to circumvent that kind of nonsense with a filter or something).

 

[Pat Hartman]

The junction is three fields -
Course - Person - Role

 

[Ldbenney]

The_Doc_Man,

I am interested in getting further detailed regarding your genealogy example shown above. I'm not quite understanding where the tblRelationTypes fits in and how it is linked to the tblPersons/tblRelatiins info, not exactly what those relationships look like to track ancestors/descendants. .... Also, how would you suggest fitting into this data the parents marriage/divorce information without having to enter it twice (for mother and father)? ...... I'm pretty new to this database programming stuff and know just enough to make myself dangerous and get myself into trouble! Therefore, as much detail as you could provide would be greatly appreciated.

Thank you in advance for your time.

 

[ButtonMoon]

I was taught that having a loop of relationships is a bad thing to have.

That's not so - or at least it's not a very reliable design guideline. It's important to understand that circular relationships and database constraints that support such relationships are not "wrong" if they accurately represent the business rules you are trying to model. Dependencies such as A <=> B (A implies B; B implies A) are quite usual in reality. One very common and straightforward example is a purchase order: every ordered item must be associated with an order; every order must include at least one item.

Problems arise only because SQL and DBMSs based on SQL have certain limitations that make such business rules tricky to implement. Specifically, dependencies between two or more tables can't usually be enforced if they require both tables to be updated simultaneously because the SQL model doesn't allow for simultaneous updates (a feature called "multiple assignment").

There are a number of possible solutions, none of which is perfect:
- Don't enforce the rule or some part of the rule
- Use a rules engine or external business logic tier to enforce the rule
- Enforce the rule in procedural code (e.g. triggers)
- Use deferred constraint checking (if your DBMS supports it)
- Decompose the tables so that some of the dependencies are not preserved

I suggest you study a database design book that covers this topic, such as this one: http://www.amazon.com/Information-Modeling-Relational-Databases-Management/dp/0123735688

 

[jdraw]

The_Doc_Man,

I am interested in getting further detailed regarding your genealogy example shown above. I'm not quite understanding where the tblRelationTypes fits in and how it is linked to the tblPersons/tblRelatiins info, not exactly what those relationships look like to track ancestors/descendants. .... Also, how would you suggest fitting into this data the parents marriage/divorce information without having to enter it twice (for mother and father)? ...... I'm pretty new to this database programming stuff and know just enough to make myself dangerous and get myself into trouble! Therefore, as much detail as you could provide would be greatly appreciated.

Thank you in advance for your time.

There is more info(theory) on trees and hierarchy here than you may care to read.. but.... http://i.stanford.edu/~ullman/focs/ch05.pdf