What should be the suitable design!

[cap.zadi]

Hi



I am looking forward to record some transactions and scenario is as below;


TblInspection!
InspID: PK
InspDate:
Shift: (Day/Night)
Inspector: FK from tblEmployees
WorkCenter: FK from tblWorkcenters


TblInspectionResults
ResultID : pk
InspID : FK (Joint)
CartNo :
CartType:
Product: FK from tblProducts
GoodQty :
RejectQty:
ScrapQty:

Notes:
- One Shift can have many carts to inspect with different products. So one to many relationship between TblInspection & TblInspectionResults.
- Each Product have different qty on each cart to inspect (Like Product A have 20 units and product B have 30 units per cart)
- Results can be in three categories as "GoodQty", "RejectQty" and "ScrapQty".
Example: There can be 10 units good, 5 rejected and 5 scrapped which can be recorded against each field but the issue is to mention the reason for reject and scrap.

May out of 5 rejected units, there is 2 units because of defectA and 3 units because of defectD and same can be in scrapped Qty.

Requirement:

How to make the 3rd table to link for rejected and scrapped quantities for each kind of reasons?

Any help will be appreciated?

[CJ_London]

not sure what your problem is - if reasons are wide and varied, just include a 'reason' text field in the results table. If they are relatively limited, have a third table with a PK and those reasons and a reasonFK field in your results table. Or both if you need a reason 'type' + further explanation.

reason textbox - 'primer showing on underside'

reason table (PK=1) 'paint finish'

both - PK='paint finish', explanation textbox - 'primer showing on underside'

[cap.zadi]

Dear Sir

Thanks for your time.

The problem is how to describe the rejects and scrap assigning a reason for this and as said above, the 5 rejected units can have different reasons and same scrapped units as well.

So what should be design of tables and relationship especially for reasons?

[CJ_London]

you need to describe your business in more detail but I would suggest in that case your table design is wrong. It should be something like

TblInspectionResults
ResultID : pk
InspID : FK (Joint)
CartNo :
CartType:
Product: FK from tblProducts


TblOutcomes
OutcomePK autonumber
ResultFK - link to inspection results
OutcomeType: text or numeric identifier (e.g. Passed, Reject, Scrap)
Qty
Reason - or reason type plus explanation as above. Would be left blank for a 'passed' outcome