Will this Database design logic work and what should be used? A few weeks ago I had a client of mine ask me about how complex it would be to develop a custom CRM application similar to the one being provided by highrisehq.com

What he essentially wants to do is provide external sales reps within a specific industry access to use this CRM application for managing all of their contacts, clients and communication/tasks.

The key difference between highrisehq and this system is that he wants to allow each of these sales reps the ability of "sharing" data with one another. Now what I mean by this is the following:

Lets assume there were two separate CRM account users within the system, each of these accounts have thousands of contacts and hundreds of relationships (linking companies to contacts, contacts/companies to projects, and contacts to messages/tasks/notifications).

Now... what he wants to do is allow both of these sales reps each with their own  independent accounts to be able to share information with one another.

My thought was that the first thing which should happen here is a "share" request be initiated through the system by one user to the other user. Upon this share request being accepted both accounts now have the "ability" of sharing info with either other.

My thought was that once these accounts are linked that user can now show up as a "sharing user" and show up in all areas where you would normally set the view/edit permissions for individual users.

Where things get complicated in my view is how to show the "activity stream" for example because essentially now the system is not just showing all the activity for my account but also all the activity for external accounts for which the user has been granted access.

Furthermore... if a user has lets say 100 accounts he is sharing information with is there anything I can do to ensure the system does not slow to a crawl?

What is the most logical, best and least expensive db logic which should be used to create these relationships so a query on a single account returns data at the same speed as when having to access data from multiple accounts?

Any other key aspects which you guys feel should be considered or are "must have" features?
I really have to encourage you to look at the way DB schemas are handled by existing CRM systems and consider whether (1) you wouldn't be further off customizing one of these systems and (2) you're not biting off more than you can chew.

Most CRM systems I've seen have extensive customization capabilities built into them.  Typically, this includes the ability to extend and/or modify the definitions of the built-in entities and the ability to add new entity types and relationships.  Many of these CRM systems also have the ability to set up some pretty funky security setups, and some might be able to do something similar to what you're suggesting.

I'd also encourage you to try to pry a business scenario out of this client, as well.  What's got him convinced that sales reps are going to share information?  If they're working together on a team, then existing CRM systems have this covered already.  If not, then I'd be a little suspicious about the premise of information sharing.  Understand that sales reps are going to follow the money with respect to their behavior.  If they're paid to share information, then it'll happen; otherwise, you're going to have to beat them with a stick to share anything.