I have a web app that I built for a trucking company that I would like to offer as SaaS. What is the best way to design the database?
我有一个web应用程序，我为一家卡车公司建造，我想提供SaaS。设计数据库的最佳方式是什么?
Should I create a new database for each company? Or should I use one database with tables that have a prefix of the company name? Or should I Use one database with one of each table and just add a company id field to the tables? Or is there some other way to do it?
我应该为每个公司创建一个新的数据库吗?还是应该使用带有公司名称前缀的数据库?或者我应该对每个表使用一个数据库，然后在表中添加一个公司id字段?或者有别的方法吗?
5 个解决方案
#1
25
faced with a similar situation about 10 years ago, we opted for a database per client. we have hundreds (not thousands) of clients. looking back it was one of the best decisions we made. backups are easy. copying a single client to our office for analysis is easy (just take the last backup). scaling is easy (moving a single big client to a different server can free up resources on a stressed sql server). joel & jeff had a discussion about this on a stack overflow podcast (not a recent one) and joel did the same thing i do ... each client gets their own database. database purists will often argue for lumping everyone into one db, but i would never do that.
面对大约10年前的类似情况，我们选择了每个客户机的数据库。我们有成百上千的客户。回顾过去，这是我们做出的最好的决定之一。备份是容易的。将单个客户端复制到我们的办公室进行分析是很容易的(只需进行最后一次备份)。扩展很容易(将一个大客户端移动到另一个服务器可以释放压力sql服务器上的资源)。joel和jeff在stack overflow播客上讨论过这个问题(不是最近的)，joel做了和我一样的事情……每个客户机都有自己的数据库。数据库纯粹主义者通常会主张将每个人都归为一个db，但我绝不会这么做。
-don
——请
#2
9
Should I create a new database for each company?
我应该为每个公司创建一个新的数据库吗?
Yes - Don Dickinson was on the money. However, see a refinement below.
是的，唐·狄金森在赚钱。但是，请参见下面的细化。
Or should I use one database with tables that have a prefix of the company name?
还是应该使用带有公司名称前缀的数据库?
Lord no! Changing your database queries for different for client would make you go insane! Also, you'd almost certainly run dynamic SQL (where the table name is changed in code before running the query), which would harm performance as most servers like to cache query plans and interim results - this doesn't work if the table names keep changing.
主不!为客户更改数据库查询将使您发疯!此外，您几乎肯定会运行动态SQL(其中表名在运行查询之前在代码中被更改)，这将损害性能，因为大多数服务器都喜欢缓存查询计划和临时结果——如果表名不断变化，这将不起作用。
Or should I Use one database with one of each table and just add a company id field to the tables?
或者我应该对每个表使用一个数据库，然后在表中添加一个公司id字段?
You might want to do this if you want to have some kind of scalable model for your customers. Whilst provisioning a new database for each customer gives you lots of flexibility, it also involves costs and complexity. You have to create a new backup schedule, have a lifecycle model for dealing with expired customers etc.
如果您想为您的客户提供某种可伸缩模型，您可能需要这样做。虽然为每个客户提供新数据库给您带来了很大的灵活性，但它也涉及成本和复杂性。您必须创建一个新的备份计划，拥有一个处理过期客户的生命周期模型等等。
So, you might say that "free trial" and "bronze" customers are all lumped into a single database, using the company id to separate them out; "silver" users get their own database (but you still keep the customer_id field in the schema, so you don't have to change queries between two levels of customer), and "gold" customers get their own database server.
因此，您可能会说“免费试用”和“青铜”客户都被合并到一个数据库中，使用公司id将它们分开;“silver”用户可以获得自己的数据库(但您仍然保留了模式中的customer_id字段，因此您不必更改两个级别的客户之间的查询)，而“gold”客户端则拥有自己的数据库服务器。
I did something similar a few years ago at a SaaS company - and customers are typically happy to have an upgrade path on infrastructure (read: performance and resilience) as well as features.
几年前，我在一家SaaS公司做过类似的事情——客户通常都很乐意在基础设施(即:性能和弹性)以及特性上进行升级。
#3
4
What are the advantages of using a single database for EACH client?
为每个客户端使用一个数据库有什么好处?
Should I use a single or multiple database setup for a multi-client application?
我应该为多客户端应用程序使用单个或多个数据库设置吗?
https://*.fogbugz.com/default.asp?W24218 (podcast transcript, discussion around 50 mins in)
https://*.fogbugz.com/default.asp?W24218(播客文字记录，50分钟左右)
#4
2
We have some databases here with shared clients and some where each client has it's own server and own database. The ones where the client is on it's own server are the easiest to manage and the least likely to cause a problem when some developer forgot to add the clientid and sent client a's data to client b by accident (an example NOT chosen at random).
我们这里有一些数据库和共享的客户端，还有一些客户端有自己的服务器和数据库。当某些开发人员忘记添加clientid并意外地将客户端a的数据发送给客户端b时，客户端所在的服务器是最容易管理的，也是最不容易引起问题的。
Keeping each on it's own server or server instance allows us to keep the database structure the same with the same names and makes it easier to propagate changes to all the servers because we don't have to change the database name.
将每个数据库保存在它自己的服务器或服务器实例上，可以使用相同的名称保持数据库结构不变，并使向所有服务器传播更改变得更容易，因为我们不必更改数据库名称。
If you do use separate instances for each client, make sure you design and implement a good system for propagating all changes to all clients. If these databases get out of sync, they can become horrible to maintain. You'll find that if you let them get out of sync, each client will ask for changes and you will have 27 ways to do the same thing. You have to generalize when they are on the same database, when they are separate you have to use self discipline to ensure new functionality is the same for each client.
如果您确实为每个客户端使用单独的实例，请确保您设计并实现了一个良好的系统，用于将所有更改传播到所有客户端。如果这些数据库不同步，它们就会变得难以维护。你会发现如果你让他们不同步，每个客户都会要求改变，你会有27种方法去做同样的事情。当它们在同一个数据库上时，当它们是分开的时，您必须使用self discipline来确保每个客户机的新功能是相同的。
#5
1
It depends, here, i work in a company that has many "Internal Business units" treated like other companies. So, some reports must include all companies, Client accounts must also be shared across companies. Here we have a CompanyId Field in the tables that requires it. The Prefix solution is surely one to be avoided.
这取决于我在这里工作的公司，它有许多“内部业务部门”，和其他公司一样。因此，有些报告必须包括所有公司，客户账户也必须在公司之间共享。在需要它的表中有一个CompanyId字段。前缀解决方案肯定是可以避免的。
#1
25
faced with a similar situation about 10 years ago, we opted for a database per client. we have hundreds (not thousands) of clients. looking back it was one of the best decisions we made. backups are easy. copying a single client to our office for analysis is easy (just take the last backup). scaling is easy (moving a single big client to a different server can free up resources on a stressed sql server). joel & jeff had a discussion about this on a stack overflow podcast (not a recent one) and joel did the same thing i do ... each client gets their own database. database purists will often argue for lumping everyone into one db, but i would never do that.
面对大约10年前的类似情况，我们选择了每个客户机的数据库。我们有成百上千的客户。回顾过去，这是我们做出的最好的决定之一。备份是容易的。将单个客户端复制到我们的办公室进行分析是很容易的(只需进行最后一次备份)。扩展很容易(将一个大客户端移动到另一个服务器可以释放压力sql服务器上的资源)。joel和jeff在stack overflow播客上讨论过这个问题(不是最近的)，joel做了和我一样的事情……每个客户机都有自己的数据库。数据库纯粹主义者通常会主张将每个人都归为一个db，但我绝不会这么做。
-don
——请
#2
9
Should I create a new database for each company?
我应该为每个公司创建一个新的数据库吗?
Yes - Don Dickinson was on the money. However, see a refinement below.
是的，唐·狄金森在赚钱。但是，请参见下面的细化。
Or should I use one database with tables that have a prefix of the company name?
还是应该使用带有公司名称前缀的数据库?
Lord no! Changing your database queries for different for client would make you go insane! Also, you'd almost certainly run dynamic SQL (where the table name is changed in code before running the query), which would harm performance as most servers like to cache query plans and interim results - this doesn't work if the table names keep changing.
主不!为客户更改数据库查询将使您发疯!此外，您几乎肯定会运行动态SQL(其中表名在运行查询之前在代码中被更改)，这将损害性能，因为大多数服务器都喜欢缓存查询计划和临时结果——如果表名不断变化，这将不起作用。
Or should I Use one database with one of each table and just add a company id field to the tables?
或者我应该对每个表使用一个数据库，然后在表中添加一个公司id字段?
You might want to do this if you want to have some kind of scalable model for your customers. Whilst provisioning a new database for each customer gives you lots of flexibility, it also involves costs and complexity. You have to create a new backup schedule, have a lifecycle model for dealing with expired customers etc.
如果您想为您的客户提供某种可伸缩模型，您可能需要这样做。虽然为每个客户提供新数据库给您带来了很大的灵活性，但它也涉及成本和复杂性。您必须创建一个新的备份计划，拥有一个处理过期客户的生命周期模型等等。
So, you might say that "free trial" and "bronze" customers are all lumped into a single database, using the company id to separate them out; "silver" users get their own database (but you still keep the customer_id field in the schema, so you don't have to change queries between two levels of customer), and "gold" customers get their own database server.
因此，您可能会说“免费试用”和“青铜”客户都被合并到一个数据库中，使用公司id将它们分开;“silver”用户可以获得自己的数据库(但您仍然保留了模式中的customer_id字段，因此您不必更改两个级别的客户之间的查询)，而“gold”客户端则拥有自己的数据库服务器。
I did something similar a few years ago at a SaaS company - and customers are typically happy to have an upgrade path on infrastructure (read: performance and resilience) as well as features.
几年前，我在一家SaaS公司做过类似的事情——客户通常都很乐意在基础设施(即:性能和弹性)以及特性上进行升级。
#3
4
What are the advantages of using a single database for EACH client?
为每个客户端使用一个数据库有什么好处?
Should I use a single or multiple database setup for a multi-client application?
我应该为多客户端应用程序使用单个或多个数据库设置吗?
https://*.fogbugz.com/default.asp?W24218 (podcast transcript, discussion around 50 mins in)
https://*.fogbugz.com/default.asp?W24218(播客文字记录，50分钟左右)
#4
2
We have some databases here with shared clients and some where each client has it's own server and own database. The ones where the client is on it's own server are the easiest to manage and the least likely to cause a problem when some developer forgot to add the clientid and sent client a's data to client b by accident (an example NOT chosen at random).
我们这里有一些数据库和共享的客户端，还有一些客户端有自己的服务器和数据库。当某些开发人员忘记添加clientid并意外地将客户端a的数据发送给客户端b时，客户端所在的服务器是最容易管理的，也是最不容易引起问题的。
Keeping each on it's own server or server instance allows us to keep the database structure the same with the same names and makes it easier to propagate changes to all the servers because we don't have to change the database name.
将每个数据库保存在它自己的服务器或服务器实例上，可以使用相同的名称保持数据库结构不变，并使向所有服务器传播更改变得更容易，因为我们不必更改数据库名称。
If you do use separate instances for each client, make sure you design and implement a good system for propagating all changes to all clients. If these databases get out of sync, they can become horrible to maintain. You'll find that if you let them get out of sync, each client will ask for changes and you will have 27 ways to do the same thing. You have to generalize when they are on the same database, when they are separate you have to use self discipline to ensure new functionality is the same for each client.
如果您确实为每个客户端使用单独的实例，请确保您设计并实现了一个良好的系统，用于将所有更改传播到所有客户端。如果这些数据库不同步，它们就会变得难以维护。你会发现如果你让他们不同步，每个客户都会要求改变，你会有27种方法去做同样的事情。当它们在同一个数据库上时，当它们是分开的时，您必须使用self discipline来确保每个客户机的新功能是相同的。
#5
1
It depends, here, i work in a company that has many "Internal Business units" treated like other companies. So, some reports must include all companies, Client accounts must also be shared across companies. Here we have a CompanyId Field in the tables that requires it. The Prefix solution is surely one to be avoided.
这取决于我在这里工作的公司，它有许多“内部业务部门”，和其他公司一样。因此，有些报告必须包括所有公司，客户账户也必须在公司之间共享。在需要它的表中有一个CompanyId字段。前缀解决方案肯定是可以避免的。