I usually add ClientID to all tables and go with one database. But since the database is usually hard to scale I will also make it possible to run on different database instances for some or all clients.
That way you can have a bunch of small clients in one database and the big ones on separate servers.
A key factor for maintainability though, is that you keep the schema identical in all databases. There will be headache enough to manage the versioning without introducing client specific schemas.
收听 Stackoverflow 播客，其中 Joel 和 Jeff 谈论了同一个问题。 Joel 正在谈论他们提供软件托管版本的经验。 他指出，在整个数据库中添加客户端 ID 会使设计和代码变得复杂（您确定您没有意外忘记将其添加到某些 WHERE 子句中吗？）并使托管功能（例如特定于客户端的备份）变得复杂。
这是在第 20 或第 21 集（查看文字记录了解详细信息）。
Listen to the Stackoverflow podcast where Joel and Jeff talk about the very same question. Joel is talking about their experience offering a hosted version of their software. He points out that adding client ids all over your DB complicates the design and code (are you sure you didn't accidentally forget to add it to some WHERE clause?) and complicates hosting feature, such as client-specific backups.
It was in episode #20 or #21 (check the transcripts for details).
In my view, it will depend on your likely customer base. If you could get into a situation where arch-rivals are both using your system, then you would be better off with separate databases. It also depends on how multiple databases get implemented by your DBMS. If each database has a separate copy of the infrastructure, then that suggests a single database (or a change of DBMS). If multiple databases can be served by a single copy of the infrastructure, then I'd go for separate databases.
Think of database backup. Customer A says "Please send me a copy of my data". Much, much easier in a separate database setup than if a single database is shared. Think of removing a customer; again, much easier with separate databases.
(The 'infrastructure' part is mealy-mouthed because there are major differences between different DBMS about what constitutes a 'database' versus a 'server instance', for example. Add: The question is tagged 'mysql', so maybe those thoughts aren't completely relevant.)
Add: One more issue - with multiple customers in a single database, every SQL query is going to need to ensure that the data for the correct customer is chosen. That means that the SQL is going to be harder to write, and read, and the DBMS is going to have to work harder on processing the data, and indexes will be bigger, and ... I really would go with a separate database per customer for many purposes.
Clearly, StackOverflow (as an example) does not have a separate database per user; we all use the same database. But if you were running accounting systems for different companies, I don't think it would be acceptable (to the companies, and possibly not to the legal people) to share databases.
DEVELOPMENT For rapid development, use a database per customer. Think how easy it will be to backup, restore, or delete a customer's data. Or to measure/monitor/bill usage. You won't need to write code to do it by yourself, just use your database primitives.
PERFORMANCE For performance, use a database for all. Think about connection pooling, shared memory, caching, etc.
BUSINESS If your business plan is to have lots of small customers (think hotmail) you should probably work on a single DB. And have all administrative tasks such registration, deletion, data migration, etc. fully automated and exposed in a friendly interface. If you plan to have dozens or up to a few hundreds of big customers then you can work in one DB per customer and have system administration scripts in place that can be operated by your customer support staff.
So if you can, go with the single database. I agree that security problems would only occur due to bugs, as you can implement all access control in the application. In some databases, you can still use the database access control by careful use of views (so that each authenticated user gets a different view).
There are ways to provide extensibility also. For example, you could create a single table with extension attributes (keyed by tenant, base record, and extension attribute id). Or you can create per-tenant extension tables, so that each tenant has his own extension schema.
When you're designing a multi-tenant database, you generally have three options:
Have one database per tenant
Have one schema per tenant
Have all tenants share the same table(s)
The option you pick has implications on scalability, extensibility and isolation. These implications have been widely discussed across different StackOverflow questions and database articles.
In practice, each of the three design options -with enough effort- can address questions around scale, data that varies across tenants, and isolation. The decision depends on the primary dimension you’re building for. The summary:
If you're building for scale: Have all tenants share the same table(s)
If you're building for isolation: Create one database per tenant
For example, Google and Salesforce follow the first pattern and have their tenants share the same tables. Stackoverflow on the other hand follows the second pattern and keeps one database per tenant. The second approach is also more commonplace in regulated industries, such as healthcare.
The decision comes down to the primary dimension you're optimizing your database design for. This article on designing your SaaS database for scale talks about the trade-offs and provides a summary in the context of PostgreSQL.
Having a database per client generally does not scale well. MySQL (and probably other databases) holds resources open per table, this does not lend itself well to 10k+ tables on one instance, which would happen in a large-scale multitenancy situation.
Of course, if you have some other issue which causes other problems before you get to this level, this may not be relevant.
Additionally, "sharding" a multi-tenant application is likely€ to be the right thing to do eventually as your application gets bigger and bigger.
Sharding does not however mean one database (or instance) per tenant, but one per shard or set of shards, which may have several tenants each. You will need to discover the right tuning parameters for yourself, probably in production (hence it probably needs to be pretty tunable from the outset)
You can start with a single database and partition it as the application grows. If you do this, there a few things I would recommend:
1) Design the database in a way that it can be easily partitioned. For example, if customers are going to share data, make sure that data is easily replicated across each database.
2) When you have only one database, make sure it is being backed up to another physical server. In the event of a failover you can revert traffic to this other server and still have your data intact.
发布评论
评论（9）
我通常将 ClientID 添加到所有表中并使用一个数据库。
但由于数据库通常很难扩展，我还将让一些或所有客户端在不同的数据库实例上运行成为可能。
这样，您就可以在一个数据库中拥有一堆小型客户端，而在不同的服务器上拥有大型客户端。
不过，可维护性的一个关键因素是在所有数据库中保持相同的架构。 在不引入客户端特定模式的情况下管理版本控制将是一件令人头疼的事情。
I usually add ClientID to all tables and go with one database.
But since the database is usually hard to scale I will also make it possible to run on different database instances for some or all clients.
That way you can have a bunch of small clients in one database and the big ones on separate servers.
A key factor for maintainability though, is that you keep the schema identical in all databases. There will be headache enough to manage the versioning without introducing client specific schemas.
收听 Stackoverflow 播客，其中 Joel 和 Jeff 谈论了同一个问题。 Joel 正在谈论他们提供软件托管版本的经验。 他指出，在整个数据库中添加客户端 ID 会使设计和代码变得复杂（您确定您没有意外忘记将其添加到某些 WHERE 子句中吗？）并使托管功能（例如特定于客户端的备份）变得复杂。
这是在第 20 或第 21 集（查看文字记录了解详细信息）。
Listen to the Stackoverflow podcast where Joel and Jeff talk about the very same question. Joel is talking about their experience offering a hosted version of their software. He points out that adding client ids all over your DB complicates the design and code (are you sure you didn't accidentally forget to add it to some WHERE clause?) and complicates hosting feature, such as client-specific backups.
It was in episode #20 or #21 (check the transcripts for details).
在我看来，这将取决于您可能的客户群。 如果您可能遇到主要竞争对手都使用您的系统的情况，那么您最好使用单独的数据库。 它还取决于您的 DBMS 如何实现多个数据库。 如果每个数据库都有一个单独的基础设施副本，则建议使用单个数据库（或更改 DBMS）。 如果多个数据库可以由基础设施的单个副本提供服务，那么我会选择单独的数据库。
想想数据库备份。 客户 A 说“请向我发送一份数据副本”。 单独的数据库设置比共享单个数据库要容易得多。 考虑删除一个客户； 同样，使用单独的数据库会更容易。
（“基础设施”部分是空话，因为不同的 DBMS 之间在“数据库”与“服务器实例”的构成方面存在重大差异。添加：问题标记为“mysql” '，所以也许这些想法并不完全相关。）
添加：
还有一个问题 - 对于单个数据库中的多个客户，每个 SQL 查询都需要确保选择正确客户的数据。 这意味着 SQL 将更难编写和读取，DBMS 将不得不更加努力地处理数据，索引将更大，而且……我真的会为每个数据库使用一个单独的数据库。客户有多种用途。
显然，StackOverflow（作为示例）没有为每个用户提供单独的数据库； 我们都使用相同的数据库。 但是，如果您为不同的公司运行会计系统，我认为共享数据库是不可接受的（对公司而言，可能对法人而言）。
In my view, it will depend on your likely customer base. If you could get into a situation where arch-rivals are both using your system, then you would be better off with separate databases. It also depends on how multiple databases get implemented by your DBMS. If each database has a separate copy of the infrastructure, then that suggests a single database (or a change of DBMS). If multiple databases can be served by a single copy of the infrastructure, then I'd go for separate databases.
Think of database backup. Customer A says "Please send me a copy of my data". Much, much easier in a separate database setup than if a single database is shared. Think of removing a customer; again, much easier with separate databases.
(The 'infrastructure' part is mealy-mouthed because there are major differences between different DBMS about what constitutes a 'database' versus a 'server instance', for example. Add: The question is tagged 'mysql', so maybe those thoughts aren't completely relevant.)
Add:
One more issue - with multiple customers in a single database, every SQL query is going to need to ensure that the data for the correct customer is chosen. That means that the SQL is going to be harder to write, and read, and the DBMS is going to have to work harder on processing the data, and indexes will be bigger, and ... I really would go with a separate database per customer for many purposes.
Clearly, StackOverflow (as an example) does not have a separate database per user; we all use the same database. But if you were running accounting systems for different companies, I don't think it would be acceptable (to the companies, and possibly not to the legal people) to share databases.
发展
为了快速开发，请为每个客户使用一个数据库。 想象一下备份、恢复或删除客户数据是多么容易。 或者测量/监控/计费使用情况。 您不需要自己编写代码来完成此操作，只需使用数据库原语即可。
性能
为了性能，全部使用数据库。 考虑连接池、共享内存、缓存等。
业务
如果您的业务计划是拥有大量小客户（例如 hotmail），您可能应该在单个数据库上工作。 并使所有管理任务（例如注册、删除、数据迁移等）完全自动化并以友好的界面公开。 如果您计划拥有数十个甚至数百个大客户，那么您可以为每个客户使用一个数据库，并准备好可由客户支持人员操作的系统管理脚本。
DEVELOPMENT
For rapid development, use a database per customer. Think how easy it will be to backup, restore, or delete a customer's data. Or to measure/monitor/bill usage. You won't need to write code to do it by yourself, just use your database primitives.
PERFORMANCE
For performance, use a database for all. Think about connection pooling, shared memory, caching, etc.
BUSINESS
If your business plan is to have lots of small customers (think hotmail) you should probably work on a single DB. And have all administrative tasks such registration, deletion, data migration, etc. fully automated and exposed in a friendly interface. If you plan to have dozens or up to a few hundreds of big customers then you can work in one DB per customer and have system administration scripts in place that can be operated by your customer support staff.
对于多租户，您在租户之间共享的资源越多，性能通常会提高，请参阅
http://en.wikipedia .org/wiki/Multitenancy
因此，如果可以的话，请使用单一数据库。 我同意安全问题只会由于错误而发生，因为您可以在应用程序中实现所有访问控制。 在某些数据库中，您仍然可以通过仔细使用视图来使用数据库访问控制（以便每个经过身份验证的用户获得不同的视图）。
还有一些方法可以提供可扩展性。 例如，您可以创建一个包含扩展属性的表（以租户、基本记录和扩展属性 ID 为键）。 或者，您可以创建每个租户的扩展表，以便每个租户都有自己的扩展架构。
For multitenancy, performance will typically increase the more resources you manage to share across tenants, see
http://en.wikipedia.org/wiki/Multitenancy
So if you can, go with the single database. I agree that security problems would only occur due to bugs, as you can implement all access control in the application. In some databases, you can still use the database access control by careful use of views (so that each authenticated user gets a different view).
There are ways to provide extensibility also. For example, you could create a single table with extension attributes (keyed by tenant, base record, and extension attribute id). Or you can create per-tenant extension tables, so that each tenant has his own extension schema.
当您设计多租户数据库时，通常有以下三种选择：
您选择的选项会影响可伸缩性、可扩展性和隔离性。 这些影响已在不同的 StackOverflow 问题和数据库文章中得到了广泛讨论。
在实践中，只要付出足够的努力，这三个设计选项中的每一个都可以解决有关规模、不同租户的数据和隔离等方面的问题。 该决定取决于您构建的主要维度。 摘要：
例如，Google 和 Salesforce 遵循第一种模式，并让其租户共享相同的表。 另一方面，Stackoverflow 遵循第二种模式，为每个租户保留一个数据库。 第二种方法在医疗保健等受监管行业中也更为常见。
该决定取决于您优化数据库设计的主要维度。 这篇关于设计您的规模化 SaaS 数据库讨论了权衡，并在 PostgreSQL 的背景下提供了总结。
When you're designing a multi-tenant database, you generally have three options:
The option you pick has implications on scalability, extensibility and isolation. These implications have been widely discussed across different StackOverflow questions and database articles.
In practice, each of the three design options -with enough effort- can address questions around scale, data that varies across tenants, and isolation. The decision depends on the primary dimension you’re building for. The summary:
For example, Google and Salesforce follow the first pattern and have their tenants share the same tables. Stackoverflow on the other hand follows the second pattern and keeps one database per tenant. The second approach is also more commonplace in regulated industries, such as healthcare.
The decision comes down to the primary dimension you're optimizing your database design for. This article on designing your SaaS database for scale talks about the trade-offs and provides a summary in the context of PostgreSQL.
需要考虑的另一点是，您可能有法律义务将一家公司的数据与另一家公司的数据分开。
Another point to consider is that you may have a legal obligation to keep one companies' data separate from anothers'.
每个客户端都有一个数据库通常不能很好地扩展。 MySQL（可能还有其他数据库）为每个表保留打开的资源，这不太适合一个实例上有 10k+ 表，而这种情况会在大规模多租户情况下发生。
当然，如果您在达到此级别之前遇到其他问题导致其他问题，则这可能不相关。
此外，随着应用程序变得越来越大，“分片”多租户应用程序最终可能是正确的做法。
然而，分片并不意味着每个租户一个数据库（或实例），而是每个分片或一组分片一个数据库（或实例），每个分片可能有多个租户。 您需要为自己找到正确的调整参数，可能是在生产中（因此它可能需要从一开始就可以很好地调整）
€我不能保证这一点。
Having a database per client generally does not scale well. MySQL (and probably other databases) holds resources open per table, this does not lend itself well to 10k+ tables on one instance, which would happen in a large-scale multitenancy situation.
Of course, if you have some other issue which causes other problems before you get to this level, this may not be relevant.
Additionally, "sharding" a multi-tenant application is likely€ to be the right thing to do eventually as your application gets bigger and bigger.
Sharding does not however mean one database (or instance) per tenant, but one per shard or set of shards, which may have several tenants each. You will need to discover the right tuning parameters for yourself, probably in production (hence it probably needs to be pretty tunable from the outset)
€ I can't guarantee it.
您可以从单个数据库开始，并随着应用程序的增长对其进行分区。 如果您这样做，我会建议您执行以下操作：
1）以易于分区的方式设计数据库。 例如，如果客户要共享数据，请确保可以在每个数据库之间轻松复制数据。
2) 当您只有一个数据库时，请确保将其备份到另一台物理服务器。 如果发生故障转移，您可以将流量恢复到另一台服务器，并且数据仍然完好无损。
You can start with a single database and partition it as the application grows. If you do this, there a few things I would recommend:
1) Design the database in a way that it can be easily partitioned. For example, if customers are going to share data, make sure that data is easily replicated across each database.
2) When you have only one database, make sure it is being backed up to another physical server. In the event of a failover you can revert traffic to this other server and still have your data intact.