Zydeceltico
Registered User.
- Local time
- Today, 07:57
- Joined
- Dec 5, 2017
- Messages
- 843
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
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