implement parent and child in build of materials “

Question

implement parent and child in build of materials “

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

SJTerrill SSCrazy Points: 2938 More actions · Answer 1

Totally unrelated to topic, I know... but here's some fairly cool pictures of California interchanges, including the 'four-level' in L.A.

http://members.cox.net/mkpl/interchange/interchange.html

SJTerrill

yogiberr Hall of Fame Points: 3679 More actions · Answer 2

Hi folks,

I’m working away at this end and would like to ask if I’ve followed you correctly regarding:

1)the number of primary keys for each table

2)the relationships between them.

So, I’ve listed the tables, with my interpretation of them.

tblProduct (single primary key)

productId INT NOT NULL IDENTITY(1,1),

productDesc VARCHAR(150) NOT NULL

tblBom (single primary key)

bomId INT NOT NULL IDENTITY(1,1),

bomName VARCHAR(150) NOT NULL

I think, so far so good 🙂

Now:

tblBomProduct (composite primary key?)

bomId INT NOT NULL REFERENCES(tblBom.bomId)

productId INT NOT NULL REFERENCES(tblproduct. productId)

tblProductDependent (composite primary key?)

productId INT NOT NULL REFERENCES(tblproduct. productId)

dependentId INT NOT NULL

I am not sure how to relate the “dependentId” to

TblProduct.productId…

1)should I relate it? (I was getting errors when I tried to)

2)should both these fields compose the primary key for this table ?

tblBomDetail (composite primary key?)

bomId INT NOT NULL REFERENCES(tblBom.bomId),

productId INT NOT NULL REFERENCES(tblproduct. productId)

Now, using the car analogy, would the productId that represents the car,(ie, the highest-level product )be contained in tblBomDetail?

Sorry if all of this is a bit dis-jointed..

What you’re actualy seeing is the contents of what is left of my dodgy brain...

Cheers,

yogiberr

jpipes SSChampion Points: 12230 More actions · Answer 3

Coming along just fine. Here are the answers to your questions:

1) Yes, both BOMProduct and ProductDependent have composite PRIMARY KEYS:



--Using my original schema...

ALTER TABLE BOMProduct

ADD CONSTRAINT PK_BOMProduct PRIMARY KEY (BOM, Product)

, CONSTRAINT FK_BOMProduct_BOM FOREIGN KEY (BOM) REFERENCES BOM (BOMID)

, CONSTRAINT FK_BOMProduct_Product FOREIGN KEY (Product) REFERENCES Product (ProductID)

--

ALTER TABLE ProductDependent

ADD CONSTRAINT PK_ProductDependent PRIMARY KEY (Product, Dependent)

, CONSTRAINT FK_ProductDependent_Product FOREIGN KEY (Product) REFERENCES Product(ProductID)

, CONSTRAINT FK_ProductDependent_Product2 FOREIGN KEY (Dependent) REFERENCES Product (ProductID)

2) As for BOMDetail:



ALTER TABLE BOMDetail

ADD CONSTRAINT PK_BOMDetail PRIMARY KEY (BOM,Product)

, CONSTRAINT FK_BOMDetail_BOM FOREIGN KEY (BOM) REFERENCES BOM (BOMID)

, CONSTRAINT FK_BOMDetail_Product FOREIGN KEY (Product) REFERENCES Product (ProductID)

The trick is recognizing, and becoming absolutely familiar with, the specific purpose each of the tables serves. The tables are named and function similarly, but all slightly differently. The key to the solution is understanding those subtle differences...good luck.

yogiberr Hall of Fame Points: 3679 More actions · Answer 4

Hi, thanks for all the help folks, I have made some decent progress:

Here is my table structure so far:

tblProduct

productId productDesc

---------- ---------

1car

2frontWheel

3rearWheel

4frontWheelBolt

5 rearWheelBolt

6frontWheelWasher

7 rearWheelWasher

tblBom

bomId bomName

------ ---------

1frontWheelBOM

2rearWheelBOM

‘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

3bolt

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