Database Design: distributed, yet shared, content

I'm looking for suggestions, links to good articles, advice, and anything else that someone thinks might be valuable.
We'd like to have some content that is simultaneously shared and private. I know, :confused::confused::confused:, right?
But, here it is:

We're going to be breaking down a sales force into competing groups (each using their own instance of our the custom Sales/Inventory management system we've developed inhouse).
I've got to figure out how to use a single central datastore for all customer records, yet prevent each group from seeing customers which they didn't find themselves.
IE:
The central DB has

CompanyA
CompanyB
CompanyC
CompanyD
CompanyE

SalesGroup1 identifies companies A, C, and D as potential customers. They add those company names to their instance of the sales program, and bingo, they have access to all the centrally managed contact and address information.
SalesGroup2 identifies companies B, C, and D. They add those company names, and bingo, they have all the contact and address information.

Now, SalesGroups 1 and 2 each share the same contact information for two customers, have sole access to the information for one unique customer, and there is still a customer in the central datastore which hasn't been found by any SalesGroups.

Initially, I thought to just have some salesgroup flags in the central DB for each group, but then the solution needs to be fully scalable.
The flags idea kinda breaks down when the company grows to have 50, or 100, or 200 competing sales groups.

Currently I'm thinking something like this (the concept has some growing up to do):

Central Customers Table

ID__CoName____moreInfo_
01 | CompanyA | blah
02 | CompanyB | dee
03 | CompanyC | daa
04 | CompanyD | blah
05 | CompanyE | blah

Local Customers Table for SalesGroup1

ID__FK__CoName___
01 | 01 | CompanyA
02 | 03 | CompanyC
03 | 04 | CompanyD

Local Customers Table for SalesGroup2

ID__FK__CoName___
01 | 02 | CompanyB
02 | 03 | CompanyC
03 | 04 | CompanyD


This, unfortunately, would require 2 queries to get the contact info every time someone in one of the groups does anything with a customer record (whether its simply pulling up their contact info, or creating a sales order, or building a quote, etc...), but that might be better than having redundant info stored over and over and over.

I'm actively looking for well rounded and well balanced solutions, so links, suggestions, advise, related experiences would be appreciated in any way/shape/form :D

Thanks for looking.
~ Mo

Another idea is to simplify the SalesGroups tables into simple white-lists with one column: CompanyID.

In that case, the queries could get submitted directly to the Central DataStore, and then the results could get filtered according to the white-lists.
This seems a little easier to manage.

Any thoughts??
~ Mo

Many to many table. Period.


Central Customers Table

* custID__CoName____moreInfo_
* 1 | CompanyA | blah
* 2 | CompanyB | dee
* 3 | CompanyC | daa
* 4 | CompanyD | blah
* 5 | CompanyE | blah

SalesGroup table

sgid | groupname | etc.
1 | andy's androids
2 | bob's braintrust

SalesGroupCustomers

sgid | custid
1 | 1
1 | 3
1 | 4
2 | 2
2 | 3
2 | 4


No choice. Only thing that works well. Only thing that allows you to make queries such as this:

"Find all the sales groups that have 7 or more companies in common."

"Find all the companies that have been claimed by all but one of the sales groups."

and so on.

"Find all the sales groups that have 7 or more companies in common."

SELECT sgc1.sgid, sgc2.sgid, COUNT(*) AS incommon
FROM SalesGroupCustomers AS sgc1, SalesGroupCustomers AS sgc2
WHERE sgc1.custid = sgc2.custid
AND sgc1.sgid < sgc2.sgid
GROUP BY sgc1.sgid, sgc2.sgid
HAVING COUNT(*) >= 7
ORDER BY sgc1.sgid, sgc2.sqid


"Find all the companies that have been claimed by all but one of the sales groups."


SELECT c.CoName, c.custid, COUNT(*) AS claims
FROM Customers AS C, SalesGroupCustomers AS SGC
WHERE C.custid = SGC.custid
GROUP BY c.CoName, c.custid
HAVING 1 + COUNT(*) = ( SELECT COUNT(*) FROM Sales )

Fantastic!
And then, when somebody from SalesGroup1 searches for all company records where the complany name starts with "comp", we'd basically do a query like this?

SELECT CoName, custid
FROM Customers
WHERE CoName LIKE "Comp%"
AND custid IN (SELECT custid FROM SalesGroupCustomers WHERE sgid = 1)

Is that Right?

~ Mo

Yes, although it would possibly be more efficient to make it a JOIN.

SELECT C.CoName, C.custid
FROM Customers AS C, SalesGroupCustomers AS SGC
WHERE CoName LIKE 'Comp%'
AND C.custid = SGC.custid
AND SGC.sgid = 1

That's where proper use of indexes and a little investigation using EXPLAIN will help you decide best course of action.

Awesome!
Thank you.