首先，仅将“Company_id”添加到几个表中是不够的。 事实上，尽管 Sai 评论说为每家公司拥有一个数据库/应用程序是荒谬的，但由于为多个离散客户托管 SaaS 系统的潜在复杂性，这绝对是有意义的。 如果您只是为几家不同的公司提供服务（例如为他们创建发票），那么 Sai 的评论是非常正确的。 然而，如果您要向多个组织提供软件应用程序，则复杂性会更高，并且可能需要离散数据库。
We had exactly this issue a few months ago in a product that is often used by organizations that, in turn, serve multiple clients. They came to us asking us to modify our SaaS system so they could create complete, discrete web sites for each of their clients (we build an online, domain-specific web-site construction tool).
A short summary: it may seem obvious to put everyone on a single database but, as you probe deeper, you'll find it isn't always cut-and-dry. There are a few challenges that you'll want to keep in mind as you proceed. A couple of points:
First, it isn't enough to just add "Company_id" to a few tables. Indeed, despite Sai's comments about it being ridiculous to have a database/app for each company there are absolutely cases where this makes sense due to the underlying complexity of hosting SaaS systems for multiple, discrete clients. If you are simply serving a few different companies (e.g. creating invoices for them) then Sai's comment is quite true. If you are providing a software application to multiple organizations, however, the complexity is quite a bit higher and discrete databases may well be in order.
Second, be prepared for a significantly more complex user querying and reporting effort in a multi-client database. For example, when building our user-querying capabilities we had to be absolutely certain that there would be no "bleed-through" between organizations as there was HIPAA-protected data involved. This meant that the querying and reporting capabilities required a level of engineering far in excess of what had gone before. In our case, our querying capabilities were very flexible and essentially permitted users to construct queries on the fly (subject to some pretty stiff constraints, obviously - we weren't accepting SQL!). Thus, we had to make sure that every query was automatically modified to use the "Company_ID" constraint, as appropriate, no matter what the origin of the data or the permissions of the staff member submitting the query. The wrinkle? Our 'super-user' analysis account had to be able to run the queries without such a constraint...
Third, you probably do not yet anticipate just how many things need to be separated. For example, I had built a quite sophisticated "Settings" object into the site that pulled settings from the database on startup and maintained them in the "Application" object (this is a .NET app). This all needed to be floated to handle multiple organizations.
For another example, fields that used to be unique for us (e.g. logins) now had to be done as part of a Company_ID, LoginID key. If you are building from scratch, this isn't such a big idea, but we were retrofitting so it was.
Anyway, as I proceeded through the build, I was surprised to find out just how much work was required to do this right.
Fourth, I always build software using a "meta-programming" approach. That is, I rarely build a single-purpose page but rather often build a highly customizable framework in order to facilitate end-user customization and internal code reuse. While I anticipated that this would help with a transition to multi-organization databases, it often did not! Because such coding is often fairly complex to begin with, floating the Organization was often more difficult than if I simply had a vanilla web page.
Finally, if there is no crying need to share data (e.g. analysis of overall usage patterns) then you might want to stick with discrete databases simply to facilitate scaling. While you add new multi-org databases (a second discrete system), our scaling often involved existing clients that suddenly experienced a surge in growth. Peeling them out of an existing database and onto a new server is a bit more difficult than just moving to a new server with an existing database.
With all of these caveats, you might think I'd advise you against building a system capable of handling multiple organizations on a single database. However, this isn't the case: there are some real wins taking a multi-org approach! Usage analysis, cross-organizational reporting, application deployment, etc. are all significantly enhanced. I just want to provide you the benefit of our experience in the hopes that it'll help you anticipate some of the difficulties that you may anticipate.
Creating a new table for every anything - let alone a new DATABASE - would be absurd (and indeed is the fodder for many a Daily WTF post).
That's the whole point of using a relational database - you link things together.
If you need to then make the db bigger, there are tons of ways to do it (master/slave, master/multislave, dual master, horizontal scaling, just buying a ton of RAM, etc).
FWIW: my last app had ~12 million users (~300k per day); it had two databases (horizontal scaling; done by the previous guys. I didn't agree with that decision, and would've just used slaves).
EDIT: Caveat - this is assuming that you are only exposing access via your app (either its web interface or an API).
If you really need to expose the database directly to customers, a) tell them to think again because it's a bad idea, and b) then you may need to make hard choices between what's easier to maintain that preserves the needed firewalling. But srsly, you don't want to go there if you can help it.
When running software as a service there's always a few things to consider when choosing a database strategy. Two arguments for a separate database per client are backups and (feeling of) security. If you have one database with a discreet customer_id field and customer 666 screws up and wants his data of yesterday restored, you're in for some work.
Have a single database per customer is also sometimes required by that customer because the data might be sensitive. He could rightfully argue that is it more save to put the data in different databases and setup good security.
发布评论
评论（4）
几个月前，我们在一个经常被为多个客户提供服务的组织所使用的产品中遇到了这个问题。 他们来找我们，要求我们修改我们的 SaaS 系统，以便他们可以为每个客户创建完整的、独立的网站（我们构建了一个在线的、特定领域的网站构建工具）。
简短的总结：将每个人都放在一个数据库上似乎是显而易见的，但是，当您深入探究时，您会发现它并不总是一成不变的。 在继续操作时，您需要牢记一些挑战。 有几点：
首先，仅将“Company_id”添加到几个表中是不够的。 事实上，尽管 Sai 评论说为每家公司拥有一个数据库/应用程序是荒谬的，但由于为多个离散客户托管 SaaS 系统的潜在复杂性，这绝对是有意义的。 如果您只是为几家不同的公司提供服务（例如为他们创建发票），那么 Sai 的评论是非常正确的。 然而，如果您要向多个组织提供软件应用程序，则复杂性会更高，并且可能需要离散数据库。
其次，为多客户端数据库中更加复杂的用户查询和报告工作做好准备。 例如，在构建用户查询功能时，我们必须绝对确定组织之间不会出现“渗透”，因为涉及受 HIPAA 保护的数据。 这意味着查询和报告功能所需的工程水平远远超出了以前的水平。 在我们的例子中，我们的查询功能非常灵活，基本上允许用户动态构建查询（显然，受到一些相当严格的限制 - 我们不接受 SQL！）。 因此，我们必须确保每个查询都自动修改为适当地使用“Company_ID”约束，无论数据的来源或提交查询的工作人员的权限如何。 皱纹？ 我们的“超级用户”分析帐户必须能够在没有这样的约束的情况下运行查询...
第三，您可能还没有预料到需要分离多少东西。 例如，我在站点中构建了一个相当复杂的“设置”对象，该对象在启动时从数据库中提取设置并将它们维护在“应用程序”对象中（这是一个 .NET 应用程序）。 这一切都需要浮动才能处理多个组织。
再举一个例子，曾经对我们来说唯一的字段（例如登录名）现在必须作为 Company_ID、LoginID 键的一部分来完成。 如果您是从头开始构建，这并不是一个伟大的想法，但我们正在进行改造，所以确实如此。
不管怎样，当我继续构建时，我惊讶地发现需要做多少工作才能正确完成这项工作。
第四，我总是使用“元编程”方法构建软件。 也就是说，我很少构建单一用途的页面，而是经常构建高度可定制的框架，以促进最终用户定制和内部代码重用。 虽然我预计这将有助于过渡到多组织数据库，但事实往往并非如此！ 由于此类编码一开始通常相当复杂，因此浮动组织通常比我只是拥有一个普通网页更困难。
最后，如果没有迫切需要共享数据（例如分析总体使用模式），那么您可能希望坚持使用离散数据库，只是为了方便扩展。 当您添加新的多组织数据库（第二个离散系统）时，我们的扩展通常涉及突然经历增长激增的现有客户。 将它们从现有数据库中剥离出来并转移到新服务器上比仅仅迁移到具有现有数据库的新服务器要困难一些。
考虑到所有这些警告，您可能会认为我建议您不要构建能够在单个数据库上处理多个组织的系统。 然而，事实并非如此：采用多组织方法确实取得了一些成果！ 使用情况分析、跨组织报告、应用程序部署等均得到显着增强。 我只是想让您从我们的经验中受益，希望它能帮助您预见到一些您可能会遇到的困难。
We had exactly this issue a few months ago in a product that is often used by organizations that, in turn, serve multiple clients. They came to us asking us to modify our SaaS system so they could create complete, discrete web sites for each of their clients (we build an online, domain-specific web-site construction tool).
A short summary: it may seem obvious to put everyone on a single database but, as you probe deeper, you'll find it isn't always cut-and-dry. There are a few challenges that you'll want to keep in mind as you proceed. A couple of points:
First, it isn't enough to just add "Company_id" to a few tables. Indeed, despite Sai's comments about it being ridiculous to have a database/app for each company there are absolutely cases where this makes sense due to the underlying complexity of hosting SaaS systems for multiple, discrete clients. If you are simply serving a few different companies (e.g. creating invoices for them) then Sai's comment is quite true. If you are providing a software application to multiple organizations, however, the complexity is quite a bit higher and discrete databases may well be in order.
Second, be prepared for a significantly more complex user querying and reporting effort in a multi-client database. For example, when building our user-querying capabilities we had to be absolutely certain that there would be no "bleed-through" between organizations as there was HIPAA-protected data involved. This meant that the querying and reporting capabilities required a level of engineering far in excess of what had gone before. In our case, our querying capabilities were very flexible and essentially permitted users to construct queries on the fly (subject to some pretty stiff constraints, obviously - we weren't accepting SQL!). Thus, we had to make sure that every query was automatically modified to use the "Company_ID" constraint, as appropriate, no matter what the origin of the data or the permissions of the staff member submitting the query. The wrinkle? Our 'super-user' analysis account had to be able to run the queries without such a constraint...
Third, you probably do not yet anticipate just how many things need to be separated. For example, I had built a quite sophisticated "Settings" object into the site that pulled settings from the database on startup and maintained them in the "Application" object (this is a .NET app). This all needed to be floated to handle multiple organizations.
For another example, fields that used to be unique for us (e.g. logins) now had to be done as part of a Company_ID, LoginID key. If you are building from scratch, this isn't such a big idea, but we were retrofitting so it was.
Anyway, as I proceeded through the build, I was surprised to find out just how much work was required to do this right.
Fourth, I always build software using a "meta-programming" approach. That is, I rarely build a single-purpose page but rather often build a highly customizable framework in order to facilitate end-user customization and internal code reuse. While I anticipated that this would help with a transition to multi-organization databases, it often did not! Because such coding is often fairly complex to begin with, floating the Organization was often more difficult than if I simply had a vanilla web page.
Finally, if there is no crying need to share data (e.g. analysis of overall usage patterns) then you might want to stick with discrete databases simply to facilitate scaling. While you add new multi-org databases (a second discrete system), our scaling often involved existing clients that suddenly experienced a surge in growth. Peeling them out of an existing database and onto a new server is a bit more difficult than just moving to a new server with an existing database.
With all of these caveats, you might think I'd advise you against building a system capable of handling multiple organizations on a single database. However, this isn't the case: there are some real wins taking a multi-org approach! Usage analysis, cross-organizational reporting, application deployment, etc. are all significantly enhanced. I just want to provide you the benefit of our experience in the hopes that it'll help you anticipate some of the difficulties that you may anticipate.
肯定是company_id。
为所有东西创建一个新表 - 更不用说一个新数据库 - 是荒谬的（而且确实是许多 Daily WTF 帖子的素材）。
这就是使用关系数据库的全部意义——将事物链接在一起。
如果您需要扩大数据库，有很多方法可以实现（主/从、主/多从、双主、水平扩展、只需购买大量 RAM 等）。
FWIW：我的上一个应用程序拥有约 1200 万用户（每天约 30 万）； 它有两个数据库（水平扩展；由以前的人完成。我不同意这个决定，并且只会使用奴隶）。
编辑：警告 - 这是假设您仅通过应用程序（其 Web 界面或 API）公开访问权限。
如果您确实需要将数据库直接公开给客户，a）告诉他们再考虑一下，因为这是一个坏主意，b）那么您可能需要在更容易维护和保留所需防火墙之间做出艰难的选择。 但老实说，如果你能控制的话，你就不想去那里。
Definitely the company_id.
Creating a new table for every anything - let alone a new DATABASE - would be absurd (and indeed is the fodder for many a Daily WTF post).
That's the whole point of using a relational database - you link things together.
If you need to then make the db bigger, there are tons of ways to do it (master/slave, master/multislave, dual master, horizontal scaling, just buying a ton of RAM, etc).
FWIW: my last app had ~12 million users (~300k per day); it had two databases (horizontal scaling; done by the previous guys. I didn't agree with that decision, and would've just used slaves).
EDIT: Caveat - this is assuming that you are only exposing access via your app (either its web interface or an API).
If you really need to expose the database directly to customers, a) tell them to think again because it's a bad idea, and b) then you may need to make hard choices between what's easier to maintain that preserves the needed firewalling. But srsly, you don't want to go there if you can help it.
我最近发现，当公司之间共享应用程序和数据库时，SaaS 中的此类事物有一个名称：
多租户
I recently found out there is a name for this sort of thing in SaaS, when an application and database is shared between companies:
Multitenancy
当将软件作为服务运行时，在选择数据库策略时总是需要考虑一些事情。 每个客户端使用单独的数据库的两个论据是备份和（感觉）安全性。 如果您有一个数据库，其中包含一个谨慎的 customer_id 字段，而客户 666 搞砸了并希望恢复他昨天的数据，那么您需要做一些工作。
有时该客户还要求每个客户拥有一个数据库，因为数据可能很敏感。 他可以正确地争辩说，将数据放在不同的数据库中并设置良好的安全性是否更节省。
-埃杜德
When running software as a service there's always a few things to consider when choosing a database strategy. Two arguments for a separate database per client are backups and (feeling of) security. If you have one database with a discreet customer_id field and customer 666 screws up and wants his data of yesterday restored, you're in for some work.
Have a single database per customer is also sometimes required by that customer because the data might be sensitive. He could rightfully argue that is it more save to put the data in different databases and setup good security.
-Edoode