multiple level relationships

[fattires]

Hello,

I am having a table design problem that I cannot find a solution for online or in texts. Lost, in a word.



(PK=primary key, FK=foreign key, and one to ---> many relationship)

Table structure:
tblCustomers
CustomerID, autonum, PK
CustomerName, text

tblCustomerLocation
CustomerLocationID, autonum, PK
CustomerID, integer, FK
LocationName, txt

tblModels
ModelID, autonum, PK
CustomerID, integer, FK
Model


tblProducts
ProductID, autonum, PK
CustomerID, integer, FK
PartNumber, text


Relationships:
tblProducts.CustomerID ---> tblCustomers.CustomerID
tblCustomers.CustomerID ---> tblModels.CustomerID
tblCustomers.CustomerID ---> tblLocations.CustomerID


These relationships are valid and required in one way or another, but what I cannot do with them is relate Products to multiple Models, or Models to multiple Locations. If I had this then I think I could also relate Products to multiple Customers and Locations.


The current set up works at a low level but I am missing something fundamental that I cannot figure out to make the more complex relationships work. There are many more fields in the tables but I just posted the pertinent ones.


Thanks!

 

[fattires]

what about this

Two espressos now and this is what I have...

So if I create a new table, tblCustomerLocationModel, with the appropriate foreign keys, and point the individual tables to it, would this be a valid design?

tblCustomers.CustomerID ---> tblCustomerLocationModel.CustomerID
tblLocations.LocationID ---> tblCustomerLocationModel.LocationID
tblModels.ModelID ---> tblCustomerLocationModel.ModelID

From this I should be able to see models by location sorted by customer.
I also think the relationships are pointing in the right direction, but I don't think SQL will like the joins...I'll have to try it. The thing is I don't have products in there.

More caffeine...