If you have to create an application like - let's say a blog application, creating the database schema is relatively simple. You have to create some tables, tblPosts, tblAttachments, tblCommets, tblBlaBla… and that's it (ok, i know, that's a bit simplified but you understand what i mean).
如果你必须创建一个类似的应用程序 - 比如一个博客应用程序，那么创建数据库模式就相对简单了。你必须创建一些表，tblPosts，tblAttachments，tblCommets，tblBlaBla ......就是这样（好吧，我知道，这有点简化，但你理解我的意思）。
What if you have an application where you want to allow users to define parts of the schema at runtime. Let's say you want to build an application where users can log any kind of data. One user wants to log his working hours (startTime, endTime, project Id, description), the next wants to collect cooking recipes, others maybe stock quotes, the weekly weight of their babies, monthly expenses they spent for food, the results of their favorite football teams or whatever stuff you can think about.
如果您希望允许用户在运行时定义部分模式的应用程序，该怎么办？假设您要构建一个用户可以记录任何类型数据的应用程序。一个用户想要记录他的工作时间（startTime，endTime，项目ID，描述），下一个想要收集烹饪食谱，其他人可能是股票报价，他们的婴儿每周体重，他们花在食物上的每月费用，他们的结果最喜欢的足球队或你能想到的任何东西。
How would you design a database to hold all that very very different kind of data? Would you create a generic schema that can hold all kind of data, would you create new tables reflecting the user data schema or do you have another great idea to do that?
您如何设计数据库来保存所有非常不同类型的数据？您是否会创建一个可以容纳所有类型数据的通用架构，是否可以创建反映用户数据架构的新表，或者您是否有另外一个好主意呢？
If it's important: I have to use SQL Server / Entity Framework
如果它很重要：我必须使用SQL Server / Entity Framework
9 个解决方案
#1
11
Let's try again.
让我们再试一次。
If you want them to be able to create their own schema, then why not build the schema using, oh, I dunno, the CREATE TABLE statment. You have a full boat, full functional, powerful database that can do amazing things like define schemas and store data. Why not use it?
如果您希望他们能够创建自己的架构，那么为什么不使用，哦，我不知道，CREATE TABLE语句来构建架构。你有一个完整的船，功能齐全，功能强大的数据库，可以做一些惊人的事情，如定义模式和存储数据。为什么不用它？
If you were just going to do some ad-hoc properties, then sure.
如果你只是想做一些临时属性，那么肯定。
But if it's "carte blanche, they can do whatever they want", then let them.
但如果它是“全权委托，他们可以做任何他们想做的事情”，那就让他们吧。
Do they have to know SQL? Umm, no. That's your UIs task. Your job as a tool and application designer is to hide the implementation from the user. So present lists of fields, lines and arrows if you want relationships, etc. Whatever.
他们必须知道SQL吗？嗯，不。那是你的UI任务。您作为工具和应用程序设计人员的工作是隐藏用户的实现。如果你想要关系等，那么现在列出的字段，线条和箭头。无论如何。
Folks have been making "end user", "simple" database tools for years.
人们多年来一直在制作“最终用户”，“简单”的数据库工具。
"What if they want to add a column?" Then add a column, databases do that, most good ones at least. If not, create the new table, copy the old data, drop the old one.
“如果他们想要添加一个列怎么办？”然后添加一个列，数据库就是这样做的，至少是最好的。如果没有，请创建新表，复制旧数据，删除旧数据。
"What if they want to delete a column?" See above. If yours can't remove columns, then remove it from the logical view of the user so it looks like it's deleted.
“如果他们想要删除列怎么办？”往上看。如果您的列无法删除，则将其从用户的逻辑视图中删除，以使其看起来已被删除。
"What if they have eleventy zillion rows of data?" Then they have a eleventy zillion rows of data and operations take eleventy zillion times longer than if they had 1 row of data. If they have eleventy zillion rows of data, they probably shouldn't be using your system for this anyway.
“如果他们有数十亿行数据怎么办？”然后，他们拥有数十亿行数据和操作，比拥有1行数据的时间长了11亿次。如果他们有数十亿行数据，他们可能不应该使用你的系统。
The fascination of "Implementing databases on databases" eludes me.
“在数据库上实施数据库”的魅力使我望而却步。
"I have Oracle here, how can I offer less features and make is slower for the user??"
“我在这里有Oracle，我怎么能提供更少的功能，让用户的速度变慢？”
Gee, I wonder.
哎呀，我想知道。
#2
9
There's no way you can predict how complex their data requirements will be. Entity-Attribute-Value is one typical solution many programmers use, but it might be be sufficient, for instance if the user's data would conventionally be modeled with multiple tables.
您无法预测其数据要求的复杂程度。 Entity-Attribute-Value是许多程序员使用的典型解决方案，但它可能就足够了，例如，如果用户的数据通常用多个表建模。
I'd serialize the user's custom data as XML or YAML or JSON or similar semi-structured format, and save it in a text BLOB.
我将用户的自定义数据序列化为XML或YAML或JSON或类似的半结构化格式，并将其保存在文本BLOB中。
You can even create inverted indexes so you can look up specific values among the attributes in your BLOB. See http://bret.appspot.com/entry/how-friendfeed-uses-mysql (the technique works in any RDBMS, not just MySQL).
您甚至可以创建反向索引，以便在BLOB中的属性中查找特定值。请参阅http://bret.appspot.com/entry/how-friendfeed-uses-mysql（该技术适用于任何RDBMS，而不仅仅是MySQL）。
Also consider using a document store such as Solr or MongoDB. These technologies do not need to conform to relational database conventions. You can add new attributes to any document at runtime, without needing to redefine the schema. But it's a tradeoff -- having no schema means your app can't depend on documents/rows being similar throughout the collection.
还要考虑使用Solr或MongoDB等文档存储。这些技术不需要符合关系数据库约定。您可以在运行时向任何文档添加新属性，而无需重新定义架构。但这是一种权衡 - 没有架构意味着你的应用程序不能依赖整个集合中类似的文档/行。
I'm a critic of the Entity-Attribute-Value anti-pattern.
我是Entity-Attribute-Value反模式的批评者。
I've written about EAV problems in my book, SQL Antipatterns: Avoiding the Pitfalls of Database Programming.
我在我的书“SQL Antipatterns：避免数据库编程的陷阱”中写过关于EAV问题的文章。
Here's an SO answer where I list some problems with Entity-Attribute-Value: "Product table, many kinds of products, each product has many parameters."
这是一个SO答案，我列出了Entity-Attribute-Value的一些问题：“产品表，多种产品，每种产品都有很多参数。”
Here's a blog I posted the other day with some more discussion of EAV problems: "EAV FAIL."
这是我前几天发布的关于EAV问题的更多讨论的博客：“EAV FAIL。”
And be sure to read this blog "Bad CaRMa" about how attempting to make a fully flexible database nearly destroyed a company.
并且一定要阅读这篇博客“Bad CaRMa”，了解如何使一个完全灵活的数据库几乎摧毁了一家公司。
#3
5
I would go for a Hybrid Entity-Attribute-Value model, so like Antony's reply, you have EAV tables, but you also have default columns (and class properties) which will always exist.
我会选择混合实体 - 属性 - 值模型，因此像Antony的回复一样，您有EAV表，但您也有默认列（和类属性），它们将始终存在。
Here's a great article on what you're in for :)
这是一篇很棒的文章，关于你在做什么:)
As an additional comment, I knocked up a prototype for this approach using Linq2Sql in a few days, and it was a workable solution. Given that you've mentioned Entity Framework, I'd take a look at version 4 and their POCO support, since this would be a good way to inject a hybrid EAV model without polluting your EF schema.
作为补充评论，我在几天内使用Linq2Sql敲除了这种方法的原型，这是一个可行的解决方案。鉴于您已经提到了实体框架，我将看一下版本4及其POCO支持，因为这将是一种注入混合EAV模型而不会污染您的EF模式的好方法。
#4
#5
3
I'm not familiar with the Entity Framework, but I would lean towards the Entity-Attribute-Value (http://en.wikipedia.org/wiki/Entity-Attribute-Value_model) database model.
我不熟悉实体框架，但我倾向于实体 - 属性 - 值（http://en.wikipedia.org/wiki/Entity-Attribute-Value_model）数据库模型。
So, rather than creating tables and columns on the fly, your app would create attributes (or collections of attributes) and then your end users would complete the values.
因此，您的应用程序不是动态创建表格和列，而是创建属性（或属性集合），然后您的最终用户将完成值。
But, as I said, I don't know what the Entity Framework is supposed to do for you, and it may not let you take this approach.
但是，正如我所说，我不知道实体框架应该为你做什么，它可能不会让你采取这种方法。
#6
1
Not as a critical comment, but it may help save some of your time to point out that this is one of those Don Quixote Holy Grail type issues. There's an eternal quest for probably over 50 years to make a user-friendly database design interface.
不是一个批评性的评论，但它可能有助于节省你的一些时间来指出这是唐吉诃德圣杯类型问题之一。有一个超过50年的永恒追求，使用户友好的数据库设计界面。
The only quasi-successful ones that have gained any significant traction that I can think of are 1. Excel (and its predecessors), 2. Filemaker (the original, not its current flavor), and 3. (possibly, but doubtfully) Access. Note that the first two are limited to basically one table.
我能想到的唯一准成功的是1. Excel（和它的前辈），2。Filemaker（原始的，不是它当前的味道），和3.（可能，但可疑）访问。请注意，前两个基本上仅限于一个表。
I'd be surprised if our collective conventional wisdom is going to help you break the barrier. But it would be wonderful.
如果我们的集体传统智慧能够帮助你打破障碍，我会感到惊讶。但它会很精彩。
#7
1
Rather than re-implement sqlservers "CREATE TABLE" statement, which was done many years ago by a team of programmers who were probably better than you or I, why not work on exposing SQLSERVER in a limited way to the users -- let them create thier own schema in a limited way and leverage the power of SQLServer to do it properly.
多年前由一个可能比你或我更好的程序员团队完成的sqlservers“CREATE TABLE”语句重新实现，为什么不以有限的方式向用户公开SQLSERVER - 让他们创建他们以有限的方式拥有自己的架构，并利用SQLServer的强大功能来正确地完成它。
#8
0
I would just give them a copy of SQL Server Management Studio, and say, "go nuts!" Why reinvent a wheel within a wheel?
我只是给他们一份SQL Server Management Studio，并说“疯了！”为什么要在车轮内重新发明*？
#9
0
Check out this post you can do it but it's a lot of hard work :) If performance is not a concern an xml solution could work too though that is also alot of work.
看看这篇文章，你可以做到这一点，但这是一项艰苦的工作:)如果性能不是一个问题，xml解决方案也可以工作，虽然这也是很多工作。
#1
11
Let's try again.
让我们再试一次。
If you want them to be able to create their own schema, then why not build the schema using, oh, I dunno, the CREATE TABLE statment. You have a full boat, full functional, powerful database that can do amazing things like define schemas and store data. Why not use it?
如果您希望他们能够创建自己的架构，那么为什么不使用，哦，我不知道，CREATE TABLE语句来构建架构。你有一个完整的船，功能齐全，功能强大的数据库，可以做一些惊人的事情，如定义模式和存储数据。为什么不用它？
If you were just going to do some ad-hoc properties, then sure.
如果你只是想做一些临时属性，那么肯定。
But if it's "carte blanche, they can do whatever they want", then let them.
但如果它是“全权委托，他们可以做任何他们想做的事情”，那就让他们吧。
Do they have to know SQL? Umm, no. That's your UIs task. Your job as a tool and application designer is to hide the implementation from the user. So present lists of fields, lines and arrows if you want relationships, etc. Whatever.
他们必须知道SQL吗？嗯，不。那是你的UI任务。您作为工具和应用程序设计人员的工作是隐藏用户的实现。如果你想要关系等，那么现在列出的字段，线条和箭头。无论如何。
Folks have been making "end user", "simple" database tools for years.
人们多年来一直在制作“最终用户”，“简单”的数据库工具。
"What if they want to add a column?" Then add a column, databases do that, most good ones at least. If not, create the new table, copy the old data, drop the old one.
“如果他们想要添加一个列怎么办？”然后添加一个列，数据库就是这样做的，至少是最好的。如果没有，请创建新表，复制旧数据，删除旧数据。
"What if they want to delete a column?" See above. If yours can't remove columns, then remove it from the logical view of the user so it looks like it's deleted.
“如果他们想要删除列怎么办？”往上看。如果您的列无法删除，则将其从用户的逻辑视图中删除，以使其看起来已被删除。
"What if they have eleventy zillion rows of data?" Then they have a eleventy zillion rows of data and operations take eleventy zillion times longer than if they had 1 row of data. If they have eleventy zillion rows of data, they probably shouldn't be using your system for this anyway.
“如果他们有数十亿行数据怎么办？”然后，他们拥有数十亿行数据和操作，比拥有1行数据的时间长了11亿次。如果他们有数十亿行数据，他们可能不应该使用你的系统。
The fascination of "Implementing databases on databases" eludes me.
“在数据库上实施数据库”的魅力使我望而却步。
"I have Oracle here, how can I offer less features and make is slower for the user??"
“我在这里有Oracle，我怎么能提供更少的功能，让用户的速度变慢？”
Gee, I wonder.
哎呀，我想知道。
#2
9
There's no way you can predict how complex their data requirements will be. Entity-Attribute-Value is one typical solution many programmers use, but it might be be sufficient, for instance if the user's data would conventionally be modeled with multiple tables.
您无法预测其数据要求的复杂程度。 Entity-Attribute-Value是许多程序员使用的典型解决方案，但它可能就足够了，例如，如果用户的数据通常用多个表建模。
I'd serialize the user's custom data as XML or YAML or JSON or similar semi-structured format, and save it in a text BLOB.
我将用户的自定义数据序列化为XML或YAML或JSON或类似的半结构化格式，并将其保存在文本BLOB中。
You can even create inverted indexes so you can look up specific values among the attributes in your BLOB. See http://bret.appspot.com/entry/how-friendfeed-uses-mysql (the technique works in any RDBMS, not just MySQL).
您甚至可以创建反向索引，以便在BLOB中的属性中查找特定值。请参阅http://bret.appspot.com/entry/how-friendfeed-uses-mysql（该技术适用于任何RDBMS，而不仅仅是MySQL）。
Also consider using a document store such as Solr or MongoDB. These technologies do not need to conform to relational database conventions. You can add new attributes to any document at runtime, without needing to redefine the schema. But it's a tradeoff -- having no schema means your app can't depend on documents/rows being similar throughout the collection.
还要考虑使用Solr或MongoDB等文档存储。这些技术不需要符合关系数据库约定。您可以在运行时向任何文档添加新属性，而无需重新定义架构。但这是一种权衡 - 没有架构意味着你的应用程序不能依赖整个集合中类似的文档/行。
I'm a critic of the Entity-Attribute-Value anti-pattern.
我是Entity-Attribute-Value反模式的批评者。
I've written about EAV problems in my book, SQL Antipatterns: Avoiding the Pitfalls of Database Programming.
我在我的书“SQL Antipatterns：避免数据库编程的陷阱”中写过关于EAV问题的文章。
Here's an SO answer where I list some problems with Entity-Attribute-Value: "Product table, many kinds of products, each product has many parameters."
这是一个SO答案，我列出了Entity-Attribute-Value的一些问题：“产品表，多种产品，每种产品都有很多参数。”
Here's a blog I posted the other day with some more discussion of EAV problems: "EAV FAIL."
这是我前几天发布的关于EAV问题的更多讨论的博客：“EAV FAIL。”
And be sure to read this blog "Bad CaRMa" about how attempting to make a fully flexible database nearly destroyed a company.
并且一定要阅读这篇博客“Bad CaRMa”，了解如何使一个完全灵活的数据库几乎摧毁了一家公司。
#3
5
I would go for a Hybrid Entity-Attribute-Value model, so like Antony's reply, you have EAV tables, but you also have default columns (and class properties) which will always exist.
我会选择混合实体 - 属性 - 值模型，因此像Antony的回复一样，您有EAV表，但您也有默认列（和类属性），它们将始终存在。
Here's a great article on what you're in for :)
这是一篇很棒的文章，关于你在做什么:)
As an additional comment, I knocked up a prototype for this approach using Linq2Sql in a few days, and it was a workable solution. Given that you've mentioned Entity Framework, I'd take a look at version 4 and their POCO support, since this would be a good way to inject a hybrid EAV model without polluting your EF schema.
作为补充评论，我在几天内使用Linq2Sql敲除了这种方法的原型，这是一个可行的解决方案。鉴于您已经提到了实体框架，我将看一下版本4及其POCO支持，因为这将是一种注入混合EAV模型而不会污染您的EF模式的好方法。
#4
3
On the surface, a schema-less or document-oriented database such as CouchDB or SimpleDB for the custom user data sounds ideal. But I guess that doesn't help much if you can't use anything but SQL and EF.
从表面上看，用于自定义用户数据的无模式或面向文档的数据库（如CouchDB或SimpleDB）听起来很理想。但是，如果除了SQL和EF之外你不能使用任何东西，我想这没有多大帮助。
#5
3
I'm not familiar with the Entity Framework, but I would lean towards the Entity-Attribute-Value (http://en.wikipedia.org/wiki/Entity-Attribute-Value_model) database model.
我不熟悉实体框架，但我倾向于实体 - 属性 - 值（http://en.wikipedia.org/wiki/Entity-Attribute-Value_model）数据库模型。
So, rather than creating tables and columns on the fly, your app would create attributes (or collections of attributes) and then your end users would complete the values.
因此，您的应用程序不是动态创建表格和列，而是创建属性（或属性集合），然后您的最终用户将完成值。
But, as I said, I don't know what the Entity Framework is supposed to do for you, and it may not let you take this approach.
但是，正如我所说，我不知道实体框架应该为你做什么，它可能不会让你采取这种方法。
#6
1
Not as a critical comment, but it may help save some of your time to point out that this is one of those Don Quixote Holy Grail type issues. There's an eternal quest for probably over 50 years to make a user-friendly database design interface.
不是一个批评性的评论，但它可能有助于节省你的一些时间来指出这是唐吉诃德圣杯类型问题之一。有一个超过50年的永恒追求，使用户友好的数据库设计界面。
The only quasi-successful ones that have gained any significant traction that I can think of are 1. Excel (and its predecessors), 2. Filemaker (the original, not its current flavor), and 3. (possibly, but doubtfully) Access. Note that the first two are limited to basically one table.
我能想到的唯一准成功的是1. Excel（和它的前辈），2。Filemaker（原始的，不是它当前的味道），和3.（可能，但可疑）访问。请注意，前两个基本上仅限于一个表。
I'd be surprised if our collective conventional wisdom is going to help you break the barrier. But it would be wonderful.
如果我们的集体传统智慧能够帮助你打破障碍，我会感到惊讶。但它会很精彩。
#7
1
Rather than re-implement sqlservers "CREATE TABLE" statement, which was done many years ago by a team of programmers who were probably better than you or I, why not work on exposing SQLSERVER in a limited way to the users -- let them create thier own schema in a limited way and leverage the power of SQLServer to do it properly.
多年前由一个可能比你或我更好的程序员团队完成的sqlservers“CREATE TABLE”语句重新实现，为什么不以有限的方式向用户公开SQLSERVER - 让他们创建他们以有限的方式拥有自己的架构，并利用SQLServer的强大功能来正确地完成它。
#8
0
I would just give them a copy of SQL Server Management Studio, and say, "go nuts!" Why reinvent a wheel within a wheel?
我只是给他们一份SQL Server Management Studio，并说“疯了！”为什么要在车轮内重新发明*？
#9
0
Check out this post you can do it but it's a lot of hard work :) If performance is not a concern an xml solution could work too though that is also alot of work.
看看这篇文章，你可以做到这一点，但这是一项艰苦的工作:)如果性能不是一个问题，xml解决方案也可以工作，虽然这也是很多工作。