Parts/Work Orders Database Design

[snewton]

Hi all,

I've been reading the threads here but I'm not quite sure any of them fit my requirements here. This is my first database!

I have parts or fabricated assemblies with unique numbers. Schema for referring to each part in my work orders, etc. is like BLDG.DIV.001.001 = Building.Division.Number.Finish/Paint

Each Job has many parts, each part can potentially be in many jobs.


Each Building has many parts, each part can be in many buildings. (however, I will have things like ENTRY.ARCH.123.001 and EXIT.ARCH.123.001 These will be the SAME PART, but different buildings...)
Each Division has many parts, each part has only one division.
Each Material can be in many parts, each part can have many materials.

The crux is this. I want the work order Form to have the Project Manager enter in a list of parts they want fabricated, and a report generated that shows all the relevant information for each of the parts. I would also like to have the quantity ordered subtracted from the quantity total.

My eventual goal is to have these parts tracked for shippping. (Shipping Container Number, Palette Number, Warehouse Location, Date Shipped, etc.)

Is this making sense? See attached for how far I've gotten. Hopefully this is 3NF? Any guidance or caveats would be greatly appreciated!

Thanks,
Stephen

[snewton]

Bump for justice! I've changed the relationships a bit based on my perusing of the internet.

[John_G]

Hi -

I took a look at the second diagram. You seem to have the normalization part sorted out OK, and you understand the concept of junction tables, but I have serious issues with your relationships (the ones on the diagram, that is!). In an entity - relationship diagram, by following the relationship links, in general there should be only one path between any two tables. On yours, there are FOUR different paths to get from tblJobs to tblParts. This can lead to all sorts of inconistent data, and is almost guaranteed to lead to major headaches when it comes to designing queries and forms. To start, here is what I suggest you look at:

Parts are directly related to WorkOrders which are directly related to Jobs. Parts are not directly related to Jobs. Delete tblPartsJobs and its relationships.

Buildings are directly related to Jobs. Parts are not directly related to Buildings. Delete tblPartsBuildings and its relationships.

Finishes are directly related to Parts. Finishes are not directly related to Jobs. Delete tblJobFinishes and its relationships.

So, now you have only one pathway from Jobs to Parts:

Jobs can have many Workorders which can have many Parts.

For Shipments, the design will depend on what the "business rules" are for shipments. A shipment obviously can contain many parts - but:
Can one shipment contain parts for many different work orders?
Can one work order require more than one shipment to complete?
Can one shipment consist of many containers (your structure indicates that it cannot).
Your structure shows that One Shipment consists of one container which contains only one part type. Is this correct?

Shipments are not directly related to Jobs, so delete that relationship.

What is UOM? There is a problem there. The relationships between tblParts, tblContainers and tblUOM are circular; one of those relationships should not be there.

These are some things I would look at first -if you can clarify some of the questions about shipments, it will help.

Cheers!

John

[snewton]

Hi -

I took a look at the second diagram. You seem to have the normalization part sorted out OK, and you understand the concept of junction tables, but I have serious issues with your relationships (the ones on the diagram, that is!). In an entity - relationship diagram, by following the relationship links, in general there should be only one path between any two tables. On yours, there are FOUR different paths to get from tblJobs to tblParts. This can lead to all sorts of inconistent data, and is almost guaranteed to lead to major headaches when it comes to designing queries and forms. To start, here is what I suggest you look at:

Parts are directly related to WorkOrders which are directly related to Jobs. Parts are not directly related to Jobs. Delete tblPartsJobs and its relationships.

Buildings are directly related to Jobs. Parts are not directly related to Buildings. Delete tblPartsBuildings and its relationships.

Finishes are directly related to Parts. Finishes are not directly related to Jobs. Delete tblJobFinishes and its relationships.

So, now you have only one pathway from Jobs to Parts:

Jobs can have many Workorders which can have many Parts.

For Shipments, the design will depend on what the "business rules" are for shipments. A shipment obviously can contain many parts - but:
Can one shipment contain parts for many different work orders?
Can one work order require more than one shipment to complete?
Can one shipment consist of many containers (your structure indicates that it cannot).
Your structure shows that One Shipment consists of one container which contains only one part type. Is this correct?

Shipments are not directly related to Jobs, so delete that relationship.

What is UOM? There is a problem there. The relationships between tblParts, tblContainers and tblUOM are circular; one of those relationships should not be there.

These are some things I would look at first -if you can clarify some of the questions about shipments, it will help.

Cheers!

John

John,

Thank you for your thoughtful reply and advice. The intent here was to create a unique part number for each element, consisting of BUILDING.DIVISION.PartName.FINISH. So I created this monster.

After reviewing your comments, I think it may be too ambitious to allow this database to encompass ALL JOBS. So, I'm going to delete Jobs entirely, that way I can have Building, Division, Partname all in one table. This would be much easier if one partID only had one Finish!

As for shipping: One shipment can contain any part, and any PARTIAL or ENTIRE quantity of any part. That's why I have tblPARTS.PartQtyTotal and JtblPARTSWORKORDERS.QuantityOrderedNow and tblCONTAINERS.QuantityOnContainer.
One shipment should probably be limited to one container. If I have a semi leaving here full of stuff, I'd consider that a single shipment. However, in future, we may track much smaller shipments like UPS and whatnot. Did I have the tblCONTAINERS and tblSHIPMENTS relationship reversed?

Each container will be full of any number of parts, and any quantity of said parts, Partial or Complete.

I'm not sure I see what's wrong with the UOM relationships? (Unit of Measurement.) Really, I only have four values in there (Linear Feet, Square Feet, Each, Cubic Feet), but since they are repeated, and I wanted to be 3NF, I pulled it out. I guess UOM would be driven by the value of UOM for each part anyway, so I shouldn't have another field for it in CONTAINERS. Is that right?

Thanks again for your help. (Oh, and I hope my relationships are forever one-to-many.)

Stephen

[John_G]

Steve -

Now I see part of the problem. Each entry in tblParts describes parts that you have on hand (or manufacture to spec, possibly), and each of these different parts are identified by PartID. A part is described by not only the entry in tblParts, but also groups of materials and finishes listed in tblPartsMaterials and tblPartsFinishes. Each entry in tblParts describes a specific part type - but it doesn't (and maybe shouldn't, so you don't need PartQtyTotal) say anything about how many there are.

Now, when a work order requests, say, 5 of parts ABC, it is some or all of those 5 that are put in a shipping container. Your relationship between tblParts and tblContainers is putting the descriptors in the container, not the parts, so it is no correct. The relationship is actually between tblPartsWorkorders and tblContainers, and it requires an intermediate junction table to record the shipments since a) a container can contain many parts and b) one work order request can require several shipments to complete.

Fields in that junction table could be: tblPartsWorkordersID (you need to put that into tblPartsWorkordersID), PartID, Quantity_Shipped, Container_ID, and Shipment_ID.

Does tblContainers describe individual containers which are sent back and re-used, or does it describe container Types like cardboard boxes? If the latter, (I think) you will need another table for Container_ID - Container_Type, so you can track each shipped container.

Your DB does require careful thought (they all do) - but properly done it does not have to be the monster you fear.

More food for thought.

John

[snewton]

John,

Thank you again for your help with this!

Let me clarify my intent here. This is for a large job, with about 1800 or so individual components. They are all fabricated architectural and structural elements. They are built ONCE and then shipped. The work order is from ME to the shop, to create the required components for this large construction project. The PartQtyTotal is the number required to construct the entirety of the project, though I may call upon the shop to produce a small quantity as labor loading allows (hence the JtblPartsWorkOrders.QtyOrderedNow).

Then the part is stored for some time, and shipped when called upon, based on the requirements of the construction scheduling. The containers (think 40ftx10ftx10ft) will be single-use, filled to the rim based on any number of criteria (i.e., all of one division, all of one building, a mixture of both). I could probably call each container a shipment instead, but I want to number the containers so I know which one of the potentially many on a given day are received on the other end. I will also use this database to generate bills of lading and part number tags for each of these many parts. I'm going to be a hero if this works.

I don't believe it is appropriate to tie WorkOrders to Shipments.

Thanks, I was hungry.
Stephen