table design parts drawing revisions and suppliers (1 Viewer)

Hi all -

I'm working on yet another inspection db. This one is separate from the last.

We outsource 8 component parts to four different production houses.

We have drawings for each part. Each drawing (but one) has been through several revisions (A, B, C, etc...)

One part is currently made by two different companies. Each of those companies is working from a different revision of the same drawing. The drawing revision is extremely minor - nothing dimensional - but a revision nonetheless. One of the companies is finishing a very large PO on the older of the two drawing revisions. This will be received in partial PO shipments over the course of the next year or more. The company working from the older revision will no longer be a supplier after they finish the current PO.

For auditing purposes, while we do our inspections of these parts I need to record which drawing revision the parts were made to (as well as the supplier).

Right now I have tblParts (PartID, PartName); tblSuppliers (SupplierID, SupplierName); and a linking table called tblPartsSuppliers (ID, Part_FK, Supplier_FK).

I cannot decide if I should have a tblDrawings with Drawing_ID, DrawingName, Part_FK, Supplier_FK, and DWG Revision OR......something different. I also cannot figure out where to link it into the larger schema in order to ensure that I can get the most efficient queries later. Queries will likely simply be to verify that all shipments on POs were in fact inspected and which drawing revision they were inspected too.

We will never go backwards with drawing revisions - only forwards.

I am not sure where to assign the actually drawing revision number and I am also really unclear how to assign a drawing revision to a Supplier especially given that two suppliers are making the same part to two different revisions and also because one supplier (a different from the ones just mentioned) makes four of the eight parts.

As always - looking forward to your insights!

Tim

Hi Tim. Not sure I follow all that but if a supplier will never work on more than one version of a drawing for a particular part, then I would assign the version number to the supplier, so when you assign a supplier to a part and you receive it, then you can tell which version of the drawing it was based on. This is just one interpretation, and there could be others.

Hi Tim,

I think you are beating yourself up a bit over this.
What you have is a "Part" and "Version".
Each supplier provides one (or more) Part/Version.

All YOU need to know is which was ordered from whom. Treat each Part/Version as a different item, not some union of "Part" and "Revision".

Mark_ said:

Hi Tim,

I think you are beating yourself up a bit over this.
What you have is a "Part" and "Version".
Each supplier provides one (or more) Part/Version.

All YOU need to know is which was ordered from whom. Treat each Part/Version as a different item, not some union of "Part" and "Revision".

Click to expand...

Yeah - I was barking up wrong tree I think. Remembering that this db is first and foremost for inspecting received parts - not really for inventory - I had to do a mental backflip to grasp that I am still inspecting Orders that happen to be parts versus inspecting Parts - at least where table design and structure are concerned. This is primarily because we receive partial orders.

I've attached a pic of the relationships the way I think will work well for what I am after.

theDBguy said:

Hi Tim. Not sure I follow all that but if a supplier will never work on more than one version of a drawing for a particular part, then I would assign the version number to the supplier, so when you assign a supplier to a part and you receive it, then you can tell which version of the drawing it was based on. This is just one interpretation, and there could be others.

Click to expand...

I think I have it. The mental roadblock I was having was not realizing I am inspecting ORDERS of parts - not simply inspecting Parts - - which changed my table design and relationships.

And I can already see that I need to move fields QTYRecvdThisInspection and QTYBalanceThisDate from tblOrders to tblInspections - probably.

I'd do it a bit differently myself.

Your ORDER is a child to a supplier, because that is where it is coming from.
Your ORDER is a child to a part, because that is what is ordered.
A Part consists of not just what you call the piece, but also which version of the piece you have.

This means you'd move Version into the Part table.
This also means that Part isn't a parent to the supplier, but rather a parent to the order.

The only link between supplier and product should be a "Does this supplier make this part/version of this part" to limit which part(s) can be on a given order.

I would step into this more, but the others have already steered you in the right direction. This is just a case of "purity of entity." You determine where something goes by purity of definition. The question here is "What did you order?" The answer is "something based on drawing number XYZ123." Who CARES what the part is actually called? Especially when you point out that the revisions in some cases are really minor.

What I see is actually that for THIS situation, you don't have parts. You have parts based on drawings. So PARTS might easily be a child of DRAWINGS.

The_Doc_Man said:

What I see is actually that for THIS situation, you don't have parts. You have parts based on drawings. So PARTS might easily be a child of DRAWINGS.

Click to expand...

This was a big a-ha for me. Changes the model for sure. Seems straight ahead now.

Mark_ said:

The only link between supplier and product should be a "Does this supplier make this part/version of this part" to limit which part(s) can be on a given order.

Click to expand...

I follow the rest of your post but if Order is child to both Part and Supplier what mechanism do I have for limiting which part drawing revisions are done by which Suppliers? I've got it mapped out graphically and on paper and am unclear how to accomplish that one piece - how to limit which part can be made by a given supplier based on drawing revision.

Remember - right now - we have one part being made by two different suppliers. each supplier is working from a part drawing revision that is different from the other one. Supplier A has Dwg1 Rev1 and Supplier B has Dwg1 Rev2. This could happen in the future also. Supplier C has Dwg2, Dwg3, Dwg4, and Dwg5 (each with their own revisions).

The only way I've been able to see it is to have another table (tblPartsSuppliers) with fields: PartSupplier_ID, Part_FK, Supplier_FK, and DWGRevision....and then link PartSupplier_ID to tblOrders on PartSupplier_FK.

I apologize for the mental roadblock.

Thanks,

Tim

The_Doc_Man said:

What I see is actually that for THIS situation, you don't have parts. You have parts based on drawings. So PARTS might easily be a child of DRAWINGS.

Click to expand...

I'm working through this model also at the moment.

Remember, I might be wrong, too, because in this discussion, only YOU really know the business model. Always, always, always take ANY advice you get here as coming from the outside looking in. We can't see behind the scenes. So if we give you an idea, the FIRST duty to yourself is to bounce it off of reality.

Having offered a "standard disclaimer" now, I'm glad to have given you that insight and hope it helps you.

I wrote a comprehensive drawing log system for a steel fabrication company. We didn't deal with parts separately although part number was an attribute of a drawing. Our purpose was to track who had what version of each drawing and what the current status of the drawing was if the drawing was back in our court. So, whenever a drawing went anywhere, it went as drawing number + revision.

That being said, you may not need a part table at all. you might just need to keep part number as an attribute of the drawing. You can always validate part number against some other application's part table. When your order is placed, THAT is where rev must be specified. If the order is by drawing number, then it is easy to link to the order. If the order is by part number then part number must be unique in your drawing table. When the order is fulfilled, each item is matched back to your app based on drawing + rev or part + rev.