Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder mOrloff's Avatar
    Join Date
    Nov 2008
    Location
    The Great Pacific NW, USA
    Posts
    422
    Thanks
    8
    Thanked 6 Times in 6 Posts

    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, , 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

    Thanks for looking.
    ~ Mo
    Last edited by mOrloff; 07-02-2011 at 01:25 AM.
    ...because it's dundant already.

  • #2
    Regular Coder mOrloff's Avatar
    Join Date
    Nov 2008
    Location
    The Great Pacific NW, USA
    Posts
    422
    Thanks
    8
    Thanked 6 Times in 6 Posts
    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
    ...because it's dundant already.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,216
    Thanks
    75
    Thanked 4,344 Times in 4,310 Posts
    Many to many table. Period.

    Code:
    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.

  • Users who have thanked Old Pedant for this post:

    mOrloff (07-06-2011)

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,216
    Thanks
    75
    Thanked 4,344 Times in 4,310 Posts
    "Find all the sales groups that have 7 or more companies in common."
    Code:
    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."

    Code:
    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 )

  • #5
    Regular Coder mOrloff's Avatar
    Join Date
    Nov 2008
    Location
    The Great Pacific NW, USA
    Posts
    422
    Thanks
    8
    Thanked 6 Times in 6 Posts
    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?
    Code:
    SELECT CoName, custid
    FROM Customers 
    WHERE CoName LIKE "Comp%"
    AND custid IN (SELECT custid FROM SalesGroupCustomers WHERE sgid = 1)
    Is that Right?

    ~ Mo
    ...because it's dundant already.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,216
    Thanks
    75
    Thanked 4,344 Times in 4,310 Posts
    Yes, although it would possibly be more efficient to make it a JOIN.
    Code:
    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.
    Last edited by Old Pedant; 07-06-2011 at 09:04 PM.

  • #7
    Regular Coder mOrloff's Avatar
    Join Date
    Nov 2008
    Location
    The Great Pacific NW, USA
    Posts
    422
    Thanks
    8
    Thanked 6 Times in 6 Posts
    Awesome!
    Thank you.
    ...because it's dundant already.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •