Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44

    Associative table attached to a single table help!

    Hey everyone,

    So I had a question about how to correctly implement a certain design.

    Here's the issue:

    I have a table called OWNER which stores basic information about land owners for a system I'm developing. I want to be able to represent the relationship between owners as well. Ultimately, I would like some sort of associative table called OWNER_RELATIONSHIP that allows me to link two owners together, and then have a column that describes their relationship in the new table. I understand how to resolve many-to-many relationships to an associative table between two separate tables, but how do I do it with just one table?

    The reason I'm hitting a brick wall is that I want to have a composite key in the associative table that is made up of the primary key for the two owners from the OWNER table. But since the primary key is the same for both owners, I can't figure out how to implement it. Any suggestions would be greatly appreciated.

    Regards,

    Paul
    Paul Palubinski

  2. #2
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44
    Attached is what the relationship currently looks like in a logical model diagram...
    Attached Thumbnails Attached Thumbnails visio.bmp  
    Paul Palubinski

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    So a child owner may be related to more than one parent owner via multiple owner relationship types?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44
    I'm not too sure if my diagram is clear, but ultimately I'm just trying to have it where the parent/child relationship is between the OWNER and OWNER_RELATIONSHIP tables, and not between two rows in the OWNER table, if that makes sense... So, I guess the OWNER table is a parent twice for each entry in the OWNER_RELATIONSHIP table...
    Paul Palubinski

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I loosely understand that he needs a relationship between a couple of tables but no matter how many times I read the requirement I haven't the slightest idea what it is he's trying to model or understand why the simple relationship supplied wasn't enough. Would it be possible for a simple English translation and a couple of examples to show why the current design is insufficient.

    Just what is wrong with:
    OWNER: id, name, other_data ...
    OWNER_RELATIONSHIP: id, child_id, relationship_type

    PS You didn't ever work for Goldmans in the 90's and write the spec for their position management system did you?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mike_bike_kite
    OWNER: id, name, other_data ...
    OWNER_RELATIONSHIP: id, child_id, relationship_type
    for one thing, using the word "child"

    yes, a table with a FK is a "child" of the "parent" table with the PK

    but in this context, those terms are confusling

    the diagram posted used the names OwnerId1 and OwnerId2, which i find to be more appropriate, because the nature of the relationship is not "parent"-"child" but owner-owner
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ppalubinski
    I'm not too sure if my diagram is clear
    it is very clear

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I accept child isn't the best wording here but to be fair I can't work out what's wrong with the basic table structure - perhaps you can enlighten us to what the problem is?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mike_bike_kite
    perhaps you ...
    me?

    structure, let me show u it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by r937
    me?
    Sorry, you seemed to be implying you know knew what the issue was. I was just trying to find out.

    Quote Originally Posted by r937
    pre-order my book Simply SQL from Amazon
    Has the restriction been lifted now on advertising merchandise on the forum?

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Quote Originally Posted by mike_bike_kite
    Has the restriction been lifted now on advertising merchandise on the forum?
    Actively participating members have always been permitted to include their web sites in their forum signatures. While outright advertising (say for automobiles) is always flatly unacceptable, in a case when a new product (book, software, etc) that is directly relevant to DBForums members then it is considered an announcement instead of advertising.

    We've had numerous similar situations with other users. At one point in time I considered announcing the release of Joe Celko's books (I derive no reveue from them) in my own sig. The decision is subjective, made jointly by the moderators, and has never been an issue for contention.

    -PatP

  12. #12
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44
    Hey folks, thanks for the responses. The truth is, I wasn't sure if there was a problem to begin with. I just wanted to make sure that I was modeling it correctly. I didn't know if it violated some rule of database design to reference the same PK from one table as a FK twice in a different table (and then form a new composite PK from the two FKs).
    Paul Palubinski

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    No, referencing the PK from one table twice in another table is no problem. Other than having to rename at least one set of the PK columns, there is no problem at all.

    -PatP

  14. #14
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44
    Thanks Pat and Rudy! You guys definitely answered my question.
    Paul Palubinski

Posting Permissions

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