sql closed loop relations; what could go wrong?

Question

Hi,
I am working with a database with the following design. I read it is not a good practice to have closed loops in a database design, and i have more than one. But i cannot remember why. So not sure how this might affect me. Any examples how this could be dangerous?

Edit: went through my ebooks, found what i was reading was Beginning Database Design From Novice to Professional, publisher: APRESS.
they just warn against this, but give a vague reason why. No, we are not using triggers. Anyone has a clearer explanation?
Thanks Excerpt, p.109:

A small company has employees who each work for one of a number of different small project groups. Each group and all its employees are housed in one particular room with larger rooms housing several groups.We may require information such as where each employee is located, a particular employee’s phone number, where to find a particular group, which employees work in each group, who is in each room, and so on. One possible data model is shown in Figure 5-7. Take a moment to understand the data model and the information it contains about the number of groups in a room and so on for this particular problem. The model has redundant information. Can you see what it is?

With respect to Example 5-3, if we regularly want to find an employee’s phone number, we might think that the top relationship in Figure 5-7 between Employee and Room would be a useful direct route. However, this same information is very easily available by an alternative route through Group. We can find the employee’s (one only) group and then find that group’s (one only) room. This is a very simple retrieval (it does not involve all the complications with dates that plagued the small hostel in Example 5-2). However, the extra relationship is not just unnecessary, it is dangerous. With two routes for the same information, we risk getting two different answers unless the data is very carefully maintained. Whenever an employee changes group or a group shifts rooms, there will be two relationship instances to update. Without very careful updating procedures, we could end up having that Jim is in Group A, which is in Room 12, while the other route may have Jim associated directly with Room 15. Redundant information is prone to inconsistencies and should always be removed.

Answer 1

I've not had problems in the past using "closed loop" references between groups of tables (i.e. at least 3 in the relationship loop). The only issue I can think of would be if you use triggers, and even then it would only be a problem if you are updating the other tables in the "loop" in the trigger.

Do you have a reference for where you read this advice for not having closed loops?

To the others who commented the image can be seen if you copy the link to a new window: http://imgur.com/ChFL1

Answer 2

Not all databases have deferred relationship checking enabled and not all databases allow inserting two records into two different tables in one statement.

Imagine you have a FOREIGN KEY from A to B and from B to A.

Initially, both tables are empty.

How are you going to insert the very first record?

You cannot insert anything to A since it has to refer to a record in B (which is empty), and in the same way you cannot insert anything into B.

Answer 3

I wouldn't say that "it's not good practice to have closed loops". However, they do draw attention to a potential problem. So it would be a good habit to check all closed loops for the possibility of this problem.

The text book example seems quite clear to me. There are 2 ways in which the location/phone of the employee can be determined:

• Employee -> Group -> Room -> Phone
• or Employee -> Room -> Phone

This is like storing two variables for the same thing that need to be kept in synch. There will be the possibility that something goes wrong and the variables end up with different values - then you have to ask: "Which of the two is correct?"

So the text book example highlights the problem you have to watch our for. However, the problem really boils down to semantics. I.e. what do the relationships mean. In the text book example, both paths to Room mean exactly the same thing. If however the Group -> Room were merely a 'default' for each employee because senior employees could get their own room, or employees could temporarily be assigned to work somewhere else away from their group, then the additional relationship would be justified.

Moving onto your design, this is what you should do:

• Look for closed groups
• Assess the semantics/purpose/meaning of the relationships
• Check that you haven't just created two paths to exactly the same information.

For example, you have:

• TestSample -> Coil -> Slab
• TestSample -> Plate -> Slab

(Forgive the possibly absurd understanding of your terminology.) Does this mean your test sample can be on 2 different slabs at the same time? Or does it mean your test sample will be made up of a plate and a coil that could have (but not necessarily) come from different slabs?