最大表数设计模式
我现在正在开发一个应用程序，它有潜力变得相当大。整个应用程序通过单个域运行，并为客户提供子域，这意味着这一切当然都通过通用代码库运行。
我正在努力解决的是数据库设计。我不确定在每个表中有一列指定客户 ID 更好，还是创建一组新表（在同一数据库中）更好，或者为每个客户创建一个完整的新数据库是否更好。
数据库中指定客户 ID 的“标志”的好处是所有内容都位于一个位置。缺点是显而易见的——表可能会变得很大，维护可能会变成一场噩梦。如果出现增长，将其分散到多台服务器上将是一个巨大的痛苦。
创建新表的好处是很容易做到，并且还可以使表保持很小。由于客户数据不需要交互，因此不存在任何问题。但同样，维护可能会成为一个问题（尽管我确实有一个迁移库，可以为每个客户动态更新，所以这没什么大不了的）。另一个问题是我不知道一个数据库中可以有多少个表。有谁知道限制是什么，以及性能问题是什么？
为每个客户创建一个新数据库的好处是，当我需要扩展时，我将能够做得很好。有几个网站使用了这种设计（wordpress.com 等）。它已被证明是有效的，但也有一些缺点。
所以，基本上我只是在寻找一些关于我应该（可以）走哪个方向的建议。
I am working on an app right now which has the potential to grow quite large. The whole application runs through a single domain, with customers being given sub-domains, which means that it all, of course, runs through a common code-base.
What I am struggling with is the database design. I am not sure if it would be better to have a column in each table specifying the customer id, or to create a new set of tables (in the same database), or to create a complete new database per customer.
The nice thing about a "flag" in the database specifying the customer id is that everything is in a single location. The downfalls are obvious- Tables can (will) get huge, and maintenance can become a complete nightmare. If growth occurs, splitting this up over several servers is going to be a huge pain.
The nice thing about creating new tables it is easy to do, and also keeps the tables pretty small. And since customers data doesn't need to interact, there aren't any problems there. But again, maintenance might become an issue (Although I do have a migrations library that will do updates on the fly per customer, so that is no big deal). The other issue is I have no idea how many tables can be in a single database. Does anyone know what the limit is, and what the performance issues would be?
The nice thing about creating a new database per customer, is that when I need to scale, I will be able to, quite nicely. There are several sites that make use of this design (wordpress.com, etc). It has been shown to be effective, but also have some downfalls.
So, basically I am just looking for some advice on which direction I should (could) go.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
单一数据库的优点
每个客户单独的数据库 优点
结论
如果您计划支持客户定制，那么单独的数据库方法将是有效的。否则，我不认为安全性是一个大问题 - 如果有人获得数据库凭据，您真的认为他们不会看到该服务器上的其他数据库吗？
Single Database Pros
Separate Database per Customer Pros
Conclusion
The separate database approach would be valid if you plan to support customer customization. Otherwise, I don't see the security as a big issue - if someone gets the db credentials, do you really think they won't see what other databases are on that server?
多个数据库。
不同的客户会有不同的需求，这将使您能够更好地为他们服务。
此外，如果某个特定客户正在攻击数据库，您不希望这对所有其他客户的站点性能产生负面影响。如果所有内容都在一个数据库上，那么就没有损害控制机制。
Multiple Databases.
Different customers will have different needs, and it will allow you to serve them better.
Furthermore, if a particular customer is hammering the database, you don't want that to negatively affect the site performance for all your other customers. If everything is on one database, you have no damage control mechanism.
使用单独的数据库，客户之间意外共享数据的风险要小得多。如果您希望将所有数据集中在一处（例如用于报告），请设置客户无法访问的报告数据库。
单独的数据库允许您仅针对一位客户推出并测试错误修复。
MySQL 中的表数量没有限制，您可以创建数量惊人的表。不过，我认为每个数据库超过一百个表的任何内容都是维护噩梦。
The risk of accidentally sharing data between customers is much smaller with separate database. If you'd like to have all data in one place, for example for reporting, set up a reporting database the customers cannot access.
Separate databases allow you to roll out, and test, a bugfix for just one customer.
There is no limit on the amount of tables in MySQL, you can make an insane amount of them. I'd call anything above a hundred tables per database a maintenance nightmare though.
您打算开发云应用程序吗？
我认为你不需要由客户制作表格或数据库。我建议您使用更具可扩展性的关系数据库管理系统。就我个人而言，我不知道 MySQL 的功能，但我很确定它应该支持分布式数据库模型以处理负载。
为每个客户创建表或数据库可能会给您带来维护噩梦。
我曾使用过多个公司的数据库，每个表都包含客户 ID，为了访问其数据，我们为每个客户开发视图（用于报告目的）
祝你好运，
Are you planning to develop a Cloud App?
I think that you don´t need to make tables or data bases by customer. I recommend you to use a more scalable relational database management system. Personally I don´t know the capabilities of MySQL, but i´m pretty sure that it should support distributed data base model in order to handle the load.
creating tables or databases per customer can lead you to a maintenance nightmare.
I have worked with multi-company databases and every table contains customer ids and to access its data we develop views per customer (for reporting purposes)
Good luck,
你可以做任何你想做的事。
如果您在每一列中都有 customer_id，那么您必须以这种方式编写整个应用程序。这并不完全正确，因为应该足以将该列仅添加到某些表中，其余的可以使用一些简单的联接来完成。
如果每个用户有一个数据库，则应用程序中不会有任何额外的代码，这样会更容易。
如果每个
如果您采用第一种方法，那么迁移到许多数据库不会有问题，因为您可以在所有这些表中拥有 customer_id 列。当然，每个表中的该列中都会有相同的值，但这不是问题。
就我个人而言，我会采用简单的一客户一数据库方法。为所有客户使用更多数据库服务器更容易，显示属于其他客户的客户数据更困难。
You can do whatever you want.
If you've got the customer_id in each column, then you've got to write the whole application that way. That's not exactly true as there should be enough to add that column only to some tables, the rest could be done using some simple joins.
If you've got one database per user, there won't be any additional code in the application so that could be easier.
If you take to first approach there won't be a problem to move to many databases as you can have the customer_id column in all those tables. Of course then there will be the same value in this column in each table, but that's not a problem.
Personally I'd take the simple one customer one database approach. Easier to user more database servers for all customers, more difficult to show a customer data that belongs some other customer.