Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2011
    Posts
    39

    Unanswered: Many to Many relationships

    Is it fine to have multiple many to many relationships for two tables? http://img16.imageshack.us/img16/3782/databasemq.jpg

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Yes, no problem with that.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why is your DeliveryDetails table linked to your Product table?
    Deliveries would seem to be related to Orders, and only indirectly to Products (through Order, or through ProductOrder).
    I think you should make DeliveryDetails a child table of ProductOrder. ProductLosses too, perhaps, though I'm not sure of the purpose of that table.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by blindman View Post
    Why is your DeliveryDetails table linked to your Product table?
    Deliveries would seem to be related to Orders, and only indirectly to Products (through Order, or through ProductOrder).
    I think you should make DeliveryDetails a child table of ProductOrder. ProductLosses too, perhaps, though I'm not sure of the purpose of that table.
    Blindman, this post is connected to another post of caa5042. He rents stuff. Sometimes things end up being broken or are missing when the customer returns the rented stuff.

    I can understand your remark about the missing link between DeliveryDetails and ProductOrder. It is possible however, that he did this on purpose. Suppose a client orders 5 "small tables", but another client broke them or didn't return them in time. By not making DeliveryDetails dependent of ProductOrder, he can deliver other goods than those that were ordered, like delivering "medium tables".

    ProductLosses should indeed be linked to DeliveryDetails, as only delivered goods can be broken or become lost.
    On the other hand, I wonder if I would model it this way.
    Perhaps I would add a few extra columns to DeliveryDetails to capture losses. It wouldn't be architecturally as nice as an extra table, but I think it would be more practical and easier to program.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Sep 2011
    Posts
    39
    hey guys...yes i was thinking about that. Linking delivery details and product losses to product order table...thats what i did initially....would you recommend this? Do you think it would look too 'messy'?
    ...im new to sql so please give me some suggestions..

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Wim View Post
    Suppose a client orders 5 "small tables", but another client broke them or didn't return them in time. By not making DeliveryDetails dependent of ProductOrder, he can deliver other goods than those that were ordered, like delivering "medium tables".
    That should be a separate order, or an addendum to the original order.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Sep 2011
    Posts
    39
    blindman...but how about if a customer places an order for several products...and the products dont all get delivered at once...but rather, in parts....thats why i linked delivery details to the Product table and included a Quantity field...so that you can put i how many and what type of items were delivered at a certain time....do you think this is the way to do it??

  8. #8
    Join Date
    Sep 2011
    Posts
    39
    the way it is right now, will i be able to create a computed column in delivery details that uses values from the productorder table?....

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That still sounds like multiple deliveries per order to me.
    If it's not practically useful, then it's practically useless.

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

  10. #10
    Join Date
    Sep 2011
    Posts
    39
    yes, its meant to be that way..multiple deliveries per order...so should i keep things the way they currently are?

Posting Permissions

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