I'm not sure if I'm overthinking this or not. I've never come across this situation, so I wanted to get opinions on this structure. Our manufacturing facility manufactures several items, on several different production lines (each item is associated with only one production line), each line having particular types of settings (e.g. Sealer Temp, Tooling, etc.). Each item has the settings associated with them (based on Production Line), and then their own specific values. For example, item 100 is run or Production Line 1. Production Line 1 has Sealer Temperature as one of its settings. Item 100 will have a value of 225 degrees for the Sealer Temp setting. The way I set it up, I have a junction table (for Item Settings - acting as a many to many between my products and my line settings) related to another junction table. That's the situation I've never come across before. My structure is below:



I guess another question I have is, when relating tblProductionLine. Right now I have it related to jctProductionLineSettings. Is this the correct way, or do I just link the ProductionLineID from tblProducts to jctProductionLineSettings? Or do I do both?

Sorry, it's been a long week already. I've thought about this design to the point of exhaustion and not sure if I'm just overthinking it.

Thanks for any help!

I get that each production line may be associated with many products, but is it also true that a product may be associated with many production lines? It seems not, based on what you wrote, so I'm not sure a junction table is needed here. Seems to me the Product table may be above the Line/Settings junction table in the hierarchy.

If there is variation in something such as the Sealer Temp from one product to another, it seems to me that temperature may belong in the junction table between lines and products. It is not clear where that setting (e.g. 225°) is stored. If the Sealer Temp could be 200° for a product, where is that setting stored?

Hi Bruce. Sorry, I might have not been as clear as I thought. It all sounds good when you're the author (IMG:style_emoticons/default/notworthy.gif) Anyway, each product is associated with one production line. The first junction table I have is a junction table between my Settings table (any possible setting we can have for any line), and the Production Line (each Production Line can have many settings, and each setting can be associated with many lines). The second junction table I have is between my Products table and the junction table I just mentioned (each item can have many settings - based on line, and each line setting can have many products). The specific value (such as the 225 degrees) is stored in this junction table. I'll try to show what I mean:



Does this make sense? I've attached a diagram. Sorry for the confusion.

If you enforce referential integrity in the table relationships it will be easier to see the direction of the join.

It seems to me that a production line has certain settings (more accurately, perhaps, things that can be set such as Run Speed?). So when you are creating the record about running a product on the production line, you need a listing of the settings available for that production line so that you can apply the actual values needed for the product? In that case it seems you could select from the settings that are available on that production line, which would come from the junction table between settings and production lines.

It seems to me the Product record would have a related table for applicable settings. Now I go back to look...yes, it seems to be there, if I understand the purpose of jctProductSettings.

I am running out of time for today, but it seems to me that jctProductSettings is populated in part by a setting that is applicable to the production line used for that product, and also includes the actual setting (temperature or whatever).

I would think of it like this: The junction table between settings and production lines is a convenience. You could select from a full list of all possible settings, but you don't want the ability to choose settings that don't apply. Work it out like that, then use the settings/production lines junction table in place of the complete settings table.

Hi Bruce. Thanks for the reply. I apologize for the lack of referential integrity. It's actually there, but I'm using a SQL Server Backend, and when I show the relationships in Access they don't show up. Usually the primary keys show, but I'm not quite sure why they didn't.

Your understanding of what I'm trying to accomplish seems to be correct. I believe that from your explanation I have set it up correctly. I'm a little confused by this though:



Are you saying I should completely get rid of the tblSettings and only use the junction table? My first go around with this design actually didn't include tblSettings. Instead, my ProductionLineSettings table was designed like this:



The only reason I changed it was to prevent someone from creating something like "Sealer Temp" for one line, and then "Sealer Temperature" for another line. I really doubt we'll end up having to query based off of the Setting Description, so maybe I was just taking normalization too far. Or maybe I don't fully understand normalization. Usually I understand junction tables very well and many to many relationships very well. I've just never come across the situation where I'd have to link two junction tables to each other like I did in this project.

Well, enough thinking for the day. Thanks for your help!

I didn't put that right. I don't like to talk about the interface ahead of the design, but in terms of the user, it seems to me you would go to a Product record, then create a related record for the actual steps through which the product is processed. To that end, I would have expected there to be a junction table between tblProduct and tblSettings (one product:many settings and one setting:many products). That junction table would be the record source for a subform on the Products form. So I would think the Production Line is an attribute of the Product since there is one Production Line for each product:



For the relationships, one:many from tblProductionLine to tblProducts.

For the junction table between tblProduct and tblSettings:



You could simply select the Setting from tblSettings, except that would require you to keep a lot of information in your head about which setting is available for the ProductionLine on which that product is processed. Instead, you have the junction table between tblProductionLine and tblSettings. The way I would do this is to have a combo box on the subform (which has jctProductSettings as its Record Source). The Row Source for the combo box is a query based on jctProductionLineSettings. It would be something like:

SELECT PLS.SettingID, S.SettingDescription FROM jctProductionLineSettings As JLS INNER JOIN tblSettings As S ON JLS.SettingID = S.SettingID

However, that is all Settings for all Production Lines. So in the After Update event of the control on the main form where you enter the Production Line:



Similar code would go into the main form's Current event, except you would need to test ProductionLineID for a value before running the code (a new record, for instance, would have no value in that field).

The line continuation characters in strRow are optional, as are the aliases (e.g. tblSettings As S), but both make the code easier to read and follow, and the aliases make it easier to type. Instead of aliases you could use the complete table or query names.

Where this differs from your approach is that jctProductSettings is as the name suggests: a junction table between Products and Settings.

That's how it looks from here.

I've changed my relationships to match your description, and coded everything to your recommendation. I used the aliases as you suggested, but I think I found an error (as long as I understand the aliases as much as I think I do).

SELECT PLS.SettingID, S.SettingDescription FROM jctProductionLineSettings As JLS INNER JOIN tblSettings As S ON JLS.SettingID = S.SettingID

I changed the PLS to JLS at the beginning of the statement. I did the same for the after update event.


The best way I could think of to do this was by adding the If statement shown below:


Everything works the way I envisioned. Thanks for taking the time to understand what I was trying to accomplish, and sorry if I caused any confusion.

Thanks again Bruce.

I have no idea where I came up with JLS. I meant to use PLS (short for ProductionLineSettings) in all cases, but as long as it is the same everywhere, it doesn't much matter what it is (although I would guess it is better if it is not an actual table or query name). An alias can be a helpful alternative to typing out the full table or query name each time. I especially like to use them when assembling SQL in VBA, or otherwise facing the need to type it rather than build it in the query design grid. You can also assign aliases in query design view by going to the query properties.


That should work, but I'm not sure you need the new record test. If ProductionLineID is not null, that should be enough.