多个客户端的 SQL 数据库共享
我正在创建一个 iPad 应用程序，它将有一个 SQL Server 后端。我的问题是，我是在每个客户注册时为他们创建一个新数据库更好，还是应该创建一个 SQL 脚本在客户注册时为他们生成新表。
例如，场景 1 每个用户一个数据库。对于 100 个用户，sql server 将有 100 个独立的数据库。即User1_DB、User2_DB……
场景2 每个用户的新表集。对于 100 个用户，sql server 将在同一数据库中拥有 100 个独立的表集。例如，MyiPad 数据库 - Table1_User1、Table2_User1、Table1_User2、Table2_User2
在这两种情况下，我们都会有自动脚本，在用户注册时生成表/数据库。
所以我的问题是什么场景更有效？或者这两种情况都是错误的，那么我应该如何处理这个问题？
提前致谢
Im creating an IPad Application which will have an SQL Server backend. My question is, is it better for me to create a new database for each customer as they sign up or should I create an SQL script that generates new tables for the customer when they sign up.
e.g. Scenario 1 One database per user. For 100 users the sql server will have 100 seperate db's. i.e User1_DB, User2_DB......
Scenario 2 New set of tables for each User. For 100 users the sql server will have 100 seperate sets of the tables in the same database. e.g. MyIPad Database - Table1_User1, Table2_User1, Table1_User2, Table2_User2
In both scenarios we will have automated scripts that would generate the tables/Database when the user signs up.
So my question is what scenario is more efficient? Or are both scenario's wrong, therefore how should i approach this issue?
Thanks in advance
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我想说，除非您明确需要进行如此多的分离，否则我建议使用一组表并使用带有客户 ID 的客户表来区分客户数据。您仍然可以通过存储过程限制对数据库的访问，以便任何客户都无法看到其他客户的数据，但您不必处理多个数据库或数千个表的所有开销。
但是，如果必须分离，请使用不同的数据库。数据库分离使您能够更好地控制数据安全。如果您必须竭尽全力为每个客户使用不同的对象，那么您希望单独的数据库具有完全的安全性。这也将为您提供将一个客户转移到不同服务器等所需的可移植性。
已经有一个类似的问题了，他们遵循了我的基本建议。基本上，Wordpress 一开始使用一个数据库，但随着规模的扩大，他们最终为每个客户使用一个数据库，以便他们也可以横向扩展。这是链接：
PHP Web 应用程序：mysql 数据库设计最佳实践问题
I would say that unless you have an explicit need to have this much separation, I would suggest having one set of tables and using a customer table with a customer ID to differentiate customer data. You can still limit access to the database through stored procedures so that no customer can see another customer's data but you don't have to deal with all of the overhead of multiple databases or thousands of tables.
If you must have separation, though, use different databases. Database separation gives you the ability to have more control over data security. If you have to go to the lengths of using different objects for each customer, you want the full security of separate databases. This will also give you the portability necessary for moving one customer to a different server, etc.
There is a similar question on SO already, and they followed my basic advice. Basically, Wordpress started out with one database but as they scaled up they ended up going with one database per customer so that they could scale out as well. Here is the link:
PHP Web Application: mysql database design best practices question
我根本不会走这条路线，而是在行级别执行此操作，每个表的每一行都与特定用户（或公司/用户，如果应用程序可以为拥有许多用户的特定公司注册） ）。对于任何大量用户来说，创建单独的表/数据库根本无法扩展。
I wouldn't go this route at all, but rather do it at the row level, with each row of each table being tied to a specific user (or company/user, if the app can be registered for a particular company with many users). Creating separate tables/databases is simply not scalable for any significant number of users.