63

Someone told me that it was bad design to have loops in the datamodel. I have heard this before a couple of times but didn't pay much attention. For example you have entities User, Project, Activity. A project is owned by a User, so we have a one-to-many relationship from user to Project. An activity can be assigned to a single User, another one-to-many relationship from User to Activity. Of course a project is defined by a set of activities, another one-to-many relationship from Project to Activity. Thus a loop is formed.

I asked this guy why is it bad design but he told me he didn't know either, he was told so too, monkey learning at it's best.

I tried searching but I guess I didn't use the proper words, however this seems to me something that should be fundamental for someone trying to design a DB.

So, can anyone point me to some useful info about loops/cycles in er/db diagrams, should they be avoided?

pgpb.padilla
  • 2,318
  • 2
  • 20
  • 44

1 Answers1

68

There's a really good treatment of relationship loops in chapter 3 of this paper (archive.org).

Generally however, the most common issue with loops is consistency of redundant information.

Consider the case (from the paper) where a parent has many children; each child attends a school. There is a third relationship between parent & school ('parent has child at school'). However: you don't want to model the 3rd relationships explicitly; it's completely derivable from the other two. If you did capture it explicitly, you'd need to ensure the loop was always consistent.

So in that case you'd want to avoid the loop. However: loops are not universally bad. Taking the above example again, consider modelling the case where a parent is a governor at a school. That would also create a loop. In this case though it's valid: it's not possible to derive the 'parent is governor at school' relationship from the other two relationships.

So in summary: don't model loops when one relationship is completely derivable from the others combined. But it's OK to create loops when they're not derivable.

Would recommend the paper though; it gives a much better description than I can give here.

TylerH
  • 20,799
  • 66
  • 75
  • 101
sfinnie
  • 9,854
  • 1
  • 38
  • 44
  • Its a good question and your answer makes a lot of sense. So if I was to extrapolate to the OP's example, you could say you generally don't need to link the user to a project, as that relationship could be derived based on the fact they are assigned to tasks that are in turn assigned to a project, therefore you can derive this relationship. However if your project entity had a project manager, this would be a valid relationship to map to users as you could not derive that relationship. – Stephen Nov 15 '11 at 20:33
  • @Stephen: yes, that's it. The paper has some other scenarios too - but in essence it boils down to either managing consistency of redundant information, or removing it through derivation. – sfinnie Nov 15 '11 at 23:00
  • 1
    The paper seems like much ado about nothing. No, "loops" (of this kind) aren't "better avoided". What is better avoided is redundancy (in general, not only as applying to relationships). Information that is derivable from other information that is already present, that is to be avoided unless you have very good reasons not to. Why do they need x-ty pages to say this ? – Erwin Smout Nov 16 '11 at 14:33
  • 3
    @Erwin: if you already know what you should be wary of in loops - as you clearly do - then the paper will tell you nothing new. However the OP specifically said (s)he had been told 'loops are bad' and didn't know why. The paper provides concrete examples that explain the situation from first principles. It's therefore appropriate if you're starting from there. If you're not then it's not. – sfinnie Nov 16 '11 at 19:03
  • The link is dead sadly. – Twenty Jul 22 '20 at 09:45
  • Paper on dead link on archive.org: https://web.archive.org/web/20120307133229/http://ooatool.com/docs/OOA96.pdf and on other site: https://xtuml.org/wp-content/uploads/2015/10/The-OOA-96-Report.pdf – viktorkho Sep 19 '20 at 08:52