I'm wondering if the following DB schema would have repercussions later. Let's say I'm writing a place entity. I'm not certain what properties of place will be stored in the DB. I'm thinking of making two tables: one to hold the required (or common) info, and one to hold additional info.
我想知道以下数据库架构是否会在以后产生影响。假设我正在写一个地方实体。我不确定地方的哪些属性将存储在数据库中。我正在考虑制作两个表：一个用于保存所需（或常见）信息，另一个用于保存其他信息。
Table 1 - Place
表1 - 地点
- PK PlaceId
- PK PlaceId
- Name
- 名称
- Lat
- 纬度
- Lng
- LNG
- etc... (all the common fields)
- 等......（所有常见领域）
Table 2 - PlaceData
表2 - PlaceData
- PK DataId
- PK DataId
- PK FieldName
- PK FieldName
- PK FK PlaceId
- PK FK PlaceId
- FieldData
- FieldData
Usage Scenario
使用场景
I want certain visitors to have the capability of entering custom fields about a place. For example, a restaurant is a place that may have the following fields: HasParking, HasDriveThru, RequiresReservation, etc... but a car dealer is also a place, and those fields wouldn't make sense for a car dealer.
我希望某些访问者能够输入关于某个地方的自定义字段。例如，餐馆是一个可能包含以下字段的地方：HasParking，HasDriveThru，RequiresReservation等......但是汽车经销商也是一个地方，这些领域对于汽车经销商来说没有意义。
I want to support any type of place, from a single table (well, 2nd table has custom fields), because I don't know the number of types of places that will eventually be added to my site.
我想支持任何类型的地方，从一个表（嗯，第二个表有自定义字段），因为我不知道最终将添加到我的网站的地方类型的数量。
Overall goal
总体的目标
On my asp.net MVC (C#/Razor) site, where I display a place, it will show the attributes, as a unordered list populated by: SELECT * FROM PlaceData WHERE PlaceId = @0.
在我的asp.net MVC（C＃/ Razor）站点上，我显示一个位置，它将显示属性，作为一个无序列表填充：SELECT * FROM PlaceData WHERE PlaceId = @ 0。
This way, I wouldn't need to show empty field names on the view (or do a string.IsNullOrWhitespace() check for each and every field. Which I would be forced to do if every attribute was a column on the table.
这样，我就不需要在视图上显示空字段名称（或者对每个字段执行string.IsNullOrWhitespace（）检查。如果每个属性都是表中的列，我将被强制执行。
I'm assuming this scenario is quite common, but are there better ways to do it? Particularly from a performance perspective? What are the major drawbacks of this schema?
我假设这种情况很常见，但是有更好的方法吗？特别是从绩效角度来看？这个架构的主要缺点是什么？
3 个解决方案
#1
2
If you want your application to be able to create its own custom fields, this is a fine model. The Mantis Bugtracker uses this as well to allow Admins to add custom fields to their tickets.
如果您希望您的应用程序能够创建自己的自定义字段，这是一个很好的模型。 Mantis Bugtracker也使用它来允许管理员向其故障单添加自定义字段。
If in any case, it's going to be the programmer that is going to create the field, I must agree with pst that this is more a premature optimization.
如果在任何情况下，它将成为创建该字段的程序员，我必须同意pst，这更不成熟。
#2
6
Your idea is referred to as an Entity-Attribute-Value table and is generally bad news in a RDBMS. RDBMSes are geared toward highly structured data.
您的想法被称为实体 - 属性 - 值表，并且通常是RDBMS中的坏消息。 RDBMS适用于高度结构化的数据。
The overall options are:
总体选择是：
-
Model the db further in an RDBMS, which is most likely if someone is holding back specs from you.
在RDBMS中进一步对数据库建模，这很可能是因为有人阻止了您的规范。
-
Stick with the RDBMS, using XML columns for the data whose structure is variable. This makes the most sense if a relatively small portion of your data storage schema is semi- or un-structured. Speaking from a MS SQL Server perspective, this data can be indexed and you can perform checks that your data complies with an XML schema definition.
坚持使用RDBMS，使用XML列来获取结构可变的数据。如果您的数据存储架构的相对较小部分是半结构化或非结构化的，则这是最有意义的。从MS SQL Server的角度来看，可以对此数据建立索引，并且可以执行检查，确保数据符合XML模式定义。
-
Move to a non-relational DB such as MongoDB, Cassandra, CouchDB, etc. This is what a lot of social sites and I suspect blog sites run with. Also, it is within reason to use a combination of RDBMS and non-relational stores if that's what your needs call for.
转移到非关系数据库，如MongoDB，Cassandra，CouchDB等。这就是许多社交网站和我怀疑博客网站运行的原因。此外，如果您的需求符合要求，则可以使用RDBMS和非关系存储的组合。
EAV gets to be a mess because you're creating a database within a database and lose all of the benefits a RDBMS can provide (foreign keys, data type enforcement, etc.) and the SQL code needed to reconstruct your objects goes from lasagna to fettuccine to spaghetti in the blink of an eye.
EAV变得一团糟，因为您在数据库中创建数据库并失去RDBMS可以提供的所有好处（外键，数据类型实施等），重建对象所需的SQL代码从千层面到眨眼间意大利细面条意大利细面条。
Given the information that's been added to the question, it would seem a good fit to create a PlaceDetails column of type XML in the Place table. You could also split that column into another table with a 1:1 relationship if performance requirements dictate it.
鉴于已添加到问题中的信息，似乎非常适合在Place表中创建XML类型的PlaceDetails列。如果性能要求规定，您还可以将该列拆分为另一个具有1：1关系的表。
The upside to doing it that way is that you can retrieve the data using very simple SQL code, even using the xml data type's methods for searching the data. But that approach also allows you to do the more complex presentation-oriented data parsing in C#, which is better suited to that purpose than T-SQL is.
这样做的好处是，您可以使用非常简单的SQL代码检索数据，甚至使用xml数据类型的方法来搜索数据。但是这种方法还允许你在C＃中进行更复杂的面向表示的数据解析，这比T-SQL更适合于这个目的。
#3
2
At any given time you can add new columns to the database (always watching for the third normalization rule) so you should go with what you want and only create a second table if needed or if such columns breaks any of the normal forms.
在任何给定时间，您都可以向数据库添加新列（始终关注第三个规范化规则），因此您应该选择所需的内容，并且只在需要时创建第二个表，或者这些列是否会破坏任何常规表单。
#1
2
If you want your application to be able to create its own custom fields, this is a fine model. The Mantis Bugtracker uses this as well to allow Admins to add custom fields to their tickets.
如果您希望您的应用程序能够创建自己的自定义字段，这是一个很好的模型。 Mantis Bugtracker也使用它来允许管理员向其故障单添加自定义字段。
If in any case, it's going to be the programmer that is going to create the field, I must agree with pst that this is more a premature optimization.
如果在任何情况下，它将成为创建该字段的程序员，我必须同意pst，这更不成熟。
#2
6
Your idea is referred to as an Entity-Attribute-Value table and is generally bad news in a RDBMS. RDBMSes are geared toward highly structured data.
您的想法被称为实体 - 属性 - 值表，并且通常是RDBMS中的坏消息。 RDBMS适用于高度结构化的数据。
The overall options are:
总体选择是：
-
Model the db further in an RDBMS, which is most likely if someone is holding back specs from you.
在RDBMS中进一步对数据库建模，这很可能是因为有人阻止了您的规范。
-
Stick with the RDBMS, using XML columns for the data whose structure is variable. This makes the most sense if a relatively small portion of your data storage schema is semi- or un-structured. Speaking from a MS SQL Server perspective, this data can be indexed and you can perform checks that your data complies with an XML schema definition.
坚持使用RDBMS，使用XML列来获取结构可变的数据。如果您的数据存储架构的相对较小部分是半结构化或非结构化的，则这是最有意义的。从MS SQL Server的角度来看，可以对此数据建立索引，并且可以执行检查，确保数据符合XML模式定义。
-
Move to a non-relational DB such as MongoDB, Cassandra, CouchDB, etc. This is what a lot of social sites and I suspect blog sites run with. Also, it is within reason to use a combination of RDBMS and non-relational stores if that's what your needs call for.
转移到非关系数据库，如MongoDB，Cassandra，CouchDB等。这就是许多社交网站和我怀疑博客网站运行的原因。此外，如果您的需求符合要求，则可以使用RDBMS和非关系存储的组合。
EAV gets to be a mess because you're creating a database within a database and lose all of the benefits a RDBMS can provide (foreign keys, data type enforcement, etc.) and the SQL code needed to reconstruct your objects goes from lasagna to fettuccine to spaghetti in the blink of an eye.
EAV变得一团糟，因为您在数据库中创建数据库并失去RDBMS可以提供的所有好处（外键，数据类型实施等），重建对象所需的SQL代码从千层面到眨眼间意大利细面条意大利细面条。
Given the information that's been added to the question, it would seem a good fit to create a PlaceDetails column of type XML in the Place table. You could also split that column into another table with a 1:1 relationship if performance requirements dictate it.
鉴于已添加到问题中的信息，似乎非常适合在Place表中创建XML类型的PlaceDetails列。如果性能要求规定，您还可以将该列拆分为另一个具有1：1关系的表。
The upside to doing it that way is that you can retrieve the data using very simple SQL code, even using the xml data type's methods for searching the data. But that approach also allows you to do the more complex presentation-oriented data parsing in C#, which is better suited to that purpose than T-SQL is.
这样做的好处是，您可以使用非常简单的SQL代码检索数据，甚至使用xml数据类型的方法来搜索数据。但是这种方法还允许你在C＃中进行更复杂的面向表示的数据解析，这比T-SQL更适合于这个目的。
#3
2
At any given time you can add new columns to the database (always watching for the third normalization rule) so you should go with what you want and only create a second table if needed or if such columns breaks any of the normal forms.
在任何给定时间，您都可以向数据库添加新列（始终关注第三个规范化规则），因此您应该选择所需的内容，并且只在需要时创建第二个表，或者这些列是否会破坏任何常规表单。