Hi folks - back again!
Have now been reading, playing, thinking, rethinking etc.. for what seems like forever! My poor understanding just cannot figure it out. I may well be overthinking something that is quite simple but now cannot see the wood for the trees so to speak!
We manufacture control panels. At the moment these are bespoke to the customer. They can be a one off for a specific single order or multiple quantities over multiple orders. So far so good - this I can understand. Sometimes the components for the panel are specified by us - sometimes they are specified by the customer - by which we have to abide.
I am struggling with the relationships between the customer (or our) parts specification on a control panel's parts list and how to relate this to suppliers and manufacturers. This is because the customer (or us) may well specify the manufacturer for the part - for which we may have many suppliers and choice of supplier is up to us. Sometimes the customer (or us) will specify just the generic part - and then choice of manufacturer and also supplier is up to us. Where the choice of supplier, or supplier and manufacturer is up to us we make the decision on final choice at the point where we order in the components for the panel - not at point of devising original parts list. This combination of selections may well change for repeat orders going forwards based on facts such as manufacturer or supplier part availability, price, lead times etc.. Original parts list will occasionally change when there has been a drawing revision that may account for discontinued parts or an upgrade to the control panel design. (If I could figure out the parts list conundrum this can be managed with a parts list revision history table I believe.)
I can link generic parts to manufacturers and then from manufacturers to suppliers - but am struggling with a way to build a record of customer specifications that deals with either a specified manufacturer's part (from which we then choose supplier) or a generic part (from which we then choose manufacturer and then supplier). I am also struggling with a definition for a "generic part". At the moment I have a Parts table with PK = PartID (Autonumber), Subcategory, Description, which links to a Category/Subcategory table and onto a Category table. I can however see the danger with this approach as the parts table is reliant on "description" and so could easily contain parts that are the same but described differently. We need some way to record "generic parts" as sometimes we or customers design a panel where the parts list specifies just (for example) 24amp power supply - not manufacturer or supplier - which is defined at point of ordering in the components (sorry repeating now!).
Sometimes the customers supply their own part reference numbers that relate to a specific part. These can change occasionally depending upon who put the original parts list together (ie parts appear in several parts lists but with different customer part references referencing the same part - which may be a "generic part" or a part for which the manufacturer is also specified (never the supplier - that choice is always down to us)). This I believe I can tie up with a DrgPart/Part table - but only if I can figure out a robust way of defining a part and relating that to whether or not a manufacturer is also specified!
I have probably asked way too many questions in my ramblings above, but any pointers or advice or clarification on where my thinking is not quite right will be more than welcome!
Regards,
Karen.