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.
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 "
Last edited by LeninTorres; 11-10-04 at 08:20.
Reason: Explanation of db design
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.
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.