Relate one field to multiple fields (1 Viewer)

Hi all -

We make products. We make 50 products.

Most of the products (assemblies) are created by attaching a top part to a bottom part. Two parts make one assembly - usually.

Very often, a part that acts as a bottom part is used with a variety of different tops resulting in multiple unique assemblies.

Sometimes, a single Part_ID could be used for both the top and the bottom of a unique Assembly_ID.

Some of the products we sell are just a top or bottom piece as a standalone part.

We have over 100 parts.

I have tblAssemblies. This table also has a PK named "Assembly_ID" which is an autonumber. Among other fields, tblAssemblies has field "Top" and field "Bottom" (i.e., component1 and component2 of the assembly).

tblParts also has key field: Part_ID. Again, assemblies take 2 parts from tblParts.

I am not keeping inventories or any such calculating. These tables exist primarily as lookup tables and always will. We have an ERP that handles everything else on a much larger scale.

I want to relate parts to assemblies. I know I could put a second instance of tblParts in the Relationship diagram and link Part_ID from tblParts to both "Top" and "Bottom" fields in tblAssemblies but I suspect there is a more elegant way to do this.

I wonder if a junction table is something I should be considering or BOM or Order Taking schema maybe fit the bill.

Thoughts?

Thanks,

Tim

Is it actually important to identify 'top' and 'bottom' in your product data? How do you currently store the data for products with any number of components other than 2, or with an assembly that's something other than top and bottom?

If Top and Bottom have to be tracked, then use them as fields in your product table (but find a way to modify them, since Top is actually a reserved word), and join them to your parts tables. In situations where there is one but not the other, just allow that one to be null. This is actually a normalization violation, but as long as you have these two locations and ONLY these two locations, it's not that big a deal.

Otherwise, you're definitely looking at a junction table. You would need Products, Parts, and ProductComponents (the junction). The PC table would have the product ID, the Part ID, and a field for top/bottom. This is the normalized answer, and it's far more flexible in the long run, but it's also more work to set up.

Frothingslosh said:

Is it actually important to identify 'top' and 'bottom' in your product data? How do you currently store the data for products with any number of components other than 2, or with an assembly that's something other than top and bottom?

Click to expand...

There are never assemblies with more than two parts.

Frothingslosh said:

If Top and Bottom have to be tracked, then use them as fields in your product table (but find a way to modify them, since Top is actually a reserved word), and join them to your parts tables. In situations where there is one but not the other, just allow that one to be null. This is actually a normalization violation, but as long as you have these two locations and ONLY these two locations, it's not that big a deal.

Click to expand...

I don't actually use "top" and "bottom." I actually use the terms "Hat" and "Plate" but thought top and bottom would be easier to visualize in a post.

With that in mind, we produce the hats and plates in-house also. So I get some sense that I do need to relate components to assemblies - but yes - I've asked myself the same question of "how vital is it to relate them to each other" when they could also be tracked and recorded as simple stepped stages of an order creating process. The only thing is that the "fit" of the two pieces together is often a critical feature for inspection and typically has unique dimensional data that requires recording that only exists when the hat and plate are fit together. I don't know if I actually need to represent that relationship in the db or not but intuition leans me towards "yes."

Frothingslosh said:

Otherwise, you're definitely looking at a junction table. You would need Products, Parts, and ProductComponents (the junction). The PC table would have the product ID, the Part ID, and a field for top/bottom. This is the normalized answer, and it's far more flexible in the long run, but it's also more work to set up.

Click to expand...

With what smattering I know about junction tables - I am pretty sure that is where I am headed. The whole point of this is to record inspection data throughout the manufacturing process.

What I was functionally hoping to accomplish is this: Every Friday our scheduler puts out the schedule for the following week. This is an Excel worksheet.

That sheet has the following column headings: StartDate, JobNumber, AssemblyNumber, WorkStationName, HatGauge, PlateGauge, Quantity, and Notes.

I've envisioned workflow being that every Friday, I input his Excel spreadsheet into the db and have an initial InspectionEvent be recorded in what more and more looks like a junction table. Then the following week when I do the actual inspection the majority of the top-level data is recorded already and I am only storing an ID value with the inspection record itself. But I'm pretty sure this would take a relationship between assemblies and their related parts. If I go this route, I suppose I would have a junction table (tblProductComponents) that would have a field named "Hat" and a field named "Bottom." How those fields are related to Assembly_ID is the big question to me.

Second goal is reporting. I want to relate all parts processes and assembly processes on a WIP report as well as a final job report. I suppose I could just relate everything to JobNumber in tblJobs but......

Third Goal (probably the most important one): It would be really, really great to query/lookup/report an AssemblyNumber and have the associated hat and plate Part_IDs automatically populate their respective fields in a report. Otherwise given our somewhat arcane Part naming conventions I have to go look through a variety of other sources.

Do you have anymore insight into this?

Thank You,

Tim

Here is a partial db of where I am headed. tblParts and tblAssemblies are partially populated.

The Hat_FK and Plate_FK fields in tblAssemblies are the correct and corresponding ID numbers to relate to tblParts although. I've created a second instance of tblParts in the Relationship window to show how I am envisioning of the the relation of Parts to Assemblies.

I've started tblProductComponents. But I'm not sure of the schema logic even though your advice above would appear clear. Not to make excuses but I have a cold and should probably actually be in bed.

Anyway - what I would really like to see is that I can have tblParts as a subdatasheet to tblAssemblies so that when I open tblAssemblies and click the subdatasheet plus sign both components (Hat and Plate) show up as such in the subdatasheet. That is the best I can explain it I suppose. And for some reason I can't seem to get there.

Thank You,

Tim

It will have to wait until after work to look at the database - I just have a couple minutes while a query runs.

For a junction table, you have a compound primary key that is comprised of foreign keys to matching primaries on different tables.

As an example, a 'ProductComponents' table would include the primary key from your Products table and the primary key from your Components table, and BOTH fields would be used to create a single compound index, either as the primary key or just as a unique index (if you'd rather use an Autonumber ID field as a surrogate primary key). Then you can add other fields, such as an indicator for whether a part is a Hat, a Plate, or neither, and any other data that's unique to that specific component/product pairing.

That gets you what is basically a component list for each product. If you have to keep track of inspections, then that's ANOTHER table. You would need to link it to the inspector, the product, the product components (each would end up its own record), and inspection results for that part, as well as some way of identifying what got inspected (such as a serial number or something - however you guys track it).

Make sense?

Frothingslosh said:

Make sense?

Click to expand...

Much more sense than earlier. Much clearer and more to work with. Thanks!

Yeah - I already assumed I would have another junction table for inspections.

I'll read up on junction tables tonight while I have some chicken soup and garlic for this darned cold.

I'll post the results tomorrow.

Thanks again,

Tim

Zydeceltico

In your first post, you said some Parts could be Hat or Plate. I'd add a field to your tblParts to indicate that ie Hat, Plate or both. That way in an Assembly form, you can used filtered combos to select the Hat and Plate parts.

I notice in your tblAssemblies there are duplicate Hat/Plate combinations eg PartIDs 38 and 115 for AsssemblyIDs 5 and 6. Maybe this is test data but if not, the implication there is some other data field that determines a unique assembly.

Just a technical comment: Having a situation where one (but not both) of two pointers is sometimes null is of no consequence to normalization unless there is a one-to-one relationship. As noted by the original question, these would be one-to-many relationships, and having a null pointer is thus not a violation. You would still have an assembly ID and at least one component ID.

The greater theoretical normalization problem would have been if there were some assemblies with THREE parts (or more). Then you are heading down the slippery slope to create a repeating group. The other issue is as noted by Cronk - if there is in fact a chance that the same part could, for different assemblies, be either a hat OR a plate, then you need a parent/child "component" table where one of the fields in the child table identifies the role that it plays.

@Tim,

Could you describe in non-database terms the manufacture or creation of a Product ( or a few Products) showing how Hat, Plate, Part and Assembly relate to one another?

Cronk said:

I notice in your tblAssemblies there are duplicate Hat/Plate combinations eg PartIDs 38 and 115 for AsssemblyIDs 5 and 6. Maybe this is test data but if not, the implication there is some other data field that determines a unique assembly.

Click to expand...

Thanks for finding that! Yeah - that is a lack of thorough scrubbing before I attached the file. This db is not "active" yet obviously. All combinations should indeed be unique to the point where - if I wanted - I could make 2 parts act as a composite key. That composite key would reflect/link to a unique Assembly number.

Each Part has a unique ID and each assembly of two parts has a unique ID.

The_Doc_Man said:

The other issue is as noted by Cronk - if there is in fact a chance that the same part could, for different assemblies, be either a hat OR a plate, then you need a parent/child "component" table where one of the fields in the child table identifies the role that it plays.

Click to expand...

I spoke with engineering about this. The shop tends to use colloquial terms/names for products. Engineering informs me that they are able to supply unique identifiers for parts that could possibly act as a hat OR a plate. In other words, I misspoke when I said above that a specific sheet could act as both a hat or a plate.

I'll be re-tagging my tblParts to reflect a unique identifier for every part - adding additional records for those parts that can act as either. Those new records will have unique IDs and names.

jdraw said:

@Tim,

Could you describe in non-database terms the manufacture or creation of a Product ( or a few Products) showing how Hat, Plate, Part and Assembly relate to one another?

Click to expand...

Absolutely.

Our company is in the steel rolling business. The company started years ago making what is called B deck which is typically used in commercial construction to support concrete floors and roofs.

Sometime ago, our company decided to take it further and make fancy B Deck by also rolling a plate that is attached to the bottom of the B deck. The idea is that when the B Deck is installed to pour a concrete floor above the customer gets a finished ceiling below at the same time.

So, we take coils of steel and roll them into 20 -40 ft. long profiles. There are many profiles. This is what I am calling Parts.

Then we take different profiles (Parts), lay one on top of the other and weld, rivet, or screw them to each other. This is what I am calling an Assembly.

We sell both assemblies AND parts to the customer. In other words a "final product" could be a truckload of assemblies or it could a truckload of parts. It just depends. 70% annually are assemblies.

Every Part has a few critical features unique to that part that must be inspected regardless of whether or not that part becomes half of an assembly.

When a Part is required to become half of an Assembly, there are additional critical features that will require attention and inspection. We call the Part that becomes the upper half of an Assembly the Hat. We call the Part that becomes the lower half of an Assembly the Plate.

Does that make it clearer?

Thanks,

Tim

Re #10


You already have a unique identifier for each part/assembly by way of the key field in each table. You could also set a unique index on the PartType and AssemblyType names.


Also make for a unique combination of hat/plate combinations by setting a unique composite index as per attachment.

There are never assemblies with more than two parts.

Click to expand...

Building a structure that supports only assemblies with two parts is very shortsighted. There is no reason to do it. It doesn't simplify anything and should your business change, it could be an enormously expensive mistake.

Three tables:
tblParts (all detail parts)
PartID
part detail fields

tblEndItems (parts that you sell)
AssemblyID
assembly detail fields

tblAssemblyItems
AssemblyID
PartID
Type (if you want to label them Hat, Plate, some future name)

tblAssemblyItems has one row for each part of an assembly. If you sell detail parts, they become assemblies with only one part.

Pat Hartman said:

Building a structure that supports only assemblies with two parts is very shortsighted. There is no reason to do it. It doesn't simplify anything and should your business change, it could be an enormously expensive mistake.

Three tables:
tblParts (all detail parts)
PartID
part detail fields

tblEndItems (parts that you sell)
AssemblyID
assembly detail fields

tblAssemblyItems
AssemblyID
PartID
Type (if you want to label them Hat, Plate, some future name)

tblAssemblyItems has one row for each part of an assembly. If you sell detail parts, they become assemblies with only one part.

Click to expand...

I've attached the layout you suggested above. For the most it works as I had hoped - meaning that I am able to clearly see the relationship between an Assembly and its two component parts. I also understand how this design allows for maximum flexibility should we ever have an Assembly with more than two component parts.

Thank You!

One new issue, is that tblParts has Part_IDs in it that do not exist in tblAssemblyItems. Because of this I cannot enforce referential integrity between the two tables. I should note that some of the Part_IDs in tblParts will be of the type that.....

I just reread your post and I am now writing for the benefit of some future reader (I hope) - - - I need to make sure an entry exists in tblAssemblyItems for every single Part_ID in tblParts and then I'll be to enforce referential integrity.

I see scrubbing in my near future.

Thanks,

Tim

Zydeceltico said:

One new issue, is that tblParts has Part_IDs in it that do not exist in tblAssemblyItems. Because of this I cannot enforce referential integrity between the two tables. I should note that some of the Part_IDs in tblParts will be of the type that.....

Click to expand...

Sure you can. One-to-Many joins do not require every entry in the One side to exist in the Many side. In your case, it's perfectly acceptable to have parts in your Part table that do not appear in your junction (Assembly Items) table.

The inverse, however, is not true: as the Many side of the join, every entry in tblAssemblyItems must have a matching PartID in tblParts.

As an example, a couple years back, another poster requested help designing a database where he had to track clients, documents, and which documents were on file for each client.

The solution was much the same: a clients table, a documents table, and a documents log (the junction table).

I uploaded a sample database complete with clients, documents, and a log of documents on file for each client. The database had relationships assigned via the Relationships editor, and most of the documents I put into the docs table weren't assigned. The database and tables still worked just fine.

If you want, I can upload a copy of it to this thread for you to look at - I just noticed a week ago that it's still sitting in my Repository folder on my PC at home.

Frothingslosh said:

Sure you can. One-to-Many joins do not require every entry in the One side to exist in the Many side. In your case, it's perfectly acceptable to have parts in your Part table that do not appear in your junction (Assembly Items) table.

The inverse, however, is not true: as the Many side of the join, every entry in tblAssemblyItems must have a matching PartID in tblParts.

Click to expand...

I thought so too - - but I've checked for incorrect or repeated IDs in tblParts and don't see any so I'm not sure what's going on.

Please tell us more.
Consider, you have a bin of Parts, some of the Parts are not "part" of an Assembly (pardon the pun), but such Part(s) could very well be in a new Assembly. I don't see this as an issue, but maybe I'm missing some business fact(s) that would make this a problem.

I do see 1 part in tblAssemblyItems that is NOT in your tblParts. That is an issue.

Try this query

I get this ???

jdraw said:

Please tell us more.
Consider, you have a bin of Parts, some of the Parts are not "part" of an Assembly (pardon the pun), but such Part(s) could very well be in a new Assembly. I don't see this as an issue, but maybe I'm missing some business fact(s) that would make this a problem.

I do see 1 part in tblAssemblyItems that is NOT in your tblParts. That is an issue.

Try this query

Code:Copy to clipboard

SELECT tblAssemblyItems.AssemblyID, tblAssemblyItems.Part_ID, tblParts.PartType
FROM tblParts RIGHT JOIN tblAssemblyItems ON tblParts.Part_ID = tblAssemblyItems.Part_ID
WHERE (((tblParts.PartType) Is Null));

I get this ???

Code:Copy to clipboard

AssemblyID	Part_ID	PartType
             28	     141

Click to expand...

JDraw - that's why you guys get paid the big bucks! That was the problem. "141" should have been "14."

If you would could you give me a quick "rundown" of that SQL query - purpose? I suspect that it could become my new best friend.

Thank You