Software Architecture and Database Design: One database/web application per each company or one database/web application for all companies?

Question

I'm designing a web driven SQL database application from ground up. The application will manage information for clients that are in the same type of industry. In other words, the information (entities and relations among them) regarding each client does not vary too much from one to the next. However, the volume of the information is dictated by the size of the company. The application could be hosted on our server(s) or anywhere a client chooses.

My first question is: what are the pros and cons given the following options:

• A. Manage multiple clients information in the same database;
• B. Manage one client information per database; so each client will have its own database;

My second question is: what are the pros and cons given the following methods of deployment?

• A. Each client gets its own server (node);
• B. Use large RAID drive(s) with one powerful server with multiple websites;

As decisions to these choices affect my design, I would like to know the pros and cons from different perspectives including maintenance, cost(financially and in time) and architecture to name a few.

Technologies used:

• Database: MS SQL
• Platform: ASP.NET
• Language: C#

Any comments or suggestions are most welcome,

Thanks,

Cullen

Answer 1

I'd hybrid this. Design it for the ability to use separate databases, but don't separate until there is a clear performance benefit.

Design your application so that multiple clients can exist in the same database. (i.e. build a layer of separation between client data). Then plan for it all to be in one database.

That way, you don't overarchitect your solution to start out with, but it gives you the flexibility to spin off very high-usage clients to a dedicated database. If your database exists with only one client in it, so what? This will also save on server costs so that you can be sure that you're using the hardware investment in a reasonable way already before adding more capacity.

Answer 2

I would not recommend giving each client their own database. I planned to do that for my current application and was talked into a single database. Good thing, since we now have 300 clients. Can you imagine managing 300 databases? Updating each one everytime you have an upgrade? Making sure each is up to date, etc.

Disclaimer: In practice we actually have several databases. A few very large clients have their own database and others share the remaining ones. I think maybe 7 databases in all.

We have the databases on one powerful SQL server. If you have multiple databases and put them on different servers, you run the risk that some servers are busier than others and small clients are underutilising their server.