If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > General > Database Concepts & Design > Circular relationship - dangerous??

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jul 2002
Location: Praha, Czech Republic
Posts: 2
Post Circular relationship - dangerous??

I know that circular relationships can be potentially hazardous, but at the same time I'm not quite sure how to handle this information otherwise. So far my test queries come out fine.

This site (dbforums) is massive and I really couldn't find the exact info I'm looking for. I'd love it if someone just took a look at my DB diagram (generated by SQL manager) and let me know the potential dangers - if any.

Actually, I'd appreciate anybody's criticism. This is my passion, and I'm always interested in learning something new from people who've been doing longer than me.

Thanks in advance.
JOS
Attached Images
File Type: gif db_diagram.gif (22.4 KB, 787 views)
Reply With Quote
  #2 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
nice diagram

everything appears to make sense

as for the circular relationship, are you worried about tbl_locale.locale_self_ref? this is the correct structure for a hierarchy, like the who-reports-to-whom manager-employee relationship

unfortunately, a "circular" relationship is possible in such a structure -- mary reports to bill who reports to fred who reports to mary

if you are really concerned about it, you have to put logic into your application to see that it doesn't happen

sorry, i do not have any examples of that logic

rudy
http://rudy.ca/
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Apr 2008
Posts: 6
Circular references and 'pigs ears'

Rudy has hilghighted your pigs ear, that is, an entity that contains a pseudo 'foreign key'.

The classic dept and emp tables illustrate this well with the example of employee who manages 0 to Many employees, but themselves may be managed by another employee.

These entities are a perfectly legal solution to the right problem

Rudy does slightly confuse the issue here though, as a heirarchy is not the same thing as a circular relationship. That is when Entitiy A relates to Entity B, Entity B relates to Entity C and Entity C relates to Entitiy A.

Also Rudy uses "-- mary reports to bill who reports to fred who reports to mary" as an example. This would just not be sound business sense. The heirarchy has someone at the top, that is , someone for who there is no manager

To query these tables using this pigs ear relationship you need to treat the table as two has seperate entities and then joining them together. you can do this by (in your table)
SELECT * FROM tbl_locale t1, tbl_locale t2 WHERE t1.ID = t2.locale_self_ref

You may get a problem if there is no one at the top, i.e. no ID that does not have a self ref. If this is the case then a pig ears is probably not for you.
Reply With Quote
  #4 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
geepers, paul, do you really think that after almost six years john is still interested in design suggestions?

the app was probably written long ago, and may already have been retired
__________________
rudy.ca | @rudydotca
Buy my SitePoint book: Simply SQL
Reply With Quote
  #5 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,561
Rudy sees a post show up that he has answered before. Rudy clicks on it and finds that it is very old.
Rudy reads the new post and finds that it repeats what Rudy said six years ago, but with more words than Rudy used.
This is what annoyed Rudy.
See Rudy? See Rudy get annoyed. Get annoyed, Rudy.
See Rudy post. Post, Rudy! Post!
Rudy feels much better now. Goodbye Rudy!
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #6 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
i keep telling you, you gotta stop taking those chems on the weekend, blindman, they make you sound like a five-year-old

we like you better as an adult

__________________
rudy.ca | @rudydotca
Buy my SitePoint book: Simply SQL
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Apr 2008
Posts: 6
What are these forums for?

Rudy

I found this discussion whilst searching for information on circular references.

It maybe an old listing but it is still 'live', and as it contains misleading information I thought it best to correct that, on the assumption that others may come across it when searching for similar information.

It was my understanding that it was the purpose of these forums to help the user community by contributing information.

It was not my intention to upset you Rudy, and I apologise if my posting caused you concern.

Paul
Reply With Quote
  #8 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
upset? me, upset? concerned? not in the slightest

although i am a bit curious about what you think might have been "misleading"

i've re-read my post and i don't see anything misleading in it at all

i don't see how i've confused anybody except perhaps yourself

__________________
rudy.ca | @rudydotca
Buy my SitePoint book: Simply SQL
Reply With Quote
  #9 (permalink)  
Old
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,910
Also, just FYI - nothing gets marked as "resolved" here (I know it does in many other forums). As such, everything is "open" (unless locked by a mod) but typically we consider any thread with no recent posts to be "dead".

Not making a fuss or owt - just part of the whole DBForums Orientation Experience for New Members
__________________
Testimonial:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
  #10 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,561
Quote:
Originally Posted by paulmarsden
Rudy does slightly confuse the issue here though, as a heirarchy is not the same thing as a circular relationship. That is when Entitiy A relates to Entity B, Entity B relates to Entity C and Entity C relates to Entitiy A.
Rudy never said that a hierarchy is the same thing as a circular relationship, so Rudy did not confuse anything.

Quote:
Originally Posted by paulmarsden
Also Rudy uses "-- mary reports to bill who reports to fred who reports to mary" as an example. This would just not be sound business sense.
Rudy uses that as an example of a circular relationship, which it is. Rudy does not recommend this as a good business practice. Rudy even recommends that the poster add logic to prevent this.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #11 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
thanks, blindman

yeah, and to think i said all that six years ago, when i was still wet behind the ears...
__________________
rudy.ca | @rudydotca
Buy my SitePoint book: Simply SQL
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Apr 2008
Posts: 6
Circular relationships and circular references

Rudy's example
"-- mary reports to bill who reports to fred who reports to mary" is an example of a circular reference of the data, not a circular relationship.
Reply With Quote
  #13 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,561
No, "mary reports to bill who reports to fred who reports to mary" is definitely a circular relationship. If this does not qualify as a circular relationship, please give an example of one that does.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #14 (permalink)  
Old
Registered User
 
Join Date: Apr 2008
Posts: 6
Circular relationships

Example of circular relationship

students are on a course; the course is made up of units; the students must achieve the units.

I have attached Word doc showing the basic un-normalised LDS.

Circular relationships are not something we would want, as they can lead to data redundancy and update anomolies.

The ideal situation is to normalise. This usually involves normalisation beyond 3NF.

A work round is to use a CHECK contstraint instead of referential integrity, which effectivley breaks the circle (although may still leave redundant data)
Attached Files
File Type: doc example of circular relationship.doc (23.5 KB, 193 views)
Reply With Quote
  #15 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
i'm sorry, but there's nothing "circular" about that example

and it does not need to be "solved" because it's already normalized
__________________
rudy.ca | @rudydotca
Buy my SitePoint book: Simply SQL
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On