DB设计：一个大DB用于所有客户或许多小DB [英] DB design: one large DB for all customers or many small DBs

问题描述

寻找任何建议或建议，甚至是最佳做法。
我使用php和mysql开发了一个在线数据库。它允许公司记录投诉和决议等。有一个用于登录的用户数据库和一个用于记录主数据的cip数据库。 2家公司正在试验和测试数据库。目前每家公司都使用单独的数据库和单独的html页面。我想知道什么是最好的方式添加更多的公司。

我有的想法是：

Looking for any suggestions or advise or even best practices. I have developed an online database using php and mysql. It allows companies to log complaints and resolutions etc. There is a user database for login and a cip database for logging the main data. 2 companies are trialing and testing the database. At the moment each company is using separate databases and separate html pages. I am wondering what the best way to add more companies.
Ideas that I have are:

1。
有一个大型数据库用于用户，一个大型数据库用于cip，并使用公司标识或类似标识来标识数据库中的单个公司记录。

1. To have one large database for users and one large database for cip and use a company id or similar to identify individual companies records in the database.

2。
为每个公司使用相同的html页面，但从登录详细信息中选择要使用的数据库。因此，每个公司将有一个单独的cip数据库，但所有公司使用相同的用户数据库。

2. To use the same html pages for every company but select which database to use from the login details. So that each company will have a separate cip database but all companies use the same users database.

3。
或者保持每个公司的一切。 （这可能对于更新是非常不好的）

3. Or just keep everything separate for every company. (this might be really bad for doing updates)

我希望我已经清楚了，期待任何建议。

I hope I have made myself clear and look forward to any suggestions.

感谢

推荐答案

我想回答的一个问题是，你需要查看自己的报告数据或使用？在这种情况下，你需要去与第一，否则你将有一个噩梦得到良好的报告。

One question I would want answered is do you ever need to see data across cutomers for your own reporting or use? In this case, you need to go with number one or you will have a nightmare to get good reporting.

您会做客户的任何定制吗？这表明分离出来可能是一个更好的选择。如果你永远不会定制，那么不要分开。

Will you be doing any customization by customer? This would indicate that separating things out might be a better choice. If you will never customize, then don't separate out.

我已经在所有这些选项中使用系统，第一个是目前为止最适合长期维护的系统。然而，如果你组织和计划好，所有都是可行的。如果您要查看单独的otion，您必须能够将更改推送到所有客户端，因此必须通过源代码控制中保存的脚本对数据库进行更改。您甚至可能需要通过数据库版本保留源代码控制，以便客户端可以选择升级或不升级。在选项1当然，没有人有选择留在旧版本。如果这更适合你的业务需求，这是选项1的加号。

I have worked with systems in all of these options and the first one is by far the best for long term maintenance. However all are workable if you are organizaed and plan well. If you go for the separate otion, you must be able to push changes to all clients and thus must be doing changes to the database through scripts that are kept in source control. You may even need to keep a source control by database version, so that clients can choose to upgrade or not. In option 1 of course, no one has the option to stay on the old version. If that fits your business needs better, that is a plus for option 1.

我非常同意Ollie Jones，如果你使用选项一，你必须有一个良好的数据库安全设计，以防止客户端看到其他客户端的数据。我们曾经将一个客户端从一个服务器（他们是唯一的客户端）移动到一个共享数据库，只有一个错过了client_ID的请求（这在旧系统中是不需要的，开发人员已经变得很麻烦）所有其他客户的销售代表都有关于第一个客户的信息。这会花费公司很多钱（既解决问题，发送电子邮件道歉，我们几乎失去了一个客户端，结果，并给他们一些成本休息，以保持他们）和许多抱歉道歉和开发人员狭义失去了他的工作。让这是一个教训，你不会学习困难的方式。

I strongly agree with Ollie Jones, if you use option one, you must have a good database security design to prevent clients from seeing the data of other clients. We once moved a client from a server where they were the only client to a shared database and just one proc that missed asking for the client_ID (It wasn't needed in the old system and the developers had gotten sloppy) ended up emailing all the sales reps of all the other clients with information about the first client. This cost the company a lot of money (both to fix the problem, to send out email apologies and we almost lost a client as a result and had to give them some cost breaks to keep them) and many grovelling apologies and the developer just narrowly missed losing his job. Let this be a lesson you don't learn the hard way.

这篇关于DB设计：一个大DB用于所有客户或许多小DB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！