Hi, thanks for all the help folks, I have made some decent progress:
Here is my table structure so far:
tblProduct
productId productDesc
---------- ---------
1 car
2 frontWheel
3 rearWheel
4 frontWheelBolt
5 rearWheelBolt
6 frontWheelWasher
7 rearWheelWasher
tblBom
bomId bomName
------ ---------
1 frontWheelBOM
2 rearWheelBOM
‘products that compose a BOM
tblBomProduct
bomId productId
------------ ---------
1 (frontWheelBOM) 4 (frontWheelBolt)
1 (frontWheelBOM) 6 (frontWheelWasher)
2 (rearWheelBOM) 5 (rearWheelBolt)
2 (rearWheelBOM) 7 (rearWheelWasher)
tblProductDependent
productId dependentId
--------- ---------
1 (car) 2 (frontWheel)
1 (car) 3 (rearWheel)
1 (car) 4 (frontWheelBolt)
1 (car) 5 (rearWheelBolt)
1 (car) 6 (frontWheelWasher)
1 (car) 7 (rearWheelWasher)
tblBomDetail
bomId productId
------------ ---------
1 (frontWheelBOM) 1 (car)
2 (rearWheelBOM) 1 (car)
I hope that I have the correct understanding of this?
Now,
I can return a list of all the PRODUCTS that compose the “finished” product by using the following sProc:
spGetDependentProducts
SELECT DISTINCT p.productDesc
FROM tblProduct p, tblProductDependent pd
WHERE pd.productId = 1
AND pd.dependentId = p.productId
I can also list all the BOM’s that compose the “finished” product by using the following sProc:
spGetProductBOMS
SELECT DISTINCT b.bomName
FROM tblBom b, tblBomDetail bd
WHERE b.bomId = bd.bomId
AND bd.productId = 1
So, I can now have a product that can compose MUTIPLE BOM’s. ( I hope 🙂
As far as hierarchy goes, I was thinking of using the following table:
tblProductCategory
catId catName
------------ ---------
1 car
2 wheel
3 bolt
I would then add the “catId“ foreign key to tblProduct, so that I could then reference the “catId“ as a starting point for a hierarchy etc
It’s probably not ideal, but I thought that it would be something to be going on with. I realise that I haven’t even touched on :
<quote>
"In all our examples none of us have mentioned:
1) quantity-off e.g 4 bolts to a wheel etc.
2) a common product might appear in more than one place in a complex build. For example you may use the same bolts on both a front and a rear wheel. You could even use a common assembly in more than one place (an electric window mechanism for example)."
<quote>
but I am conscious of not:
1) running before I can walk
2) Making my posts too long.
So, that’s how my weekend was spent 🙂
Many thanks for all the help so far.
yogiberr