I'm writing an application that that I'm going to provide as a service and also as a standalone application. It's written in Zend Framework and uses MySQL.
我正在编写一个应用程序，我将作为服务提供，也作为独立的应用程序提供。它是用Zend Framework编写的，并使用MySQL。
When providing it as a service I want users to register on my site and have subdomains like customer1.mysite.com, customer2.mysite.com.
在将其作为服务提供时，我希望用户在我的网站上注册并拥有customer1.mysite.com，customer2.mysite.com等子域名。
I want to have everything in one database, not creating new database for each user.
我希望将所有内容都放在一个数据库中，而不是为每个用户创建新的数据库。
But now I wonder how to do it better. I came up with two solutions: 1. Have user id in each table and just add it to WHERE clause on each database request. 2. Recreate tables with unique prefix like 'customer1_tablename', 'customer2_tablename'.
但现在我想知道如何做得更好。我提出了两个解决方案：1。在每个表中都有用户ID，并在每个数据库请求中将其添加到WHERE子句中。 2.重新创建具有唯一前缀的表，例如'customer1_tablename'，'customer2_tablename'。
Which approach is better? Pros and cons? Is there another way to separate users on the same database?
哪种方法更好？优点和缺点？是否有另一种方法可以在同一个数据库中分隔用户？
Leonti
Leonti
4 个解决方案
#1
3
I would stick to keeping all the tables together, otherwise there's barely any point to using a single database. It also means that you could feasibly allow some sort of cross-site interaction down the track. Just make sure you put indexes on the differentiating field (customer_number or whatever), and you should be ok.
我会坚持把所有的表保持在一起，否则几乎没有任何意义上使用单个数据库。这也意味着您可以在轨道上实现某种跨站点交互。只要确保你在差异化字段（customer_number或其他）上放置索引，你应该没问题。
If the tables are getting really large and slow, look at table partitioning.
如果表变得非常大且速度慢，请查看表分区。
#2
1
It depends on what you intend to do with the data. If the clients don't share data, segmenting by customer might be better; also, you may get better performance.
这取决于您打算如何处理数据。如果客户不共享数据，客户细分可能会更好;另外，你可能会获得更好的表现。
On the other hand, having many tables with an identical structure can be a nightmare when you want to alter the structure.
另一方面，当您想要改变结构时，拥有许多具有相同结构的表可能是一场噩梦。
#3
1
I'd recommend using separate databases for each user. This makes your application easier to code for, and makes MySQL maintenance (migration of single account, account removal and so on.)
我建议为每个用户使用单独的数据库。这使您的应用程序更容易编码，并使MySQL维护（单个帐户的迁移，帐户删除等）。
The only exception to this rule would be if you need to access data across accounts or share data.
此规则的唯一例外是，您需要跨帐户访问数据或共享数据。
#4
1
This is called a multi-tenant application and lots of people run them; see
这被称为多租户应用程序，很多人运行它们;看到
多租户标签
For some other peoples' questions
对于其他一些民族的问题
#1
3
I would stick to keeping all the tables together, otherwise there's barely any point to using a single database. It also means that you could feasibly allow some sort of cross-site interaction down the track. Just make sure you put indexes on the differentiating field (customer_number or whatever), and you should be ok.
我会坚持把所有的表保持在一起，否则几乎没有任何意义上使用单个数据库。这也意味着您可以在轨道上实现某种跨站点交互。只要确保你在差异化字段（customer_number或其他）上放置索引，你应该没问题。
If the tables are getting really large and slow, look at table partitioning.
如果表变得非常大且速度慢，请查看表分区。
#2
1
It depends on what you intend to do with the data. If the clients don't share data, segmenting by customer might be better; also, you may get better performance.
这取决于您打算如何处理数据。如果客户不共享数据，客户细分可能会更好;另外，你可能会获得更好的表现。
On the other hand, having many tables with an identical structure can be a nightmare when you want to alter the structure.
另一方面，当您想要改变结构时，拥有许多具有相同结构的表可能是一场噩梦。
#3
1
I'd recommend using separate databases for each user. This makes your application easier to code for, and makes MySQL maintenance (migration of single account, account removal and so on.)
我建议为每个用户使用单独的数据库。这使您的应用程序更容易编码，并使MySQL维护（单个帐户的迁移，帐户删除等）。
The only exception to this rule would be if you need to access data across accounts or share data.
此规则的唯一例外是，您需要跨帐户访问数据或共享数据。
#4
1
This is called a multi-tenant application and lots of people run them; see
这被称为多租户应用程序，很多人运行它们;看到
多租户标签
For some other peoples' questions
对于其他一些民族的问题