Nested junction tables

[Kirk]

I'm at a place in my design where I don't want to go further without advice for fear of really making a mess.

I have Products, Customers, Customer Locations, and Customer Models that can have any number of combinations. I've joined what I think makes sense at the lowest level, customers with locations, and next model with customers/locations.

I think products is the outer most junction, because many customers can use the same product in any number of models and locations.

The attached diagram shows where I am now--multiple junction tables, and junctions of junction tables. Now each of these tables has other relationships to nomalize things, but those are not shown.

Is one more level, or nest if you will, the right way to do this?

 

[Kirk]

Yes, of course, sorry. Let me give it another shot.

I have customers (groups in this sense, not individuals) who have the same parent company, but they are unique customers who invoice seperately, different contacts, basically different customers.

Each customer has its own locations (factories), plus since they are related in the parent company, they share a couple of locations.

Each customer has a unique set of vehicle model names. These models are built in the customer specific locations and the shared locations.

Products are supplied to the customers, and the products can be assigned to models. One product number can be used on multiple models and multiple customers.

The given information are the customer to model to location assignments, and product to model assignments. I need to relate all these so I can query any number of criteria and return a result. There are detailed criteria that I will query that are not shown in the tables attached, but are related to the entity.

I've gotten as far as shown (actually much farther counting all the forms and search code), and it works perfectly until I try to find product by location. Access returns the "ambiguous outer join" error on any combination of query or query within a query that I build. There is no direct line from products to location because location is driven by model.

I know long posts don't seem to get much attention but this is the most concise description I can give. The question really is, is my table structure correct for locations related to products?

 

[Simon_MT]

It would suggest that the Model is Unique but in shared locations? These affiliates share other affiliated factories? Are there a crossover of Models? When Products are supplied how do you know which location to delivery to?

I would look at tailoring the Locations so that they are unique and reflect the Affiliate and Location. This table represented Location + Customer.

I understand these entities

A Customer has a Models : CustomerModels representing Customer + Model
A Model Uses Products : CustomerProducts representing Model + Product

What would be helpfull is clarification of the allocation of Models within Location.

Simon

 

[Kirk]

It would suggest that the Model is Unique but in shared locations?

Yes, the model is unique to the customer and built in several locations.

These affiliates share other affiliated factories?

Yes

Are there a crossover of Models?

No, if you mean customers share model numbers.

When Products are supplied how do you know which location to delivery to?

No problem, they order from each location by customer.

What would be helpfull is clarification of the allocation of Models within Location.

Ok I'm not sure how to anwer this one, maybe with an example? Please forgive the pseudonyms; an ultra-strict confidentiality agreement at work here...
Customer Alpha at location Mt. Hood builds...
Model Gemini
Model Mercury

Customer Bravo at location Mt. Hood builds...
Model Sputnik

Gemini and Mercury are built in other uniquely customer Alpha locations too.
Sputnik is built in other unique customer Bravo locations as well.

 

[Simon_MT]

So:

Alpha Builds Gemini Mercury at several locations
At Location Mt Hood Alpha Build Gemini and Mercury and Bravo Sputnik in effect you have at the lowest level:

Customer
Location
Model
Product

The model could also have the Customer.

The ambiguity occurs when you have when there is a mismatch in the joins i.e. Left Join followed by a Inner Join. Alternatively, start at the bottom and work upwards, you will only need Inner Joins.

Simon

Simon

 

[Kirk]

Hmm...So if I undertand correctly the process would be to assign a Model to a Location, the assign the Model-Location to a Customer. I could then assign the whole lot to a Product? I think product could be first or last in the process because it has the one to many relationship with everything else.

 

[Kirk]

The attached shows what I understand you are saying, Simon.

Option A seems to be the easiest, but option B would make the combo box selections cleaner when relating products to Models, et al. It looks like I would have one record for each model/customer/location combination in option B. However this also means that I would have to have model/customer/location defined completely when I related the product, not just the customer, for example.

It also looks like SQL will like this setup.

Thanks for all your help, by the way!

 

[Simon_MT]

All I'm suggesting is that as the Model is Customer specific then Customer could go onto
TruckModels.

You example on the lastest pdf means that ModelLocationCustomer joins are all Inner Joins so you will have no ambiguity.

To get Reports at at Company Model location can be handled with Grouping and Totalling with Queries or Reports rather than the data strucuture.

Simon