多租户PHP SaaS-每个客户端使用单独的数据库，还是对它们进行分组? [英] Multi-tenant PHP SaaS - Separate DB's for each client, or group them?

问题描述

您可能需要在这里忍受一些错误的术语，因为我什至都不知道它属于整个多租户"软件即服务"类别，但是在这里会.

You'll have to bear with me here for possibly getting some of the terminology slightly wrong as I wasn't even aware that this fell into the whole 'multi-tenant' 'software as a service' category, but here it does.

我已经为客户开发了会员系统(用PHP).现在，我们正在考虑将其作为其他客户的完全托管解决方案，提供一个子域(甚至他们自己的域).

I've developed a membership system (in PHP) for a client. We're now looking at offering it as a completely hosted solution for our other clients, providing a subdomain (or even their own domain).

就数据存储而言，我似乎在表上拥有的选项是:

The options I seem to have on the table, as far as data storage goes are:

选项1 -将所有内容存储在1个大数据库中，并在需要该表的表上有一个"client_id"字段(该表将应用于约30个表)，并具有一个客户"表存储其主要设置，详细信息等以及要映射到其的域.然后，这只是设置一个包含其各自的客户端ID的全局可访问变量-我显然必须修改每个查询以检查client_id列.

Option 1 - Store everything in 1 big database, and have a 'client_id' field on the tables that need it (there would be around 30 tables that it would apply to), and have a 'clients' table storing their main settings, details, etc and the domain to map to them. This then just sets a globally accessible variable containing their individual client id - I'd obviously have to modify every single query to check for the client_id column.

选项2 -具有一个包含共享参考"表和客户"表的主表.然后有其他数据库的块"，每个数据库包含10个客户端.客户端将获得自己的数据库表，并以其客户端ID为前缀.这样会增加一点安全性，以防万一真的出了问题，防止看到其他客户端数据.

Option 2 - Have a master table with the 'shared reference' tables, and the 'clients' table. Then have 'blocks' of other databases, which each contain, say 10 clients. The client would get their own database tables, prefixed with their client ID. This adds a little bit of security to protect against seeing other client data if something went really wrong.

选项3 -与选项2完全相同，不同之处在于每个客户端都有1个数据库，将它们与其他客户端完全隔离，并且从理论上讲，如果有1个客户端表，则可以提供更多的保护被黑客入侵或以其他方式损坏，不会影响其他任何人.最大的缺点是，在部署新客户端时，需要设置整个数据库，用户和密码，等等.这是否可能还会导致相当多的开销，或者就像您将每个人都放在一个桌面上一样?数据库?

Option 3 - Exactly the same as option 2, except you have 1 database for each and every client, completely isolating them from other clients, and theoretically providing a bit more protection that if 1 client's tables were hacked or otherwise damaged, it wouldn't affect anyone else. The biggest downside is that when deploying a new client, an entire database, user and password need setting up, etc. Could this possibly also cause a fair amount of overhead, or would it be pretty much the same as if you had everyone in one database?

还有几点-这些客户中的一些将拥有5000+个客户"以及这些客户的所有详细信息-这就是为什么选项1可能会有点问题的原因-如果我有100个客户，可能等于1个表中超过500万行.

A few points as well - some of these clients will have 5000+ 'customers' along with all the details for those customers - this is why option 1 may be a bit of an issue - if I've got 100 clients, that could equal over half a million rows in 1 table.

我正确地认为，在客户数据(和付款信息)的安全性是关键的情况下，选择3是最好的方法.根据我的建议，有些人说选择1是因为更容易"，但是我真的不这么认为.我认为这是一个潜在的瓶颈，因为如果他们拥有自己的数据库，我当然可以使客户端迁移变得容易得多.

Am I correct in thinking Option 3 would be the best way to go in a situation where security of customer data (and payment information) is key. From recommendations I've had, a few people have said to go with option 1 because 'its easier' however I really don't see it that way. I see it as a potential bottleneck down the line, as surely I can move clients around much easier if they have their own database.

(仅供参考，系统是基于MySQL的PHP​​)

推荐答案

选项3具有最大的可扩展性.虽然乍一看似乎比较复杂，但它可以完全自动化，并且可以免去您对未来的麻烦.您还可以通过将客户端数据库放在多个服务器上来提高性能，从而更有效地扩展规模.

Option 3 is the most scalable. While at first it may seem more complicated, it can be completely automated and will save you headaches on the future. You can also scale more efficiently by having client databases on multiple servers for increased performance.

这篇关于多租户PHP SaaS-每个客户端使用单独的数据库，还是对它们进行分组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！