SQL Server数据库设计
我计划使用 ASP.NET 和 SQL Server 创建一个网站。然而，我的数据库设计计划让我想知道是否有更好的方法。
该网站将作为各种用户的信息库。我想我会有两个数据库，一个会员数据库和一个个人资料数据库。
配置文件数据库将包含所有用户的用户数据，其中每个用户可能有大约 20 个表。我将在创建用户帐户时创建表并生成用于命名表的密钥。这些表不直接相关。
例如，两个不同用户的一组表可能如下所示：
User1 Tables - TransactionTable_Key1, AssetTable_Key1, ResearchTable_Key1 ....;
User2 Tables - TransactionTable_Key2, AssetTable_Key2, ResearchTable_Key2 ....;
Key1、Key2 等。创建帐户时，将根据 MembershipID 数据检索值。随着时间的推移，这可能会导致出现大量的表。我不确定以这种方式设置数据库是否会限制可伸缩性。有什么建议吗？
编辑：我应该提到其中一些表将包含 20k+ 行。
I am planning to create a website using ASP.NET and SQL Server. However, my plan for the database design leaves me wondering if there is a better way.
The website will serve as a repository of information for various users. I figure I would have two databases, a Membership and Profile database.
The profile database would contain user data for all users, where each user may have ~20 tables. I would create the tables when the user account is created and generate a key used to name the tables. The tables are not directly related.
For Example a set of tables for two different users could look like:
User1 Tables - TransactionTable_Key1, AssetTable_Key1, ResearchTable_Key1 ....;
User2 Tables - TransactionTable_Key2, AssetTable_Key2, ResearchTable_Key2 ....;
The Key1, Key2 etc.. values would be retrieved based on the MembershipID data when the account was created. This could result in a very large number of tables over time. I'm not sure if this will limit scalability by setting up the database in this way. Any recommendations?
Edit: I should mention that some of these tables would contain 20k+ rows.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
您的 ORM 层（EF、LINQ、DAL 代码）将讨厌必须为每个租户处理一组表。最好在单个数据库中为所有租户提供一组表，或者为每个租户拥有单独的数据库。只有当架构升级必须由租户审核时（就像 Salesforce.com 那样），后者才会更好。如果您有能力立即将所有租户升级到新架构，那么就没有理由为每个租户创建数据库。
当您设计容纳多个租户的模式时，要记住的重要事情是
这些是相当简单的规则，如果您遵守它们（无例外），您将获得每个查询的每个租户的完美分区（没有查询会扫描一定范围内的行）不同的租户），这样就可以消除租户之间的争用。要了解更多信息，您可以禁用锁定升级以确保没有租户升级以阻止所有其他租户。
此设计还适用于表分区 并共享数据库以进行横向扩展。
Your ORM layer (EF, LINQ, DAL code) will hate having to deal with one set of tables per tenant. It is much better to have either one set of tables for all tenant in a single database, or a separate database per tenant. The later is only better if schema upgrade has to be vetted by tenant (like Salesforce.com has). If you can afford to upgrade all tenant to a new schema at once then there is no reason for database per tenant.
When you design a schema that hold multiple tenant the important things to remember are
These are fairly simple rules and if you obey them (with no exceptions) you will get a perfect partitioning per tenant of every query (no query will ever ever scan rows in a range of a different tenant) so you eliminate contention between tenants. To be more through, you can disable lock escalation to make sure no tenant escalates to block every other tenant.
This design also lends itself to table partitioning and to sharing the database for scale-out.
您绝对不想为每个用户创建一组表，并且您只希望这些表位于一个数据库中。即使 SQL Server 2008 的表容量很大（请注意，对象总数）数据库），它很快就会变得难以管理。最好的选择是使用 20 个表，并通过一列将它们分成用户区域。您可能会考虑按此用户值对表进行分区，但这也应该出于性能原因进行测试。
You definitely don't want to create a set of tables for each user, and you would want these only in one database. Even with SQL Server 2008's large capacity for tables (note really total objects in database), it would quickly become unmanageable. Your best bet is to use 20 tables, and separate them via a column into user areas. You might consider partitioning the tables by this user value, but that should be tested for performance reasons too.
是的，既然这些表只包含 id、key 和 value，为什么不制作一张表呢？
有列：
id, user ID, key, value
在用户 ID 字段上放置索引。
关系数据库背后的一个关键思想是表结构不会改变。您创建了一组可靠的表，这些是您的应用程序的“骨架”。
干杯，
丹尼尔
Yes, since the tables only contain id, key, and value, why not make one single table?
Have the columns:
id, user ID, key, value
Put an Index on the user ID field.
A key idea behind a relational database is that the table structure does not change. You create a solid set of tables, and these are the "bones" of your application.
Cheers,
Daniel
尼尔，
解决方案实际上取决于您的要求。如果担心安全性和数据访问，并且您只有少数用户，则可以为每个用户设置不同的数据库，并将其访问权限设置为仅他/她的数据库。
另一方面，Daniel Williams 建议的是一个很好的替代方案，其中您有一个数据库和表，其中索引列对用户数据行进行分区。
Neal,
The solution really depends on your requirement. If security and data access are concern and you have only a handful of users, you can set up a different db for each user with access for him set to only his/her database.
Other wise, what Daniel Williams suggested is a good alternative where you have one DB and tables laid out with a indexed column partitioning the users data rows.
从摘要中很难看出，但看起来您正在设计用户的动态归因。这种设计方法称为 EAV（实体属性值），由一个简单的基本集合键（UserID、SiteID、ProductID...）和由名称/值对组成的行组成。在更复杂的版本中，类别有时作为“超级列”添加到元组/行中，并为一组名称/值对提供子分组。
以这种方式设计将数据类型完整性、关系完整性和元组完整性的责任转移到应用层。
在关系系统中执行此操作的风险涉及将元组或行分解为一组行。更新、删除、缺失值和元组的定义不再可以通过人机交互轻松访问。随着应用程序的发展和元组定义的变化，几乎不可能判断名称/值对是否丢失，因为它是早期版本元组的一部分，还是因为它被无意删除。临时研究也变得更难管理，因为业务分析师必须在头脑中或在提供的文档中了解虚拟结构。
如果您正在寻求实现 EAV 模型，我建议您查看非关系型解决方案 (nosql)，例如 MongoDB 或 CouchDB。这些存储允许开发人员保存和检索“文档”或 json 格式的消息，这些消息本质上由名称/值对的集合组成，并且看起来非常像序列化对象。这里的优点是您可以存储动态归因而不破坏元组。您始终知道您拥有一个完整的元组，因为您可以将其作为单个“blob”信息进行存储和检索，并且可以随意序列化和反序列化。如果您担心的话，您还可以更新元组中的单个属性。
MongoDB 还提供了一些类似数据库的功能，例如多属性索引、与其他类似的非关系型产品相比更强大的查询引擎以及比使用 MySQL 麻烦得多的分片解决方案。
我希望这有帮助。
It's hard to tell from the summary, but it looks like you are designing for dynamic attribution by user. This design approach is called EAV (Entity-Attribute-Value) and consists of a simple base collection key (UserID, SiteID, ProductID...) and then rows consisting of name/value pairs. In a more complex version, categories are sometimes added as "super columns" to the tuple/row and provide sub-groupings for a set of name/value pairs.
Designing in this way moves responsibility for data type integrity, relational integrity and tuple integrity to the application layer.
The risk with doing this in a relational system involves the breaking of the tuple or row into a set of rows. Updates, deletes, missing values and the definition of a tuple are no longer easily accessible through human interaction. As your application evolves and the definition of a tuple changes, it becomes almost impossible to tell if a name/value pair is missing because it's part of an earlier-version tuple or because it was unintentionally deleted. Ad-hoc research as well becomes harder to manage as business analysts must keep an understanding of the virtual structure either in their heads or in documentation provided.
If you are looking to implement an EAV model, I would suggest you look at a non-relational solution (nosql) like MongoDB or CouchDB. These stores allow a developer to save and retrieve "documents" or json-formatted messages that are essentially made up of a collection of name/value pairs and can look very much like a serialized object. The advantage here is that you can store dynamic attribution without breaking your tuple. You always know that you have a complete tuple because you can store and retrieve it as a single "blob" of information that can be serialized and deserialized at-will. You can also update single attributes within the tuple, if that's a concern.
MongoDB also provides some database-like features such as multiple-attribute indexes, a query engine that is robust in comparison to other similar non-relational offerings and a sharding solution that is much less trouble than trying to do it with MySQL.
I hope this helps.
实际上，听起来您实际上只需要一个数据库即可完成此任务。
从您提出问题的方式来看，听起来您正在尝试在用户创建帐户时动态地为他们创建表。我不会推荐这种方法。
您想要做的是创建一个主表，其中包含每个用户的主键。我假设这是会员表。然后创建这些成员的个人资料所需的约 20 个表。每条记录，无论您拥有多少用户，都将进入这些表。这 20 个表需要有一个指向 Membership 表的唯一标识符的外键。
当您想要查询成员的用户信息时，只需从成员资格表的主 ID 与配置文件表中的外键匹配的表中进行选择。
这最终只会产生几个表，并且易于维护并遵循更好的数据库设计。
Realistically it sounds like you only really need one database for this.
From the way you worded your question, it sounds like you're trying to dynamically create tables for users as they create accounts. I wouldn't recommend this method.
What you want to do is create a master table that contains a primary key for each individual user. I'm assuming this is the Membership table. Then create the ~20 tables that you need for the profiles of these members. Every record, no matter the number of users that you have, will go into these tables. These 20 tables would need to have a foreign key pointing to the unique identifier of the Membership table.
When you want to query a Member for their user information, just select from the tables where the membership table's primary Id matches the foreign key in the profile tables.
This would result in only a few tables in the end and is easily maintainable and follows better database design.