Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
And your client is aware that there can be no foreign keys (referential integrity) across DBs? –  Damir Sudarevic Sep 7 '12 at 16:33
    
Thank you for pointing this out. We are still in the initial design phase. I'm of course looking for a clean way to solve this design problem. Is there another way to handle this scenario? –  WilliF Sep 7 '12 at 17:11
    
Are you talking about querying between database servers? or between databases on one server? –  Neil McGuigan Sep 7 '12 at 18:07
    
It will probably be one server. –  WilliF Sep 7 '12 at 18:10
    
add comment

migrated from stackoverflow.com Sep 9 '12 at 14:09

This question came from our site for professional and enthusiast programmers.

5 Answers

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!

share|improve this answer
add comment

You could certainly maintain separate databases for each company. The shared common database could contain a table used for maintaining keys, as:

USE Common;
GO

CREATE TABLE tblIDs
(
    tblIDsPK INT NOT NULL CONSTRAINT PK_tblIDs PRIMARY KEY IDENTITY(1,1)
    , IDName nvarchar(255) NOT NULL
    , LastIDUsed INT NOT NULL CONSTRAINT DF_tblIDs_LastIDUSed DEFAULT((0))
);

SELECT * FROM tblIDs;

tblIDsPK    IDName          LastIDUsed
1           UserID          22347
2           SomeOtherID     276

Each time you want to add a record to one of the Users tables, you would use a Stored Procedure to query and update this table, returning the next ID to be used:

CREATE PROCEDURE [dbo].[GetNextID](
    @IDName nvarchar(255)
)
AS
BEGIN
    /*
        Description:    Increments and returns the LastID value from tblIDs for a given IDName
        Author:         Max Vernon
        Date:           2012-09-09
    */

    DECLARE @Retry int;
    DECLARE @EN int, @ES int, @ET int;
    SET @Retry = 5;
    DECLARE @NewID int;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    SET NOCOUNT ON;
    WHILE @Retry > 0
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION;
            SET @NewID = COALESCE((SELECT LastID FROM tblIDs WHERE IDName = @IDName),0)+1;
            IF (SELECT COUNT(IDName) FROM tblIDs WHERE IDName = @IDName) = 0 
                INSERT INTO tblIDs (IDName, LastIDUsed) VALUES (@IDName, @NewID)
            ELSE
                UPDATE tblIDs SET LastIDUsed = @NewID WHERE IDName = @IDName;
            COMMIT TRANSACTION;
            SET @Retry = -2; /* no need to retry since the operation completed */
        END TRY
        BEGIN CATCH
            IF (ERROR_NUMBER() = 1205) /* DEADLOCK */
                SET @Retry = @Retry - 1;
            ELSE
                BEGIN
                SET @Retry = -1;
                SET @EN = ERROR_NUMBER();
                SET @ES = ERROR_SEVERITY();
                SET @ET = ERROR_STATE()
                RAISERROR (@EN,@ES,@ET);
                END
            ROLLBACK TRANSACTION;
        END CATCH
    END
    IF @Retry = 0 /* must have deadlock'd 5 times. */
    BEGIN
        SET @EN = 1205;
        SET @ES = 13;
        SET @ET = 1
        RAISERROR (@EN,@ES,@ET);
    END
    ELSE
        SELECT @NewID;

END

You get the next ID with:

DECLARE @ID int;
DECLARE @t TABLE (ID int);
INSERT INTO @t
EXEC Common.dbo.GetNextID 'TestID';
SET @ID = (SELECT ID FROM @t AS tt);
SELECT @ID;

@ID at the end of the example above contains a new ID each time this code is ran. Since we are specifying the database name in EXEC Common.dbo.GetNextID 'TestID'; above, this can be called from any database on the same server as the Common database. Whenever you create a new User record, you call this code to get a new UserID, which is then unique across all the databases that manage UserIDs using this method.

Having said all that, I would probably be inclined to design everything in a single database since that is far more portable and more easily managed.

share|improve this answer
1  
Unless those are for different customers of the paying customer, which certainly won't like to see their data shared with other customer's. Even error messages can reveal sensitive information - which is not desirable. –  Fabricio Araujo Sep 17 '12 at 19:10
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
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 Sep 7 '12 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 Sep 7 '12 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 Sep 7 '12 at 17:18
    
-1 since the original post deals with multiple databases on a single server, not multiple servers. The question is about how to design multiple databases with links between databases, not about inter-server communication. –  Max Vernon Sep 9 '12 at 16:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.