Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2004
    Posts
    10

    Entity relationship with itself (match-up)

    Hello, and thanks in advance. This is a tough one...

    I have an e-commerce application with a feature that shows matching products for each product. Right now my DB looks like this...


    PRODUCT Table
    Product_ID PK
    Product_Name ...


    PRODUCTMATCH Table
    Product_ID_1 PFK
    Product_ID_2 PFK


    There must be a better way, because It is never easy or simple for me to add or remove matchups because I always have to check both fields.


    I have considered this ....


    PRODUCT Table
    Product_ID PK
    Product_Name ...

    PRODUCTMATCH Table
    ProductMatch_ID PK

    PRODUCT_PRODUCTMATCH_Lookup Table
    Product_ID PFK
    ProductMatch_ID PFK

    But I am not convince that is very good either, because I will have to define some sort of unique grouping for each set of matches.

    ANY SUGGESTIONS?

  2. #2
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    wwwmaster wrote:
    There must be a better way, because It is never easy or simple for me to add or remove matchups because I always have to check both fields.
    I am confused here. Why must you check both fields? If you name the columns of the table from the original:
    Code:
     
    PRODUCTMATCH Table
    Product_ID_1 PFK
    Product_ID_2 PFK
    to:
    Code:
     ProductMatch Table 
    product_id PFK
    matching_product_id PFK
    Now, you only have to get the second column when the first one is given.

    Of course, if Product A matches Product B, and this always implies that Product B matches Product A, then you must have two rows in the current version. One row will be (ProductA, ProductB) and the other row will be (ProductB,ProductA).

    If you want to avoid having two rows, you can create a view on this table that uses only one row to create the illusion of two.

    The view could be based on something like this:

    Code:
     
    Select product_id, matching_product_id from Product_Match 
    Union 
    Select matching_product_id, product_id from Product_Match
    This way, your queries are simple, there is no need to have two rows for matched product pairs.
    The cost you pay for this is in defining the view, and slightly slower times querying the view.
    Depending on the number of products and the number of matched products, this may not even prove to be a problem.

    Hope that helps.

    Ravi

  3. #3
    Join Date
    Dec 2004
    Posts
    10
    Ravi, thanks for the reply.

    You are correct with the latter assumption that if A matches B, then B matches A. However, I am not sure I completely understand your solution. The selection of records in either case is simple, it is the updating that becomes a pain.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    What's the problem with checking both fields? How could you logically not check both fields? You want to associate and dis-associate relationships based on two values, you MUST supply both values.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Dec 2004
    Posts
    10
    Teddy, True. I am looking to see if someone can recommend a better architecture.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    I think your initial schema is just dandy.

    One table for the Items. One table for relationships between those items. I'm not sure what you were getting at with the grouping and whatnot... do you need to have multiple configurations for each item?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    No, I am not aware of a better architecture.

    Updating a row. Why would you do that?
    Updating is the same as a delete followed by an insert.

    Ravi

  8. #8
    Join Date
    Dec 2004
    Posts
    10
    Quote Originally Posted by rajiravi
    No, I am not aware of a better architecture.

    Updating a row. Why would you do that?
    Updating is the same as a delete followed by an insert.

    Ravi
    Well, that is what I've been doing. I guess I've been doing ok. Thanks for all the help guys!

Posting Permissions

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