Is it appropriate to have "circular" relationships?

[bigalpha]

See attached photo (sorry for bad quality).

This is how I have my database set up currently, except the part highlighted in red.

I was asked to see if I can use the CAT (one) to narrow down the CLIN (to many) and this is how I think it should be structured. Is it okay to have this kind of circular relationship within the database?

 

[jdraw]

No.
If you haven't worked through this tutorial, I highly recommend you do. It's well worth the time.

 

[CJ_London]

Personally I don't see why not though it may be indicative that the tables could be redesigned.

The db won't go bang if that is what you are worried about and it is perfectly valid if that is what is required to limit the number of records returned in a query

What I wouldn't do is set up your relationships with enforced referential integrity on all the joins

 

[jdraw]

Here's an article that may help describe more about circular relationship(s).

 

[bigalpha]

Thanks for the links, jdraw, I'll definitely check them out in depth when I get a free minute.

CJ - the extra table I'm looking at adding will be used solely for limiting data in a set of cascading combo boxes so the data in the new table will be pre-populated.

Looking at this again this morning, is the "proper" solution to merge both of the link tables to have only one - then update the LinkFK value in my main table?

 

[CJ_London]

You can do if it works for you.

Do read the articles provided by jdraw, they are very informative.

In particular be aware of which way the one to many relationships work

 

[gemma-the-husky]

my initial reaction would be that the data analysis is not optimal.

A table may be linked to itself by a self join, (so, eg , in an employees table, one employee can be a supervisor of others), but this still implies a hierarchical relationship, and not a circular one.

I can't actually imagine a problem that requires a circular relationship.

I don't understand the diagram you actually uploaded, so I can't really comment on that.

 

[RainLover]

I can't actually imagine a problem that requires a circular relationship.

I would have to agree with this.

What I wouldn't do is set up your relationships with enforced referential integrity on all the joins

Whereas I totally disagree with this one.

Just have a look at Microsoft Samples/Templates. They don't have these problems.

You just need a little more experience / practice with designing tables. This is the area where more people make mistakes than anywhere else.

Cheers.

 

[ButtonMoon]

I can't actually imagine a problem that requires a circular relationship.

Here's one:

Unfortunately CJ_London is right. Usually there is some aspect of such a relationship that cannot feasibly be enforced between tables using database constraints (whether by referential integrity constraints or otherwise). This is because most DBMSs evaluate constraints on a per-table basis and don't permit update/insert/delete operations that affect more than one table at once. It's because of this limitation that you normally have to make at least one part of a circular dependency optional/unenforced.

Circular dependencies are perfectly reasonable and even quite common in reality but most database designers tend to either remove them or make them optional at an early stage of design.

 

[gemma-the-husky]

Here's one:

Unfortunately CJ_London is right. Usually there is some aspect of such a relationship that cannot feasibly be enforced between tables using database constraints (whether by referential integrity constraints or otherwise). This is because most DBMSs evaluate constraints on a per-table basis and don't permit update/insert/delete operations that affect more than one table at once. It's because of this limitation that you normally have to make at least one part of a circular dependency optional/unenforced.

Circular dependencies are perfectly reasonable and even quite common in reality but most database designers tend to either remove them or make them optional at an early stage of design.

why is that example circular?

they are just 2 different one to many relations, surely. (maybe even many to many)

branch/managers (maybe requires a junction table)

and branch/workers (again, maybe requires a junction table)

 

[RainLover]

Super Dave is either correct or very close to being totally correct.

If you can't set up your relationships with enforced referential integrity on all the joins then your table design is incorrect.

Unfortunately many people here overcome their poor design work by substituting their coding skills in its place.

One should keep in mind that excessive coding can be a sign of poor design work at the lower level.

 

[ButtonMoon]

why is that example circular?

they are just 2 different one to many relations, surely. (maybe even many to many)

branch/managers (maybe requires a junction table)

and branch/workers (again, maybe requires a junction table)

Your suggestion wouldn't enforce the business rules defined by the ER diagram: that every Branch has exactly one Employee managing it and that every Employee is assigned to at least one Branch, i.e. a "circular" dependency. Of course you are right that you would probably have to implement it by removing the circular relationship - that is my point.

If you can't set up your relationships with enforced referential integrity on all the joins then your table design is incorrect.

I think what you mean is that there is a difference between reality and what can be accomplished in some DBMSs. The model might be a correct description of reality but if you need to get something working within certain software limitations then you'll probably have to compromise. Unfortunate but sometimes true. The compromises you have to make in such cases potentially lead to more complex code later on, not less. Where you can't enforce circular dependencies with constraints in the database you'll probably want to implement them in application or middle-tier code or an external rules engine instead.

 

[RainLover]

Quote:
Originally Posted by RainLover
If you can't set up your relationships with enforced referential integrity on all the joins then your table design is incorrect.

I think what you mean is that there is a difference between reality and what can be accomplished in some DBMSs. The model might be a correct description of reality but if you need to get something working within certain software limitations then you'll probably have to compromise. Unfortunate but sometimes true. The compromises you have to make in such cases potentially leads to more complex code later on, not less. Where you can't enforce circular dependencies with constraints in the database you'll probably want to implement them in application or middle-tier code or an external rules engine instead.

What is it that makes you think that you know more about what I am thinking than what I do.

I haven't seen your work but I sure would like to. I would have a guess that you are making the typical mistakes as made by others who have not as yet mastered the art of table design.

 

[gemma-the-husky]

button

I take the example to be that an employee could manage multiple branches, or indeed there could be multiple managers per branch etc.

however restricting the relationship to one employee is not a problem.

Anyway, I still do not see that there is any circularity in the example you propose.

manage: 1 branch = n employees
(or even)
1 employee = n branches

works at: 1 branch = n employees

there are 2 1 to many relationships. That's all. To even need to consider a circular relationship you would actually need to stipulate a third premise.
Maybe that a n employee can only manage the branch he works at. In which case you do not need the branch-manager relationship. Manager is just an attribute of the employee.

 

[ButtonMoon]

button
I take the example to be that an employee could manage multiple branches, or indeed there could be multiple managers per branch etc.

however restricting the relationship to one employee is not a problem.

Anyway, I still do not see that there is any circularity in the example you propose.

Possibly you missed the point of the ER diagram I posted. It is a "circular" relationship in the sense that every branch must have a manager and every employee must be assigned to a branch. So the dependency between branch and employee works "both ways at once". This is shown in my diagram because the cardinalities are 1..1, 1..(N>0)., ie. not optional.

Now there are multiple ways you could represent that reality in an ER diagram or in a database design. In a Chen-style ER diagram (undirected relationships) the joining tables that you propose would form a circle. Draw it and see. But of course the real circularity has nothing to do with a picture in a diagram. Your proposed design does not implement the circular dependency I am describing because it would allow branches without managers and/or would allow employees not assigned to any branch. So you haven't implemented the business rules as I described.

I suppose there's a problem here in that the word "circular" in the data modelling context is a bit imprecise and overloaded. Maybe we should just use instead the more precise, technical term: join dependency. Join dependencies between tables are very difficult to enforce in most SQL and SQL-style database systems because SQL does not support multiple assignment - the ability to update more than one table at once.

 

[RainLover]

It is interesting how this thread has moved away from

I was asked to see if I can use the CAT (one) to narrow down the CLIN (to many) and this is how I think it should be structured. Is it okay to have this kind of circular relationship within the database?

To using some rather technical words, like

I suppose there's a problem here in that the word "circular" in the data modelling context is a bit imprecise and overloaded. Maybe we should just use instead the more precise, technical term: join dependency. Join dependencies between tables are very difficult to enforce in most SQL and SQL-style database systems because SQL does not support multiple assignment - the ability to update more than one table at once.

This kind of talk only tends to highlight one's ability to describe a problem/solution correctly.

 

[ButtonMoon]

This kind of talk only tends to highlight one's ability to describe a problem/solution correctly.

Exactly but since there is more than one way to interpret what was meant by the term "circular" in the original question, to make things clearer it ought to help if we answer by using less ambiguous words. The reason why the data management profession uses terms like "join dependency" is because it helps us to communicate precisely with each other - precision tends to be important where data is concerned.

 

[gemma-the-husky]

button

I am not familiar with the predicates implied by the ER connections you set out. I was considering just in terms of my understanding of a database

now I see what you are getting at. but isn't this just a (sort of) paradoxical design - you cannot create a branch because you need to have employees for it, and you cannot create employees because you don't have braches to assign them to.

 

[ButtonMoon]

button
now I see what you are getting at. but isn't this just a (sort of) paradoxical design - you cannot create a branch because you need to have employees for it, and you cannot create employees because you don't have braches to assign them to.

There's no reason to call it paradoxical if such a constraint reflects reality. Perhaps my example isn't all that likely as a real business rule but the apparent "problem" isn't with the rule itself but is just a consequence of a technical limitation. Database software based on the SQL model (including Jet and Access) make it hard to accomplish this type of constraint because insert/update/delete statements can generally only affect one table at a time and therefore constraints get evaluated one table at a time.

Other possible examples of circular rules are not too difficult to find: every order must have at least one product in it and every instance of an ordered product must be assigned to exactly one order; every customer must have at least one account and every account must belong to at least one customer. These are join dependencies because the join of the relevant attributes from each table is a non-loss join in which all the original values are preserved.

 

[jdraw]

I tend to agree with Rain in that the discussion has moved away from the initial question. I interpreted that question to be more along the line - Is it ok to have circular relationships in your database design. My first reaction was No -- you don't start out to have them. You do some more analysis to see if you really have the appropriate representation of facts. If after analysis, you still have such a relationship, then accept it and move on.

Having said that, I do agree with buttonmoon that theory works as he has suggested/stated. But I also recognize there are several DBMS packages that are commercially viable, that have devout followers who wouldn't change if their lives depended on it and have never concerned themselves with theory at this level whether or not they have ever heard or studied it. Oracle, Paradox, MySql, Ingres, SQL Server, Access Jet/ACC ... all have their followers; they are all based on specific assumptions/compromises of the theoretical - indicating there are "many ways to skin a cat"..

I just hope the original poster got a satisfactory response to the post.