Why should I avoid loops when designing relationships for a database?

Question

Someone told me that a bad design has loops in the datamodel. I had heard this a couple of times before, but didn't pay much attention. For example, you have User, Project, Activity objects. The project belongs to the user, so we have a one-to-many relationship from the user to the project. An activity can be assigned to one user, another one-to-many relationship from user to activity. Of course, a project is determined by a set of actions, other one-to-many relationships from project to activity. Thus, a cycle is formed.

I asked this guy why this is a bad design, but he told me that he also does not know, he was also told that the monkey is better at learning from this.

I tried to search, but I think I did not use the correct words, however this seems to me to be something that should be fundamental for someone trying to create a database.

So, can someone give me some useful information on loops / loops in er / db diagrams, should I avoid them?

Answer 1

There really is a good relationship to relationship cycles in chapter 3 of this article .

In general, the most common problem with loops is the consistency of redundant information.

Consider the case (from the article), where the parent has many children; every child attends school. There is a third relationship between the parent and the school (“the parent has a child at school”). However: you do not want to explicitly model the third relationship; it is completely derived from the other two. If you explicitly committed it, you need to make sure that the loop is always consistent.

So in this case you need to avoid the loop. However: loops are not universally bad. Taking the above example, we consider modeling the case when the parent is the governor of the school. It would also create a loop. In this case, although this is true: it is not possible to get the parent-governor-at-school relationship from two other relationships.

So, in short: don't model loops when one relation is completely deduced from the other combined. But it is normal to create loops when they are not output.

I would recommend the document, although it gives a much better description than I can give here.

NTN.