Circular reference

Question

Circular reference

the_rutter

SSC Enthusiast

Points: 186
More actions
September 6, 2010 at 7:05 pm

#223467

Is it valid design for TableA to reference TableB (foreign key) and TableB to reference back to TableA (foreign key)? Or is this circular reference *always* bad?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

TomThomson SSC Guru Points: 104773 More actions · Answer 1

the_rutter (9/6/2010)
Is it valid design for TableA to reference TableB (foreign key) and TableB to reference back to TableA (foreign key)? Or is this circular reference *always* bad?

In relational theory, it's fine and valid. And it can be useful. So not always bad.

In actual DBMSs it's often not allowed, or allowed but badly implemented with problems you have to programme around.

The relational calculus engine has to check foreign key constraints at transaction commit time if you want a system that allows this in its full generality - and many systems can't handle it properly because a foreign key constraint is checked on the individual statement, not on the whole transaction. Some systems therefore ban it alltogether, while others allow it but you have to programme round issues caused by doing the checks too early.

It's easy in principle to do it "properly" and mostly not terribly inefficient: one algorithm that works is check on each individual statement, if the check fails add the check (with enough information about the rows concerned to allow efficient processing) to a list to be re-evaluated on commit; on commit if any check on the list fails roll back and throw an error, otherwise allow the commit to proceed. In practise this isn't as easy as it sounds.

Tom

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 2

I agree with Tom, but I'll go a little further, I wouldn't call it "bad", but I would do whatever I could to avoid that kind of circular reference. It has the potential for causing a great deal of problems, and you'll have to document the heck out of it for the next person who has to develop against or maintain the database, because if they're not very aware of what the issue, it'll bite them hard.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

TomThomson SSC Guru Points: 104773 More actions · Answer 3

Grant Fritchey (9/7/2010)
I agree with Tom, but I'll go a little further, I wouldn't call it "bad", but I would do whatever I could to avoid that kind of circular reference. It has the potential for causing a great deal of problems, and you'll have to document the heck out of it for the next person who has to develop against or maintain the database, because if they're not very aware of what the issue, it'll bite them hard.

We agree totally then - I should probably have made it clearer that although it's not always bad I think that (especially with most of the real DBMS software out there) it's usually bad.

Tom

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 4

Tom.Thomson (9/7/2010)
Grant Fritchey (9/7/2010)
I agree with Tom, but I'll go a little further, I wouldn't call it "bad", but I would do whatever I could to avoid that kind of circular reference. It has the potential for causing a great deal of problems, and you'll have to document the heck out of it for the next person who has to develop against or maintain the database, because if they're not very aware of what the issue, it'll bite them hard.
We agree totally then - I should probably have made it clearer that although it's not always bad I think that (especially with most of the real DBMS software out there) it's usually bad.

Yeah, absolutely. I didn't think there was an iota of disagreement.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 5

the_rutter (9/6/2010)
Is it valid design for TableA to reference TableB (foreign key) and TableB to reference back to TableA (foreign key)? Or is this circular reference *always* bad?

Ok... your turn. Why would you need to do this in your case?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

the_rutter SSC Enthusiast Points: 186 More actions · Answer 6

I'm actually a .NET developer with little SQL experience. I noticed this circular reference in a new project I am working on and it raised alarms in my head. I have very vague memories of this sort of thing being bad from my school days...

I'd classify it as bad in this case as the people who created and maintained this system have left.

Thanks for all the help.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

the_rutter (9/10/2010)
I'm actually a .NET developer with little SQL experience. I noticed this circular reference in a new project I am working on and it raised alarms in my head. I have very vague memories of this sort of thing being bad from my school days...
I'd classify it as bad in this case as the people who created and maintained this system have left.
Thanks for all the help.

It would be interesting to see, if you have the time.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

roulupen SSC Journeyman Points: 89 More actions · Answer 8

Hi All,

I am also facing a Circular reference problem. But, not able to get any idea to fix it.

Scenario: I have 3 tables:

tblUser(ntUserID, vcUserName, vcPassword) - Keeps User Information

tblGroup( ntGroupID, ntGroupOwnerID, vcGroupName ) - Keeps Group Information

tblGroupUser( ntGroupUserID, ntGroupID, ntUserID ) - Keeps user who has been assigned into a group

Relationship:

tblUser - > tblGroup(ntGroupOwner) - > tblGroupUser(ntGroupID) <- tblUser(ntUserID)

So, here I am facing circular reference.

Any suggestion to avoid this

aaron.reese SSChampion Points: 13498 More actions · Answer 9

Thats not a circular reference.

tblGroupUser is a cross-ref or bridge table to normalise the Many:Many relationship betwen users and groups. It allows one user to belong to many groups and for one group to have many users