Results 1 to 11 of 11
  1. #1
    Join Date
    May 2006
    Posts
    5

    Unanswered: model many-to-many relationship on objects in the same table

    Hello Oracle experts,

    I'm attempting to model many-to-many (M2M) relationships between objects (rows) in the same table. It's simple enough to to model M2M relationships between objects in different tables, however, I'm having trouble figuring out the same table case.

    The table would look something like this:


    RELATED_OBJECTS:

    | OBJECT_ID | NAME | DESCRIPTION | ICON_SOURCE | OBJECT_URL |


    Each object (row) in this table would be related to one or more other objects in the table, and each of those objects would be related back to it. In other words the relationship is 2-way, and can be looked up from either object.

    My first thought was to use a secondary table that is solely responsible for storing the relationships:


    RELATIONSHIPS:

    | OBJECT_ID_1 | OBJECT_ID_2 |


    When a relationship is created between two objects they are inserted into the RELATED_OBJECTS table (if they're not already in there), and then each of their OBJECT_ID's is inserted into the RELATIONSHIPS table (in a single row).

    This seemed like a reasonable approach, but it started to get messy when I tried to formulate the SQL queries. E.g. "Given an OBJECT_ID, find me all objects (rows) in the RELATED_OBJECTS table that are related to it."

    The area I'm struggling with is how to determine the OBJECT_ID's of related objects from the RELATIONSHIPS table. I'd have to query and set the where clause to OBJECT_ID_1 or OBJECT_ID_2 because the OBJECT_ID could be in either one of the two columns since they are of the same attribute.

    For e.g.


    SELECT * FROM RELATIONSHIPS
    WHERE OBJECT_ID_1 = ? OR OBJECT_ID_2 = ?

    (please excuse the Java CallableStatement syntax for the variable, I live in the Java world)

    From here I could get all the rows that match the query, but then I'd have to determine which OBJECT_ID is the related one for each row, and not the one I used to query on. I'm not even sure if this logic can be implemented in a single query with a join - possibly using a nested select??

    Anyhow, the more I thought it about it the more complicated it got, and the more I decided that there must be a simpler way to do this.

    Ideas?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Each object (row) in this table would be related to one or more other objects in the table
    I see a basic problem with the statement above if you start with an EMPTY table.
    How do you get the 1st row inserted when there is ZERO other rows to associate to it?
    Your "requirement" says ONE or more associated rows must exist.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2006
    Posts
    5
    Okay, let me clarify. The objects in the RELATED_OBJECTS table are only inserted when the relationship is created. So these two objects would already exist somewhere, in space, and when a relationship is established between them each object would be inserted into the RELATED_OBJECTS table, then a row would be inserted into the RELATIONSHIPS table that represents the relationship between the two.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    With free advice you get what you paid for it.
    ==================================
    When a relationship is "created", insert TWO rows in the RELATIONSHIP table
    |---MY_ID-----|--OTHER_ID--|
    | OBJECT_ID_1 | OBJECT_ID_2 |
    | OBJECT_ID_2 | OBJECT_ID_1 |
    I "assume" that when querying for relationships, you "know" your own ID.
    SELECT OTHER_ID FROM RELATIONSHIP WHERE MY_ID = :ME
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The other question to answer, is .... what are the objects. It is possible that you should have a parent child table instead of jaming everything into one table. Please give us some background on what you are trying to do.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    May 2006
    Posts
    5
    Quote Originally Posted by anacedent
    I "assume" that when querying for relationships, you "know" your own ID.
    SELECT OTHER_ID FROM RELATIONSHIP WHERE MY_ID = :ME
    Correct, I know one ID and I want to find all ID's that are related to it. However, since the relationships are two-way and both columns in the RELATIONSHIP table are the same attribute, e.g. they are both OBJECT_ID from the RELATED_OBJECTS table, there's no way to know which column the ID I'm searching for is in. Therefore I have to check both columns for my ID, and when a match is found (row returned) choose the "other" column as the related object.

    Quote Originally Posted by beilstwh
    The other question to answer, is .... what are the objects. It is possible that you should have a parent child table instead of jaming everything into one table. Please give us some background on what you are trying to do.
    These objects are all "first class" collaborative objects in an application. They could be documents, discussion topics, task objects, meeting objects, etc. that are not naturally related to each other in any way. The idea is to allow users to related them together. When they do this we need to store off some metadata about each object that is participating in the relationship, e.g. the RELATED_OBJECTS table, and we need to store the relationship, e.g. the RELATIONSHIPS table.

    Unfortunately I can't think of a way to do this in a parent-child relationship that would work, since each object participating in a relationship is a "first class" object (the same "type" of object), and the relationship is bi-directional. To further complicate things I can't rely on "types" of objects so I couldn't have DOCUMENTS and DISCUSSION_TOPICS tables. My schema has to be generic enough to accept any type of object, and new types of objects could be added at any time. That's why I chose to have a generic table that holds all the object metadata.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > Therefore I have to check both columns for my ID

    I disagree with the statement above

    >both columns in the RELATIONSHIP table are the same attribute

    The above statement is where you make thing difficult for yourself.
    The "left" column is "MY_ID" & the right column is RELATED_ID!
    Not if you do the following:

    When a relationship is "created", insert TWO rows in the RELATIONSHIP table
    |---MY_ID-----|--OTHER_ID--|
    | OBJECT_ID_1 | OBJECT_ID_2 |
    | OBJECT_ID_2 | OBJECT_ID_1 |

    SELECT OTHER_ID FROM RELATIONSHIP WHERE MY_ID = :ME

    The query above will return ALL object_IDs that are related to MY_ID.
    The data in the RELATIONSHIP table is denormalized on purpose to make
    querying it easy, simple & straight forward.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    May 2006
    Posts
    5
    Perhaps I didn't make it clear, or maybe I'm just not understanding it.

    From my perspective there is no distinction between MY_ID and OTHER_ID. Two "first class" objects, A & B are related to each other - their metadata is inserted into RELATED_OBJECTS - their ID's (A.OBJECT_ID and B.OBJECT_ID) are inserted into RELATIONSHIPS - the relationship can be looked up from either object A or B, e.g. if you lookup the relationship using A.OBJECT_ID you should get object B, and vice versa.

    I don't understand how to distinguish between MY_ID and OTHER_ID.

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Perhaps I didn't make it clear, or maybe I'm just not understanding it.
    If only a single record is inserted into RELATIONSHIP when a "relationship" is created, then your existing view of the world is correct.
    However if TWO records like:
    |---MY_ID-----|--OTHER_ID--|
    | OBJECT_ID_1 | OBJECT_ID_2 |
    | OBJECT_ID_2 | OBJECT_ID_1 |
    are inserted in RELATIONSHIP, you can be assured that:
    SELECT OTHER_ID FROM RELATIONSHIP WHERE MY_ID = :ME
    will give complete & correct results; as long as you know & use MY_ID in the WHERE clause.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    May 2006
    Posts
    5
    Oh, that's what you mean by:

    > When a relationship is "created", insert TWO rows in the RELATIONSHIP table

    Sorry, didn't catch that. Good suggestion, thx.

    Does anyone have any other ideas on how to model this in a more "elegant" way?

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    select left_id from relationship where right_id = my_id
    UNION
    select right_id from relationship where left_id = my_id
    /
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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