Normalizing a Manufacturing DB (1 Viewer)

Zydeceltico

Registered User.
Local time
Today, 11: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
 

Attachments

  • QCDB - Redesign - Rev1 - 20181220.zip
    149.5 KB · Views: 165

plog

Banishment Pending
Local time
Today, 10:17
Joined
May 11, 2011
Messages
11,611
First and foremost, stop thinking about forms. It's a common rookie mistake to think they drive everything and build everything with them in mind. Data is what you need to focus on first. After that you focus on Reports--because if you can't get data out like you need, what's the point in slick jazzy forms that help you throw data onto a useless memory heap?

Next, I don't know if we can specifically help you; but we can generally help you. I mean, that thing has 22 tables, over 100 total fields--no way I can get my mind not only around that but the actual process it is intended to track and marry the two. So here's how I would eat this elephant:

Start fresh. Open up this database on one monitor and a blank database on another. Then follow the below steps for every single table you have. The first table should be the "main" table. That's subjective, but it should be a table that will sit near the middle of the Relationships Tool (tblMillInspection seems like a good candidate).

A. Copy a table to the new database.

B. Add it to the relationship tool. Link it appropriately to whatever table it should connect to.

C. Validate the fields #1. Make sure the fields it has belong to it. Often a field is put in the wrong spot. Make sure it belongs where its at and not a connecting table. Also make sure its not duplicated somewhere else. If so, you need to remove it from somewhere.

D. Validate the fields #2. Open the table in design view and make sure the fields are appropriate for the data they contain. For example, dDate and tTime in tblMillInspection is incorrect. Its called a Date/Time field for a reason, that type fo field can hold both the date and time. Eliminate one of those fields and put the entire value in just 1 field. Also, you have PlantName in there--but you have a table for Plant names, that means you need a foreign key instead of the entire name. I see lots of fields that should be modified just in this table.

E. Make notes. If you are uncertain a field or table belongs where you put it, write yourself a note. Write where you think it might be a better fit, write examples of how some data could give that field/relationship problems.

F. Test queires. You know what "should" happen with your data, so write some queries to test things out. After you add a table, write a query to test to make sure it is behaving like you expect. Also, review your notes and see if you can make queires to resolve any questions or concerns you have written down.



That's how I would tackle this. And when you have questions about a specific table, post back here along with a screenshot of your relationship tool and your thinking with whatever table you are trying to add.
 

Zydeceltico

Registered User.
Local time
Today, 11:17
Joined
Dec 5, 2017
Messages
843
Thanks Plog. Likely the clearest advice I’ve ever received. Will do. Thank you
 

Users who are viewing this thread

Top Bottom