PHP Web应用程序：mysql数据库设计最佳实践问题 [英] PHP Web Application: mysql database design best practices question

问题描述

我正在与同事讨论有关我们正在创建的PHP Web应用程序的数据库设计的最佳实践。该应用程序是为企业设计的，每个注册的公司将有多个用户使用该应用程序。

我的设计方法是为每个签署的公司创建一个新的数据库向上。这样一来，沙箱，模块化，小型化。我的同事的理念是把每个人都放在一个数据库中。他的论点是，如果我们有1000多家公司注册，我们可以用1000多个数据库来处理。更不用说做商业智能的混乱。

为了举例，假设应用程序是订单输入系统。使用单独的数据库，即使每个公司每天都在执行100多个订单，表格大小也可以保持管理。在单桶应用程序中，表格可以非常快速地获得。

是否有最佳做法？我试图在网络上狩猎，但没有太多的成功。

提前感谢

The1Rob

$ b $我从WordPress的数据库架构师那里谈到了WordPress的主机服务。他表示，他们开始使用一个数据库，一起托管所有的客户。一个博客网站的内容真的不是那么多。这是理所当然的，一个数据库是更易于管理的。

这对他们来说很好，直到他们有成千上万的客户，他们意识到他们需要扩展，运行多个物理服务器并在每个服务器上托管其客户的一部分。当他们添加服务器时，将个人客户迁移到新服务器是很容易的，但是更难以在属于单个客户博客的单个数据库中分离数据。

同样做一个包含数据数据的单个数据库的数据库备份或恢复，而个别数据库备份和恢复每个几兆字节是一个重要因素。考虑一下：客户打电话说，由于数据输入不正确，他们的数据SNAFU，您是否可以从昨天的备份恢复数据？如果您的所有客户共享一个数据库，您如何还原客户的一个数据？

最终，他们决定将分割成一个<​​strong>单独的数据库每个客户，虽然管理复杂，为他们提供了更大的灵活性，并将他们的托管服务重新设计为这种模式。

所以， >数据建模透视图，将所有内容保存在单个数据库中似乎是正确的事情，当您传递数据量的某个断点时，某些数据库管理任务变得更加容易。 >

I am currently in a debate with a coworker about the best practices concerning the database design of a PHP web application we're creating. The application is designed for businesses, and each company that signs up will have multiple users using the application.

My design methodology is to create a new database for every company that signs up. This way everything is sand-boxed, modular, and small. My coworkers philosophy is to put everyone into one database. His argument is that if we have 1000+ companies sign up, we wind up with 1000+ databases to deal with. Not to mention the mess that doing Business Intelligence becomes.

For the sake of example, assume that the application is an order entry system. With separate databases, table size can remain manageable even if each company is doing 100+ orders a day. In a single-bucket application, tables can get very big very quickly.

Is there a best practice for this? I tried hunting around the web, but haven't had much success. Links, whitepapers, and presentations welcome.

Thanks in advance,

The1Rob

解决方案

I talked to the database architect from wordpress.com, the hosting service for WordPress. He said that they started out with one database, hosting all customers together. The content of a single blog site really isn't that much, after all. It stands to reason that a single database is more manageable.

This did work well for them until they got hundreds and thousands of customers, they realized that they needed to scale out, running multiple physical servers and hosting a subset of their customers on each server. When they add a server, it would be easy to migrate individual customers to the new server, but harder to separate data within a single database that belongs to an individual customer's blog.

As customers come and go, and some customers' blogs have high-volume activity while others go stale, the rebalancing over multiple servers becomes an even more complex maintenance job. Monitoring size and activity per individual database is easier too.

Likewise doing a database backup or restore of a single database containing terrabytes of data, versus individual database backups and restores of a few megabytes each, is an important factor. Consider: a customer calls and says their data got SNAFU'd due to some bad data entry, and could you please restore the data from yesterday's backup? How would you restore one customer's data if all your customers share a single database?

Eventually they decided that splitting into a separate database per customer, though complex to manage, offered them greater flexibility and they re-architected their hosting service to this model.

So, while from a data modeling perspective it seems like the right thing to do to keep everything in a single database, some database administration tasks become easier as you pass a certain breakpoint of data volume.

这篇关于PHP Web应用程序：mysql数据库设计最佳实践问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！