Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite
1

I have heard that having loops in the database relational model should be avoided. From what I can think of, it is not a good practice, because there will be more than one different pathes to get the same results and might lead to inconsistent records in the query result.

Is that true? Also I'm wondering if it affects the query plans and SQL Server performance.

What do you think? What are the other troubles it might cause?

share|improve this question
2  
Please give an example of this. – Martin Smith Sep 30 '12 at 13:52
To give you an example, think about this scenario: Table 'Questionnaire is linked to 'Questions', table Questions is linked to 'Answers'. Table 'Answers' is linked to 'QuestionaireResultsets',but 'QuestionaireResultsets' is also linked to 'Questionnaires', which forms a loop. – Nazila Beikpour Oct 1 '12 at 2:49
Therefore to access 'QuestionaireResultsets', you have 2 options: Questionnaires-> Questions-> Answers-> QuestionaireResultsets OR direct join between Questionnaires and QuestionaireResultsets tables. – Nazila Beikpour Oct 1 '12 at 3:01

2 Answers

up vote 4 down vote

I have heard that having loops in the database relational model should be avoided.

To me, a table structure (>1 table) where the foreign keys form circular dependencies (traversing foreign keys starting from one table, it's possible to end up back at the same table) is a red flag indicating a likely problem with the table/system design.

I'm not going to say this is never correct, but I have yet to see an example where this kind of thing was used correctly, or could not be reasonably redesigned to avoid the use of a circular dependency.

(It's the same type of issue in other areas too, as I pointed out here.)

Table 'Answers' is linked to 'QuestionaireResultsets',but 'QuestionaireResultsets' is also linked to 'Questionnaires', which forms a loop.

Those tables are related, yes, but the relationships probably should be of the form:

Questionnaires 1..* QuestionaireResultsets

QuestionaireResultsets *..* Answers, with a join table in between

These changes result in a structure that does not contain a circular dependency.

Remember that foreign key relationships have a direction. This is very important. Tables can be related such that a schema diagram looks like a circle; it's only a problem when all the segments of the circle point in the same direction.

I'm wondering if it affects the query plans and SQL Server performance.

If you ask SQL Server to perform a task using recursion instead of using sets or iteration, it's possible that performance could suffer; however, the reverse may be true as well. I'm not sure we can have a meaningful discussion about performance in this case without a specific example.

share|improve this answer
The only (good?) reason I could see to repeat the reference to Questionaires in the QuestionaireResults is for performance reasons. With that redundant references retrieving the results for a given questionaire could (potentially) be a lot faster (no join needed) - but I would see that as a last ressort to improve performance. – a_horse_with_no_name Oct 1 '12 at 6:38
@Jon: Thanks for your answer, but I don't know what do you mean by 'specific example'? Above is the example of the issue I'm taking about. – Nazila Beikpour Oct 1 '12 at 10:51
@a_horse_with_no_name: Agreed for performance reasons, but denormalizing that reference into the join table still wouldn't create a circular dependency. The join table would still be on the many side of all the relationships, which breaks the direction of the chain. – Jon Seigel Oct 1 '12 at 14:51
@NazilaBeikpour: A specific example that legitimately contains a circular dependency. As I demonstrated in my answer, the example you gave should not have a circular dependency. – Jon Seigel Oct 1 '12 at 14:52
1  
One possible case where one could have a legitimate circular dependency would be if modeling some form of heirarcy where the each level alternates between two types. Examples are scarce, but one would be an "Alternating decision tree" (a machine learning data structure). The dependency could be removed by using a self-referencing tables with a single or multi-table "inheritance" design, but that would require additional constraints to enforce the alternating aspect, while the circular reference style inheirently enforces it. – Kevin Cathcart Jan 18 at 15:37
show 2 more comments
up vote 1 down vote

In SQL Server one of your biggest problems as I understand it is a lack of support for deferring foreign key constraints. This means that loops pose data integrity problems that are not as easily solved as they are on some other systems.

Now, loops also pose some important gotchas when you are doing recursive queries. For example, if we have graphs we are traversing using recursive cte's, we have to know there may be loops and weed them out or else we get problems.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.