We are designing a product that, as per the client's request, must utilize a database-per-company design approach with one shared database holding common data.

We are using SQL Server 2012 for the job.

One of the features is a news feed with an ability to post messages, comments, likes etc.

The news feed also has the ability to post to "community." These "community" posts should be visible to all the companies. We will have to store those posts at the common level.

My question is - how would you go about designing this?

I was thinking of having duplicate tables in the common database and each of the company databases and just joining the tables for the company feed that appears in the UI.

This brings up another question; how do we manage user IDs for example? Each post or comment is associated with a user ID. Would we have to make user IDs unique for all the databases to do that?

Is there a better way to design this?


4 Answers 4


I don't know if this will help, and this maybe a little much for your situation, but here goes our solution in use today.

All users are grouped into logical entities under a domain (something.com) umbrella. Our particular scenario required an additional layer of Domain->Company->Group->User break out. Not sure if you ever worked with Active Directory or domain trees, but it follows that logic.

Using the domain model, the server itself is at the top, presumably the client of your services would be the administrator. Each company flows like a branch from the server itself. Then flows into user-defined groups which contain users. Each company in this scenario would have a Domain Admin which can administer the xyz.com, abc.com, etc domains, but can't access any other domain.

Each of the service containers (the databases) use a full trust model with the security database to provide services to the users contained in this security database (Think OpenID for perspective). We use a home brewed C++ compiled module for Apache 2.x to provide an application firewall and session security host.

Each run, the module "asks" the security database to record the page hit, produce a random (64 character string) cookie and a session cookie (two total cookies), and authenticate the session. A new session or the same session is returned based on if the cookies match. The session key and anything else relevant is provided to the end-user UI application code over HTTP headers (so the firewalls could sit in front of Google Apps or AppEngine).

Once the UI has the code, the UI can act on behalf of the user and the service database accepts this token and provides direct permissions to the user based on the token. Our application provides the username as well to match a unique user within the service database to provide extended permissions. Since we also use per-transaction random keys, the state is unable to be cached.

This model supports isolation (UI developers are unable to eavesdrop), allows a central security with a delegation aspect, and the ability to provide centralized services. Such as providing a central forum, trending (statistical compiled) data stored centrally (which maybe restricted to paying clients), or maybe even weblogs as we provide to each of our service databases to make decisions to gauge a fraudulent checkout request.

    SERVERS TABLE Minimum of id (unique key), domain, adminuser
                | -------> OPTIONAL Access Control List
    SERVER USER CONTROL LIST Minimum of id (unique key), serverid, actual url you wish to protect. 
                OPTIONAL restrictions on AdminOnly, NoSearchEngines, Restricted (Authentication Required)
                | --------> OPTIONAL ENTITY (Sub-Company) Abstraction
                | --------> OPTIONAL GROUPS
    USERS   Minimum of id (unique key), username, serverid (unless you use the entity container which are already tied to the server)
    PERMISSIONS Minimum of id (unique key), server_ucl_id, groupid/entityid/userid (Depending on preference)

    SESSIONS    Minimum of id (unique key), serverid, userid if authenticated

    Provide the sessionid and either userid or username to tamper-resistant proxy code that will provide it to the end-user UI.

We have a lot of bells and whistles in our app to provide a more robust multi-tenant solution, but this is the basics of what we are doing.

The objective is to totally isolate each company into it's own container. Proxy code of some sort should help. C++ is not required, just need code to sit in front of the web application. Also if you want to provide security in one database and shared services in another database, your shared database, which presumably will also be under your client's control, would be allowed to query directly to the security database to see if the user is logged in based on the tokens provided to it, such as when the user posts.

There are no error messages to humans using this model. If anything, any errors would either be pushed via some type of messaging que, email, or log files. The authentication layer is between the proxy code and the security database. User management is within an application that you create for the end-user companies.

This model also scales well with SQL Azure in the middle if needed as it does not need anything like CLR, FT, or etc. The only true weak point will always be the proxy code for security enthusiast. Physical security, and limited user access should lock that down.

Hope I wasn't to verbose and that this helps!


The solution we used in the end was to have two identical tables in both the common and company databases that holds the discussion and news feed data for either community level or company level.

Getting the user names and other fields that must appear in the feed is handled by a simple DiscussionUsers table in the common database that is kept up to date using triggers on the respective company user tables.

Thank you all for your answers, they definitely helped me to get into the right direction for my scenario.


While I'm not wild about the idea of separate databases per customer (They are horrible to maintain as the database struture changes), if you must and you want a unique id you can either use GUIDs or design each table to include a client_id field and make that in combination with the regular autogenerated id be the PK. I would suggest you do make the ids unique as sooner or later you will be combining two customers because Company A bought out Company B. This is much harder if you can't guarantee uniqueness of ids.

You might also consider if you can make schemas work for you to separate clients instead of databases. At least that way, you can have foreign key constraints to one common set of lookup tables. It would be a little simpler to maintain.


There are different technologies/styles depending on which database you use (mssql/oracle/others...)

From microsoft you can link databases via "linked servers" ( http://msdn.microsoft.com/en-us/library/aa213778%28v=sql.80%29.aspx ). Basicly you configure a OLE DB and the SQLserver accesses data via those ole db connections.

In oracle you can use db-Links working best between oracle databases ( http://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_5005.htm ), but also capable of connecting other db systems as well.

Selecting data will then be something like:

SELECT * FROM table@DB-linkname

For mysql and most "no cost" systems there are only workarounds to achieve this.

  • Thank you for the answer. My question was more concerned with this one specific scenario - having 1 common DB and separate DBs for companies. How do I write a query that makes a union across several databases including users that are stored as user ids in the common database? The problem is that the number of company databases will be dynamic and the only knowledge about them in the common database would probably be a mapping table "company id"/"database name"
    – WilliF
    Commented Sep 7, 2012 at 16:33
  • 1
    I have to ask why you would have more than one (centralized) company DB? it is nearly impossible to handle the user-ids in the centralized database and then keep it sync all the time (enforcing data consistance)... this is very hard on linked databases that can disconnect/dissapear at any time. I believe you will need to keep some copies (sync) of tables in the single databases. user-data would be a good example.
    – Najzero
    Commented Sep 7, 2012 at 16:36
  • The client's reasoning is better scalability and data security. I believe it is a common design pattern. Although this issue with the news feed feature popped up as not being compatible. Syncing the user tables might be a way to get around this. Thank you for suggesting it. Although I'm still open to other ideas on how to handle the design (but I'm afraid we have to keep the database separation).
    – WilliF
    Commented Sep 7, 2012 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.