For some reason i'm having difficulty thinking through in my head how I should set this database up. I'm looking at the relationships and I know something isn't right. I only just started so I want to make sure it's setup correctly before I start dumping tons of data in it.
It's a database to determine if parts we sell comply with various different regulations (5 to be exact)
The parts each contain many components which we source from various vendors. Those components could be used in many different parts.
I'm verifying that each component complies - and if doesn't- what chemical(s) it contains that causes it not to.
Ultimately i want to be able to say ok, this part has 30 components, all comply, it complies. Or it has 2 components that are causing it not to and these are the restricted substances it contains.
I have the following tables:
*ComponentValidation: ComponentPartNumber is the pk (our internal part number), VendorPartNumber, a yes/no checkbox for each of the 5 regulations to say yes/no it complies or not.
*Chemicals: a list of all restricted substances (pk) , and which regulation they correspond to (some belong to more than 1 regulation)
here's where i'm getting unsure
*PartBOM: FinishedPartNumber, ComponentPartNumber - here i'm unsure because neither is a pk. Each FinishedPartNumber will only use a component once, but again the component itself could be used in multiple finished parts.
*NonCompliantComponents: ComponentPartNumber, Restricted substance(s). Again, not sure here i don't think this is correct . It could be that a component has more than 1 restricted substance