We are developing a new version of our web application. We have multiple clients (500+), each client has its own database with its own data: users, products... In the new version, all clients are going to share some data, for example, users are going to be in the platform but each client will be able to access to their users only, but instead of having the users for each client we want to have all the users in a centralize table. Other things such as products, orders...are going to belong to each client. Each client will have a copy of the web app installed in their domain. Our app is an ASP MVC Entity Framework Code First, using SQL Server. Our question is: Option A: One database per client containing their tables (products, orders...) and one common database to store the users and other common data. Option B: One big database containing all and add a ClientId to certain tables so the clients only see their data. PROS AND CONS: With Option A we have several databases, we can have 100.000 orders in a table and it is easy to retrieve that data. On the other hand we have to deal with cross database queries and having 2 Data Context. This is the prolem, beacuse we need to retrieve user data for most of the queries, that means access to both databases, the client specific and the common one. With option B we just have to deal with 1 context and the queries are much more simple. The main concern for this approach is we could have some tables with more than 10.000 records per year, per client. So in 10 years, with 500 clients, we could have a table with 50 millions records and this could affect performance. Thanks for your advices. EDIT The thing here is not a question abou single vs multiple database because we have one more thing in the game, all clients need to access a common database. EDIT 2 Let's say we have decided to go for a single database for all our clients. So we will have multiple domains, each one with our application running, but we need each of them getting only their data. How can we do this? Adding a ClientId to each table and filtering the data with a parameter "clientId" in the web.config of each site?

Reputation: 914

Multiple vs single database

We are developing a new version of our web application. We have multiple clients (500+), each client has its own database with its own data: users, products...

In the new version, all clients are going to share some data, for example, users are going to be in the platform but each client will be able to access to their users only, but instead of having the users for each client we want to have all the users in a centralize table.

Other things such as products, orders...are going to belong to each client.

Each client will have a copy of the web app installed in their domain. Our app is an ASP MVC Entity Framework Code First, using SQL Server.

Our question is:

Option A: One database per client containing their tables (products, orders...) and one common database to store the users and other common data.
Option B: One big database containing all and add a ClientId to certain tables so the clients only see their data.

PROS AND CONS:

With Option A we have several databases, we can have 100.000 orders in a table and it is easy to retrieve that data. On the other hand we have to deal with cross database queries and having 2 Data Context. This is the prolem, beacuse we need to retrieve user data for most of the queries, that means access to both databases, the client specific and the common one.
With option B we just have to deal with 1 context and the queries are much more simple. The main concern for this approach is we could have some tables with more than 10.000 records per year, per client. So in 10 years, with 500 clients, we could have a table with 50 millions records and this could affect performance.

Thanks for your advices.

EDIT

The thing here is not a question abou single vs multiple database because we have one more thing in the game, all clients need to access a common database.

EDIT 2

Let's say we have decided to go for a single database for all our clients. So we will have multiple domains, each one with our application running, but we need each of them getting only their data.

How can we do this? Adding a ClientId to each table and filtering the data with a parameter "clientId" in the web.config of each site?

Upvotes: 1

Answers (2)

Gurdev Singh

Reputation: 2165

Option A would be the recommended approach as that will allow all different clients to query on their own selected transactional records and without any performance issues because of the requests from other clients.

Also, the option A would allow the entities based customization (if required in future) which would prove to be a challenge with option B. Multi-tenant based architecture is the recommendation.

The below mentioned resource can help you with some more options/possibilities.

https://msdn.microsoft.com/en-us/library/aa479086.aspx

Upvotes: 0

ste-fu

Reputation: 7482

My personal preference would be for option A, and the primary reason would be for security. You basically have only a couple of points of failure for leaking one client's data to another.

You could look to put a service on top of the common data and cache frequent requests for user data to handle that side of things.

Upvotes: 0

Multiple vs single database

Answers (2)

Related Questions