多个客户端的SQL数据库共享 [英] SQL Database Sharing For Multiple Clients
问题描述
我正在创建一个具有SQL Server后端的IPad应用程序。我的问题是，我是否更好地为每个客户注册时创建一个新数据库，或者我应该创建一个SQL脚本，在客户注册时为其生成新表。
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.
例如场景1每个用户一个数据库。对于100个用户，sql server将具有100个单独的db。即User1_DB，User2_DB ......
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......
场景2每个用户的新表集。对于100个用户，sql server将在同一个数据库中拥有100个单独的表集。例如MyIPad数据库 - Table1_User1，Table2_User1，Table1_User2，Table2_User2
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?
提前致谢
推荐答案
我想说，除非你明确需要这么多分离，否则我建议使用一组表并使用带有客户ID的客户表来区分客户数据。您仍然可以通过存储过程限制对数据库的访问，这样客户就无法看到其他客户的数据，但您不必处理多个数据库或数千个表的所有开销。
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.
在SO上也有类似的问题，他们遵循我的基本建议。基本上，Wordpress从一个数据库开始，但随着他们扩大规模，他们最终每个客户使用一个数据库，这样他们也可以扩展。这是链接：
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:
这篇关于多个客户端的SQL数据库共享的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
