G
Guest
I need a many to many intermediate table, but not sure of the best keys to use.
I have three tables
Customers - (PK)CustomerID
Business Details - (PK)BusinessID, (FK)CustomerID
Proposals (Orders) - (PK)ProposalID, (FK)CustomerID, (FK)BusinessID
Each have Auto ID's
Some Customers have more than one business address.
In my Proposals table I have a lookup columns for the BusinessID and CustomerID
Maybe my Proposal's table acts as a kind of Intermediate table. However in my proposal table, I want to filter the Lookup of the BusinessID (FK) based on the CustomerID (FK) I select. (So there's no chance to put in an address belonging to a different customer)
To get round this I thought of making another table which could be used as a subform (A bit like OrderID(FK), DescID(FK) and QTY on an Order details table) which would be used to connect business' with customer's on the proposal form.
But I have three Keys to manage here and not two (ProposalID, CustomerID, BusinessID). Which ones would I use as the selection? - If I use BusinessID as my selection and my FK's to be CustomerID from the Proposal table and ProposalID from the Proposal table, it may be ambiguous. This also seems to be long winded.
What's the best solution with regards to selecting a customer and the selecting a various business they may have (remembering some customers have two or more!)
I have three tables
Customers - (PK)CustomerID
Business Details - (PK)BusinessID, (FK)CustomerID
Proposals (Orders) - (PK)ProposalID, (FK)CustomerID, (FK)BusinessID
Each have Auto ID's
Some Customers have more than one business address.
In my Proposals table I have a lookup columns for the BusinessID and CustomerID
Maybe my Proposal's table acts as a kind of Intermediate table. However in my proposal table, I want to filter the Lookup of the BusinessID (FK) based on the CustomerID (FK) I select. (So there's no chance to put in an address belonging to a different customer)
To get round this I thought of making another table which could be used as a subform (A bit like OrderID(FK), DescID(FK) and QTY on an Order details table) which would be used to connect business' with customer's on the proposal form.
But I have three Keys to manage here and not two (ProposalID, CustomerID, BusinessID). Which ones would I use as the selection? - If I use BusinessID as my selection and my FK's to be CustomerID from the Proposal table and ProposalID from the Proposal table, it may be ambiguous. This also seems to be long winded.
What's the best solution with regards to selecting a customer and the selecting a various business they may have (remembering some customers have two or more!)