Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    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 Thumbnails Attached Thumbnails db_diagram.gif  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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/

  3. #3
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,576
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  7. #7
    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

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  9. #9
    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:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,576
    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

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  12. #12
    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.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,576
    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

  14. #14
    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 Attached Files

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •