View Full Version : Database Design: distributed, yet shared, content

07-02-2011, 02:13 AM
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.
The central DB has


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

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

Local Customers Table for SalesGroup1

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

Local Customers Table for SalesGroup2

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

07-05-2011, 09:32 PM
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

Old Pedant
07-06-2011, 12:31 AM
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


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.

Old Pedant
07-06-2011, 12:40 AM
"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
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

07-06-2011, 06:15 PM
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

Old Pedant
07-06-2011, 10:02 PM
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.

07-06-2011, 10:16 PM
Thank you.