Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2009
    Posts
    27

    Question how to create many-to-many relation?

    I'm at the stage of designing a database for a small restaurant programm and cannot solve one problem. on the picture you can see the problem. i have 2 tables:

    Products
    Menu/Dishes

    Dishes can consist of severeal products and products in turn can be a part of several dishes. how to implement this in my case? Should I create any intermediate table for it or something?

    Thank you in advance!
    Attached Thumbnails Attached Thumbnails many-to-many.JPG  

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Yes, you create an intermediate table. It's primary key will include the primary keys from your other two tables.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Mar 2009
    Posts
    27
    Blindman, I got it thank you for help!
    Last edited by emilh; 03-13-09 at 05:49.

  4. #4
    Join Date
    Mar 2009
    Posts
    27
    Blindman, I got it thank you for help!
    Last edited by emilh; 03-13-09 at 05:49.

  5. #5
    Join Date
    Feb 2009
    Posts
    54
    I would like to ask more in-depth this question:
    in the intermediate table is it that the referencing rows MUST form a primary key, or can I create an "indipendent" primary key and just reference the rows without constraints to other tables? Actually I've already tried it and it seems to work, but I don't know if I'm missing anything. I need this to be able to leave one column "null" of three or four referenced tables. With my previous design postgres would retreive unwanted rows.
    I hope I'm being clear enough.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    No, there is no requirement that the foreign keys must form a composite primary key.
    But you should enforce uniqueness on the pair, and a primary key is a simple method of achieving that. The other option is to create a unique non-clustered index or a constraint.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Feb 2009
    Posts
    54
    and what undesirable effects would have not enforcing uniqueness on the pair?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by shwe
    and what undesirable effects would have not enforcing uniqueness on the pair?
    search: removing duplicates 2,960,000 results

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

  9. #9
    Join Date
    Feb 2009
    Posts
    54
    haha, smart answer!

Posting Permissions

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