多个数据库与单个数据库 [英] Multiple vs single database

问题描述

我们正在开发Web应用程序的新版本.我们有多个客户(超过500个)，每个客户都有自己的数据库和自己的数据:用户，产品...

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.

每个客户端都将在其域中安装Web应用程序的副本.我们的应用程序是首先使用SQL Server的ASP MVC实体框架代码.

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.

我们的问题是:

• 选项A:每个客户一个数据库，其中包含其表(产品，订单...)，以及一个公用数据库，用于存储用户和其他公用数据.

• Option A: One database per client containing their tables (products, orders...) and one common database to store the users and other common data.

选项B:一个包含所有数据库的大数据库，并将ClientId添加到某些表，以便客户端仅查看其数据.

Option B: One big database containing all and add a ClientId to certain tables so the clients only see their data.

优点和缺点:

• 使用选项A，我们有多个数据库，一个表中可以有100.000个订单，并且很容易检索该数据.另一方面，我们必须处理跨数据库查询并具有2个数据上下文.这是一个难题，因为我们需要为大多数查询检索用户数据，这意味着可以访问两个数据库，特定于客户端的数据库和通用数据库.

• 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.

使用选项B，我们只需要处理1个上下文，并且查询要简单得多.这种方法的主要问题是每个客户每年可能有一些带有超过10.000条记录的表.因此，在10年内，如果有500个客户，我们就可以拥有一个包含5000万条记录的表，这可能会影响性能.

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.

感谢您的建议.

编辑

这里的问题不是单数据库还是多数据库的问题，因为我们还有另外一件事，所有客户都需要访问一个公共数据库.

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.

编辑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.

我们如何做到这一点?在每个站点的web.config中向每个表添加ClientId并使用参数"clientId"过滤数据吗?

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?

推荐答案

选项A，因为该方法将允许所有不同的客户端查询他们自己选择的事务记录，而不会因为其他请求而出现任何性能问题.客户.

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.

此外，选项A将允许基于实体的自定义(如果将来需要)，这将对选项B构成挑战.建议采用基于多租户的体系结构.

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

这篇关于多个数据库与单个数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！