have a look at intersection tables
essentailly a sub table that has a foreign key to both the provider and customer tables
as to the primary key in the intersection table thats up to you.. normally it would be a composite key made up of the two foreign keys (Provider ID and Customer ID)
however in this case you may choose to be a bit more creative
Id suggest you add a third column (say Provider Index, limit it to non null, and in range 1..5, that makes sure you can never have more than 5 providers associated with anyone customer)
make the customer and provider index as the primary key.. this also allows you to infer preferred suppliers (but I'd be careful of that approach it could trip you up (especially if say provider 1 is originally the preferred supplier and gets demoted.. its a pain to change keys, where as an alternative column (say supplier preference order would design out that issue))
add a unique index on supplier and provider.. that stops a user associating the same provider and supplier, so you know the same provider cannot be associated with the same supplier more than once
then when you come to doing your reporting
you extract all suppliers with the specified provider ID (doing a join on the intersection table, pulling in any provider and customer details as required).
Last edited by healdem; 08-12-08 at 12:58.
I'd rather be riding on the Tiger 800 or the Norton