Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2004
    Posts
    4

    Many to Many relationship keys

    I have a typical Product - OrderDetail - Order. In OrderDetail the primary key is conformed by idProduct+idOrder. Now, I need to keep track of the individual product ordered, to express something like "the product X ordered in order Y has zero o more defects.
    I hope my question is clear, i can post my current design if necessary.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you please repeat the question? your many-to-many table and compound primary key is correct

    perhaps you just need to add a defectqty column to the table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Posts
    4

    Question corrected (with db design attachment)

    I can´t place a simple defect counter, because each defect has a description (is a entity by itself) and the purpose of the application is to determine some information about the defects, such as the more ocurrent, and so on.

    In the attached file you can see an Credit, CreditDetail And CreditCause. A Credit is a disclaimer (made by the customer) of defect found in an ordered product. In CreditCause we have the allowed causes of defects. I need to link each of the items ordered with one or more of this causes.

    In the current design, I can't express "product ordered in that especific order had the following causes of failure: X Y Z "

    Thanks
    Attached Thumbnails Attached Thumbnails db.jpg  
    Last edited by LeninTorres; 11-10-04 at 09:20. Reason: Explanation of db design

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by LeninTorres
    I need to link each of the items ordered with one or more of this causes.
    then you need a many-to-many relationship between the OrderDetail table and the CreditDetail table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Posts
    4
    I think that, but I am not sure How this relationship can be expressed. I mean, normally, in a many to many relationship you have two primaryKeys thah are "added" in the junction table. But in this case, Is legal to put the two fields composing the primary key of OrderDetail table with the fields composing CreditDetail table int a BIG primary key in the new junction table?

    Help me please, I never faced a problem like this before.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, that is how you do it, migrate each of the primary keys, even if they are composite) as foreign keys into the relationship table

    note you will not need 2 idPRoduct columns, just one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2004
    Posts
    4
    First thing first: Thanks for prompt reply. I will now do the new design and i hope you can check it

  8. #8
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    Question: Are the causes of failure quantified or qualified? Would you need to have a boolean failure column in the variety, flower and grade tables? Seems like it would be more informative than simply saying the product failed.
    Last edited by certus; 11-13-04 at 00:23.

Posting Permissions

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