Relating Suppliers to Customers through contracts

[CDM]

I have a table design problem regarding contracts that I'm trying to track.
Each contract may have multiple suppliers and multiple customers. Thus, I've
set up a table for suppliers, customers and contracts. I am supposed to send
a notice to each customer listing the suppliers on the contract. Throughout
the year it is common to have the same suppliers and customers as parties to
mulitple contracts. As you can see, it is the contract that links the parties
together.
Is this a simple one-to-many between tblContracts and tblCustomers and
another one-to-many between tblContracts and tblSuppliers, or am I looking at
creating a couple of many-to-many tables?

 

[Allen Browne]

What you have is fine, but it might be simpler if you combined the customers
and suppliers into one table of 'clients.'

You could then create a table with fields:
ContractID relates to the p.k. of your contract table.
ClientID relates to the p.k. of your client table.
RoleID relates to the p.k. of a role table.

The little Role table would have 2 records, for Customer and Supplier. As
you can see, you can now identify multiple companies with one contract,
specifying them as customers or suppliers in one table. Further, when
someone else dreams up another way that clients could be related to the
contract, you can do that just by adding other roles.

 

[CDM]

If I set up a role table, when notifying customers would I create a recordset
on clients/contracts with a where clause for role=customer and loop through
another recordset creating notices for related client/contracts whose role
was supplier?

 

[Allen Browne]

You could do that if you want 2 separate lists.

Or you could combine them into one list if that suits your postage costs
better.

 

[CDM]

Many thanks!

You could do that if you want 2 separate lists.

Or you could combine them into one list if that suits your postage costs
better.