多租户或非多租户
我需要做出一个艰难的数据库设计决策，涉及到我积极维护的客户基于 Web 的 CRM 的分支机构数量不断增加的多租户。
我很早就决定为每个分支使用单独的应用程序和单独的数据库，因为这是满足具有不同数据和代码要求的三个不同分支的最简单方法。我还希望避免在每个查询中管理租户 ID，就像我在 2007 年构建的传统经典 ASP (cringe) 应用程序中所做的那样……太恐怖了。
但现在分支机构的数据需求正在趋同，随着业务的扩展，我需要能够快速推出新分支机构并共享全球产品SKU。
由于所有分支的表和视图都是相同的，并且现在可以使用更好的 ORM 工具来管理多租户应用程序，因此我想知道为多个分支拥有一个共享数据库是否会更好。
集中式数据库的注意事项：
- 全球产品 SKU
- 简化库存申请
- 更易于备份
- 部署一次，而不是为每个分支机构
部署 集中式数据库的注意事项：
- 更容易区分具有单独数据库的分支机构需求
- 模块化部署（一个损坏的分支不会破坏所有）
- 共享数据库更难管理和开发
- 我必须重新设计发票编号（由种子生成的序列）
- 到处都少了 WHERE 子句
- 对其他分支有很多影响
恢复一个损坏的分支 不太可能有多达 10 个分支机构。目前有 3 名
具有该领域实际经验的开发人员，如果您遇到我的情况，您会怎么做？保留应用程序和DB 是分开的，还是合并成一个巨大的系统？
编辑：关于多租户利弊的Microsoft 文章很棒。我应该指出，分支之间的数据隔离不是主要问题。
I have a difficult database design decision to make regarding multi-tenancy for the growing number of branches of my client's web-based CRM, which I actively maintain.
I made the decision early on to use separate applications with separate databases for each branch, because it was the simplest way to cater for three different branches with disparate data and code requirements. I also wanted to avoid managing Tenant IDs in every query, like I had to with the legacy Classic ASP (cringe) application I built in 2007...the horror.
But now the data requirements for branches are converging and as the business expands, I need to be able to roll out new branches quickly and share global product SKUs.
Since tables and views are the same for all branches and better ORM tools are now available to manage multi-tenant applications, I wonder if it would be better to have a shared database for multiple branches.
Considerations for a centralised database:
- Global product SKUs
- Simplified inventory requisitions
- Easier to backup
- Deploy once instead of for every branch
Considerations against a centralised database:
- Easier to differentiate branch requirements with separate DBs
- Modular deployment (one downed branch doesn't break all)
- Harder to manage and develop for shared DB
- I have to re-design invoice numbering (sequence generated by seed)
- Less WHERE clauses everywhere
- Restoring one broken branch has plenty of implications for other branches
It is unlikely there will be ever be as many as 10 branches. Right now there are 3.
Developers with real-world experience in this area, what would you do in my situation? Keep apps & DBs separate, or combine into one giant system?
Edit: Great Microsoft article on multi-tenancy pro's and cons. I should note that data isolation between branches is not a major issue.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
硬着头皮将它们合并起来。在需要的位置添加您的租户 ID，并更改您的查询。
对于自定义，请研究插件类型架构，该架构允许您为特定客户端部署特定屏幕。
我们有一个软件产品就是以这种方式构建的。有时它部署在客户端站点上，有时我们托管它。出于所有意图和目的，处理具有客户端特定扩展的单个代码库比处理代码的多个分支更容易一个数量级。
其一，当我们解决问题时，我们是为每个人解决问题。当然，如果我们破坏了它，我们就会为每个人破坏它，但这就是单元测试的目的。针对一个代码库维护一组单元测试比为多个分支维护它们要容易得多。
我们从事多租户业务已有 10 多年了，我一次也没有回头看。一般来说，如果您在验证检索记录的人实际上是否有权获取该记录时已经具有安全意识，那么查询并没有那么不同。
我不同意科尔宾提出的问题。围绕版本控制的问题应该已经通过建立基于属性的安全结构来处理。这样您就可以通过用户或租户配置来打开/关闭功能。另外，我发现客户 A 不想要客户 B 所要求的相同新功能的情况很少见。
第二个关于数据混合的问题也不是问题。只需查看 salesforce.com 或任何其他大型网站即可。他们绝对使用多租户架构，并且从使用它们的客户端数量来看，这似乎不是问题。这里最重要的是能够确保您的客户的数据是安全的。
Bite the bullet and merge them. Add your tenant ID where it needs to be, and change your queries.
For customizations, look into a plugin type architecture that would allow you to deploy specific screens for particular clients.
We have a software product that is built in just such a fashion. Sometimes it's deployed on a client site, sometimes we host it. For all intents and purposes it is an order of magnitude easier to deal with a single code base that has client specific extensions than dealing with multiple branches of the code.
For one, when we fix a problem, we fix it for everyone. Sure, if we break it, we break it for everyone but that's what unit tests are for. And it is a heck of a lot easier to maintain a set of unit tests against one code base than it is to maintain them for multiple branches.
We've been doing multi-tenant for over 10 years and not once have I looked back. Generally speaking, queries aren't that different if you are already security conscious in verifying that the person retrieving the record is actually allowed to get it.
I disagree with the issues brought up by Corbin. The one around versioning should already be handled by having an attribute based security structure in place. That way you can turn things on/off via user or tenant configuration. Also, I find it very rare that client A doesn't want the same new feature that client B asked for.
The second one about data mingling is also a non-issue. Just look at salesforce.com or any of the other large scale sites. They absolutely use a multi-tenant architecture and judging by the shear number of clients that use them this doesn't seem to be a problem. The main thing here is being able to ensure to your clients that their data is secured.
如果您谈论的是 10 个分支机构，则多租户似乎成本高昂，收益甚微。
您没有提到多租户的复杂性：
您可以通过更好的实践消除一些痛点：
不要误会我的意思，我开发的最有趣的应用程序之一是多租户。这可能会带来巨大的好处，但您更有可能看到它们拥有数千个客户，而不是十个客户。
If you're talking about 10 branches, multi-tenancy seems like a big cost with little benefit.
There are complications with multi-tenancy you don't mention:
You can eliminate a few of your pain points with better practices:
Don't get me wrong, one of the more interesting apps I worked on was multi-tenant. There can be huge benefits, but you'll more likely see them with thousands of clients versus ten.
老实说，这是一个商业问题。您要么能够在多租户设置中向较小的用户组提供更多自定义功能，但需要更多的 IT 开销。也就是说，您将需要更多的人员和硬件（管理层的说法是：金钱），但提供更大的灵活性。
如果您处于一个巨大的 Borg 环境中，那么您可以降低 IT 开销（同样，人员和事物，以管理资金），但您的最终用户必须在其软件中吸收较少的灵活性。所有的错误都是所有用户的问题，所以大的错误很快就会被解决。然而，新功能也会影响所有用户，因此速度会变慢。
如果你个人有能力拨打这个电话，而企业只需要听你说的话，或者你可以以某种方式推动管理层，我建议你问自己一系列关于你更喜欢哪种情况的问题：
A) 做你希望有更多的人来管理这件事并分担工资/责任
B) 据您所知，很快就会有第四个用户组吗？
C) 你想在这家公司呆多久？
如果您对前两个问题的回答是肯定的，那么您可能需要多租户。
Honestly, this is a business question. You are either going to be able to deliver more customized features to a smaller user group in a multi-tenant setup but with more IT overhead. That is, you will need more people and hardware (management reads this:money) but deliver greater flexibility.
If you are in a one GIANT Borg situation then you lower your IT overhead (again, people & things, to management money) but your end users have to absorb less flexibility in their software. All bugs are problems for all users, so big ones get whacked fast. However new features impact all users as well so they happen slower.
If you personally have the juice to make this call and the business just has to listen to what you say, or you can nudge management one way or another I'd suggest asking YOURSELF a series of questions about which scenario you prefer:
A) Do you want to have to have more people managing this and share salary/responsibilty
B) To the best of your knowledge is there going to be a 4th user group soon?
C) How long do you want to stay at this company?
If you answer yes to the first two, then you probably want multi-tenant.
在我工作的情况下，出于监管/法律原因，我们必须将每个客户的数据保存在单独的数据库中。然而，有一些信息必须共享，主要与客户端 URL 对应于哪个数据库的查找表等相关。此外，如果客户希望以某种逻辑方式分离数据，则可以选择拥有多个数据库。因此，对于我们的每个产品，我们实际上拥有三种类型的数据库：
现在，需要注意的是：对于 MasterData 和 ClientData，我们实际上使用相同的架构，并且通常使用相同的实际数据库。这是出于历史原因，因为允许客户端拥有一个与多个 ClientData 数据库相对应的身份验证数据库 (MasterData) 的功能是一项相对较新的功能，仅适用于我们的一款产品。此外，这种结构还简化了部署，因为大多数客户端仅使用一个 ClientData 数据库。然而，在我们的项目中，MasterData和ClientData在Entity Framework下有独立的实体模型，我们要保证MasterData和ClientData之间不存在外键等直接关系。
这个设置对我们来说非常有效。一大优点是，将不同的ClientData数据库放在不同的服务器上没有问题。这对负载平衡有很大帮助，并且提供了一种自然的数据分区方式。如果客户愿意付费，我们基本上可以为拥有大量数据的客户提供专用的数据库服务器。
在这种情况下，另一件事对我们真正有帮助是 Red Gate 的工具，特别是多脚本、SQL 源代码控制和架构比较等工具。当我们升级某些东西并且架构发生变化时，我们必须将更改部署到所有相关数据库。这些工具节省的时间远远超出了它们本身的价值。请注意，除了作为满意的用户之外，我与 Red Gate 没有任何关系。
编辑：（回应评论）
ApplicationData 是每个产品一个数据库。我们使用的三个基于 Web 的产品对 ApplicationData 使用相同的架构，因为它们记录的信息类型基本相同。然而，它没有理由必须保持这种状态。 ApplicationData 数据库都位于同一服务器上。 ApplicationData 中的表之一指向客户端 MasterData 的正确服务器和数据库名称，因此给定客户端的 MasterData 可以驻留在任何服务器上。
MasterData 具有每个 ClientData 数据库的服务器和数据库名称信息，因此数据库可以驻留在任何服务器上。实际上，目前我们只有两台用于这些产品的生产数据库服务器。每个产品的 MasterData 架构都是相似的，但我认为它们并不完全相同（我必须检查）。每个客户都有自己的主数据。如果客户购买多个产品，则该客户的每个产品都有一个主数据；如果客户购买了该功能（或请求自定义开发此类功能），则产品会以其他方式（基本上通过 Web 服务）进行交互。给定产品的 ClientData 始终具有相同的架构。
因此，总而言之：
因为我们的产品中只有一个支持每个客户端多个 ClientData 实例 。对于第二个产品，这可能最终会被实现，对于第三个产品来说，它作为一个功能根本没有任何意义，
我希望它能回答你的问题！
I work in a situation where, for regulatory/legal reasons, we have to keep each client's data in a separate database. However, there is certain information that must be shared, mostly related to things like a lookup table for which client's URL corresponds to which database. Also, a client can choose to have multiple databases if they wish to separate their data in some logical way. So, for each of our products, we really have three types of databases:
Now, one caveat: We actually use the same schema, and often the same actual database, for MasterData and ClientData. This is for historical reasons, as the ability to allow a client to have one authentication database (MasterData) corresponding to a number of ClientData databases is a relatively new feature that only applies to one of our products. Also, this structure simplifies deployment, since most clients only use one ClientData database. However, MasterData and ClientData have separate entity models under Entity Framework in our projects, and we have to ensure that there are no direct relationships between MasterData and ClientData such as foreign keys.
This setup works pretty well for us. One major advantage is that there is no problem with putting different ClientData databases on different servers. This helps greatly with load balancing, and it provides a natural way to partition data. We can essentially offer a client with a huge amount of data a dedicated database server if they are willing to pay for it.
One more thing that has really helped us in this situation are Red Gate's tools, specifically tools like Multi-Script, SQL Source Control, and Schema Compare. When we upgrade something, and the schema changes, we have to deploy the changes to all the relevant databases. These tools have more than paid for themselves in time saved. Note that I have no affiliation with Red Gate other than as a satisfied user.
Edit: (in response to comment)
ApplicationData is one database per product. The three web-based products we have use the same schema for ApplicationData, since they record basically the same types of information. However, there is no reason it would have to stay that way. The ApplicationData databases are all on the same server. One of the tables in ApplicationData points to the correct server and database name for the client's MasterData, so MasterData for a given client can reside on any server.
MasterData has server and database name information for each ClientData database, so again, the databases can reside on any server. In practice, for now, we only have two production database servers total for these products. The MasterData schema is similar per product, but I do not think they are exactly the same (I would have to check). Each client has its own MasterData. If a client purchases multiple products, there is a MasterData for each product for that client; the products interact in other ways (through web services, basically) if a client has purchased that feature (or requests custom development of such a feature. ClientData for a given product always has the same schema.
So, in summary:
I did oversimplify slightly in that only one of our products supports multiple ClientData instances per client. For a second product, that will probably be implemented eventually. For a third product, it would make no sense at all as a feature and will likely just remain as is.
I hope that answers your question!
好吧，如果趋势是在不同分支机构之间共享信息和数据，那么拥有一个中央数据库可能会更好。
否则，为了实现共享数据的能力而必须经历的困难将比共享数据库所需的额外 WHERE 子句糟糕得多。
当然，您可以为每个分支拥有一个数据库和一个额外的数据库（目前是第四个数据库）作为需要共享的信息的集中存储。尽管您必须看看过度复杂化是否会使其成为两全其美的解决方案：）
Well, if the tendency is towards sharing information and data among different branches you're probably better off having one central database.
Otherwise the hoops you'll have to go through to achieve the ability to share data will be far worse than the extra WHERE clauses needed for a shared DB.
You could, of course, have a DB per branch and an extra database (a fourth database as of now) as a centralized storage for the information that needs sharing. Although you'd have to see if the over-complication makes this a best or worst of both worlds solution :)
如果我们谈论的是 CRM，那么一个客户存在于多个数据库中的可能性有多大？如果您被要求合并跨分支机构的客户详细信息，哪怕只有一丁点机会，那么我肯定会选择一个集中式数据库。
If we're talking about CRM, then what are the chances of one customer being in multiple databases? If there's even the slightest chance of you being asked to combine customer details across branches then I'd definitely go with one centralised database.
IMO 去中心化正在成为可维护和可扩展设计的租户。我使用的唯一集中式数据库是用于身份验证的安全性，目前我正在将其发展为用于授权的去中心化数据库。因此，所有授权都可以保持在与应用程序物理位置相同的边缘，无需网络遍历，因为授权不适合缓存。
读到您对同一应用程序的多个分支特别感兴趣，而不是真正不同的应用程序，这听起来是一个不错的选择，那就是围绕种子过程构建数据库（实体框架支持此），这将允许您只部署将新的分支代码转移到 ASP.NET，然后在初始构建数据库期间物理创建表，您轮询“有福的”服务器并将所有需要的数据转储到边缘服务器。
此后，如果新产品被添加到主数据存储并且这些产品预计将进入每个边缘存储，则您将需要一些复制设置。您可以通过直接复制数据库或查看 Microsoft Sync 框架等工具来实现此目的。
IMO decentralization is becoming a tenant of maintainable and scalable design. The only centralized database I use is for security for authentication, which I'm currently growing into a decentralized database for authorization. So all authorization can stay at the same edge as the application physically sits with no network traversals since authorization is not a great candidate for caching.
Reading that you're specifically interested in multiple branches of the same application as opposed to truly disparate applications, it sounds like a great option would be to build your database around a seeding process (Entity Framework supports this) that would allow you to just deploy your new branch code to ASP.NET, and then during the initial build up the database that the tables are physically created that you poll the "blessed" server and dump all needed data to the edge server.
After this you would need some replication setup if new products are being added to the primary data store and those are expected to make it to each edge store. You could accomplish this with direct replication of your database or look at tools like the Microsoft Sync framework.
今天您可能认为您只会拥有几个客户，但几年后您可能会意识到该产品有潜力销售给数百个客户。如果发生这种情况，您将会后悔使用单租户方法。
比较以下方面的成本：
转换生产系统是一项艰巨且非常昂贵的任务。
使用第二种方法最初可能会花费更多，但它确实为您提供了一个非常有价值的选择，能够在未来以低成本添加更多客户。该选项的价格可能是值得付出的。
You may think today that you will only have a few customers, but a few years from now you may realize that the product has the potential to be sold to hundreds of customers. If that happens you will regret that you used a single-tenant approach.
Compare the costs of:
Converting a production system is a daunting and very expensive task.
Using the second approach may cost you more initially, but it does give you a very valuable option to be able to add more customers in the future at low cost. The price of that option could be worth paying.