Zydeceltico
Registered User.
- Local time
- Today, 04:17
- Joined
- Dec 5, 2017
- Messages
- 843
Hi All -
The seed of this post is from a different post of mine - on a completely different subject/question. I'm copying and pasting about 10% of the OP in case anybody recognizes text from a previous post.
I want to further normalize the db but am stuck - probably because I am not aware/familiar with available mechanics. I don't know what I don't know.
We manufacture products. Some products are made up of two distinct products welded together BUT the two distinct products that are welded together are also sold as their distinct selves meaning NOT ALL products go through weld/assembly. So we may produce PartA and PartB and weld them together to make PartC or we may just sell them as PartA or as PartB - and they do not have to sell together. All products are inspected by QC. Not all products go through all manufacturing processes which means that depending on the job requirements some products receive different combinations of TYPES of inspections.
TYPES of inspections (tblInspectionType): mill; welding; assembly; painting; fabrication; etc.
Some job reqs call for all InspectionTypes whereas other job reqs call for only one of the above - and it could be any one. There are "typical" processes and order - but that is far from a hard fast rule. One would assume that "paint" would always be near the end of a linear process - but it isn't in many cases. In fact, sometimes we paint things, for example, that we didn't produce. So it has to be an independent/standalone inspection process.
But all work has a JobNumber. Any given JobNumber can have multiple InspectionEvents (instances of an inspection being performed). And per my above description, there can also be multiple InspectionTypes required.
Here's where the design gets complicated for me.
I have tblMillInspection (workstations/rolling mills) where a given product is manufactured from raw material. Each workstation produces a discrete family-type of product. PartNumbers and Workstations have a discrete/defined relationship. PartA can only be produced on workstation number 3. However, welded assemblies (AssemblyTypes) can be welded on any one of three welders - for the most part. There are a few AssemblyTypes that can only be welded on a single welder.
I have tables: tblWorkstation; tblAssemblyType; tblPartsTypes.
tblWorkstation currently is a Lookup Table for all workstations whether they be rolling mill or welder.
tblPartsTypes has several fields. Two that are relevant to the current conversation are Workstation_FK and PartNumber. Workstation_FK is linked to Workstation_ID in tblWorkstations.
tblAssemblyType is the tough one as I mentioned above. 85% of assemblies can be welded on three different weld lines. The other 15% are weld line specific.
If this weren't the case I am pretty sure I would know how to setup tables and forms InspectionEvent and InspectionType to elegantly handle subforms. But I am unclear how to handle the AssemblyTypes/Workstation/InspectionEvent relationship.
I'm envisioning a main form based on tblInspectionEvent with tabs that house e.g., sfrmJobNumber; sfrmInspectionEvent, etc.
As I'm writing this I'm thinking that maybe sfrmInspectionEvent contains sveral subforms - one for each type of inspection (e.g., sfrmMillInspect; sfrmWeldInspect; sfrmPaintInspect; sfrmMiscFabInspect, etc.)
I'm wondering if I need to break up tblWorkstations into tblMills and tblWeldLines.
And now I'm thinking that frmInspectionEvent is actually the top level form and sfrmJobNumber resides inside it rather than being top level - I am pretty certain of this actually.
The issue that is driving this whole effort (we are using an earlier version of my db already) is that we are beginning to create more advanced queries and my original design was not normalized enough and all inspection types each had tables unconnected to each other in any way.
And now my brain hurts - - - - -
Thoughts?
Thanks!!!!!!!!!!
Tim - and Happy Holidays
The seed of this post is from a different post of mine - on a completely different subject/question. I'm copying and pasting about 10% of the OP in case anybody recognizes text from a previous post.
I want to further normalize the db but am stuck - probably because I am not aware/familiar with available mechanics. I don't know what I don't know.
We manufacture products. Some products are made up of two distinct products welded together BUT the two distinct products that are welded together are also sold as their distinct selves meaning NOT ALL products go through weld/assembly. So we may produce PartA and PartB and weld them together to make PartC or we may just sell them as PartA or as PartB - and they do not have to sell together. All products are inspected by QC. Not all products go through all manufacturing processes which means that depending on the job requirements some products receive different combinations of TYPES of inspections.
TYPES of inspections (tblInspectionType): mill; welding; assembly; painting; fabrication; etc.
Some job reqs call for all InspectionTypes whereas other job reqs call for only one of the above - and it could be any one. There are "typical" processes and order - but that is far from a hard fast rule. One would assume that "paint" would always be near the end of a linear process - but it isn't in many cases. In fact, sometimes we paint things, for example, that we didn't produce. So it has to be an independent/standalone inspection process.
But all work has a JobNumber. Any given JobNumber can have multiple InspectionEvents (instances of an inspection being performed). And per my above description, there can also be multiple InspectionTypes required.
Here's where the design gets complicated for me.
I have tblMillInspection (workstations/rolling mills) where a given product is manufactured from raw material. Each workstation produces a discrete family-type of product. PartNumbers and Workstations have a discrete/defined relationship. PartA can only be produced on workstation number 3. However, welded assemblies (AssemblyTypes) can be welded on any one of three welders - for the most part. There are a few AssemblyTypes that can only be welded on a single welder.
I have tables: tblWorkstation; tblAssemblyType; tblPartsTypes.
tblWorkstation currently is a Lookup Table for all workstations whether they be rolling mill or welder.
tblPartsTypes has several fields. Two that are relevant to the current conversation are Workstation_FK and PartNumber. Workstation_FK is linked to Workstation_ID in tblWorkstations.
tblAssemblyType is the tough one as I mentioned above. 85% of assemblies can be welded on three different weld lines. The other 15% are weld line specific.
If this weren't the case I am pretty sure I would know how to setup tables and forms InspectionEvent and InspectionType to elegantly handle subforms. But I am unclear how to handle the AssemblyTypes/Workstation/InspectionEvent relationship.
I'm envisioning a main form based on tblInspectionEvent with tabs that house e.g., sfrmJobNumber; sfrmInspectionEvent, etc.
As I'm writing this I'm thinking that maybe sfrmInspectionEvent contains sveral subforms - one for each type of inspection (e.g., sfrmMillInspect; sfrmWeldInspect; sfrmPaintInspect; sfrmMiscFabInspect, etc.)
I'm wondering if I need to break up tblWorkstations into tblMills and tblWeldLines.
And now I'm thinking that frmInspectionEvent is actually the top level form and sfrmJobNumber resides inside it rather than being top level - I am pretty certain of this actually.
The issue that is driving this whole effort (we are using an earlier version of my db already) is that we are beginning to create more advanced queries and my original design was not normalized enough and all inspection types each had tables unconnected to each other in any way.
And now my brain hurts - - - - -
Thoughts?
Thanks!!!!!!!!!!
Tim - and Happy Holidays