I have a coworker who is planning a database for a new app that will have several tables with over 30 fields each. Is this excessive? Maybe I'm just not enterprisey enough to understand.
我有个同事正在为一个新的应用程序设计一个数据库，这个应用程序将有几个表，每个表有30多个字段。这是过度吗?也许我不够有进取心，理解不了。
Edit: Also, a lot of the fields are option-type sort of things (like on a request form, would you like your widget to be yellow or green, he has a field for 'colour' with an enum). It's quite likely that these will be added to or removed over time. I haven't really done database design and try to stay away from it myself, so maybe I'm being completely stupid, but surely there's a better way of doing this??
编辑:还有，许多字段都是选项类型的东西(比如在请求表单上，您希望您的小部件是黄色还是绿色的，他有一个带有enum的“颜色”字段)。随着时间的推移，它们很可能会被添加或删除。我还没有做过数据库设计，我也没试着远离它，所以也许我是完全愚蠢的，但是肯定有更好的方法来做这个?
12 个解决方案
#1
12
Database tables can legitimately have 30 or more fields in them. What you need to look at is the normalization of the data and whether that normalization makes any sense. It will normally change in the future, as well. But, you want to try to minimize that.
数据库表中可以合法地包含30个或更多字段。你需要看的是数据的标准化以及这种正常化是否有意义。它通常也会在未来发生变化。但是，你要尽量把它最小化。
For instance, if you have a table that has addresses in it, do you include the city, state, and zip code fields in that table? Or, do you only include one field that "points" to a record in a separate table for those values? The separate table would contain unique city, state, zip code combinations. The effect of splitting the data into two tables is a reduction in the amount of data stored (most likely but not absolute) but a bit of added complexity when you go to run queries against the database. Now, you have to deal with 2 tables instead of just one. But, on the bright side, it's much cleaner, and much smaller (likely).
例如，如果有一个包含地址的表，是否在该表中包含城市、州和邮政编码字段?或者，您是否只包含一个字段，该字段在单独的表中为这些值“指向”一条记录?单独的表将包含唯一的城市、州、邮政编码组合。将数据分割为两个表的效果是减少了存储的数据量(很有可能但不是绝对的)，但是在对数据库运行查询时增加了一点复杂性。现在，你需要处理两个表而不是一个表。但是，从好的方面来看，它更清洁，也更小(可能)。
The real answer is it's okay to leave the city-state-zip data in the address table in the right circumstances. Or, you might want to "normalize" it out. Both are okay.
真正的答案是，在适当的情况下，可以将城市状态压缩数据保留在地址表中。或者，你可能想把它“正常化”。两者都是好的。
Find a good database administrator and hire them short term to review the plan, if it's in the budget. It will pay off in the long run.
找一个好的数据库管理员，短期雇佣他们来检查计划，如果计划在预算之内。从长远来看，这是值得的。
#2
11
The most obvious sign a table requires normalization that I've seen is fields ending with integers: CouponCode1, CouponCode2, CouponCode3.. you get the point. There will be exceptions to the rule as always though.
表中最明显的标志是，我所看到的是以整数结尾的字段:CouponCode1、CouponCode2、CouponCode3..你明白了吧。这条规则总会有例外的。
#3
9
Thirty fields is not too many - you just have to make sure your data is properly normalized (for which there are plenty of guides on the web).
30个字段并不太多——您只需确保您的数据是正确的规范化(web上有大量的指南)。
Based on your edit where you specify that many columns will be option-type fields which may be added or deleted over time, I would suggest the following is a better idea.
根据您的编辑，您指定许多列将是可随时间添加或删除的选项类型字段，我建议采用以下方法。
BaseTable:
Id
NonOptionFields
OptionTable:
Id
OptionName
OptionValue
Then you can tie all your options to the base record. This will mean you won't have to be adding and deleting columns to tables all the time ly normalized way to achieve what you want.
然后您可以将所有选项绑定到基本记录。这意味着您不必一直以规范化的方式向表添加和删除列，以实现您想要的结果。
#4
6
Of course, the standard answer is it depends. A table with that many fields could actually make quite a lot of sense in some situations.
当然，标准答案是要看情况而定。一个包含这么多字段的表实际上在某些情况下很有意义。
Think about the data you'll be storing in there. Is it likely that many of these fields will be NULL? What's the likelihood that these fields change (eg: more are added)?
想想你要存储的数据。这些字段中有很多可能是空的吗?这些领域发生变化的可能性是什么(如:增加更多)?
If only certain fields apply to certain objects, perhaps think about putting those fields into another table. Alternatively, store just the basic, common fields in one table, and the extra information in another table, one row per field. As I suggested for a different question (which might be helpful to you):
如果只有特定的字段应用于某些对象，或者考虑将这些字段放到另一个表中。或者，只在一个表中存储基本的、公共的字段，在另一个表中存储额外的信息，每个字段存储一行。如我所建议的另一个问题(可能对你有帮助):
refs (id, title, refType) -- title of the reference, and what type of reference it is fieldDef (id, fieldName, refType, dataType) -- name of the field, which reference types it applies to, and -- what type of data is stored in these fields (ISDN number, date, etc) fields (refId, fieldId, value) -- where you actually add data to the references.
Note that this was downvoted, and probably with good reason. This is an option, not necessarily the best option, but it's still a workable method. The highest voted answer in the question I linked to there might be the best solution however.
注意，这被否决了，而且可能有很好的理由。这是一个选项，不一定是最好的选项，但仍然是一个可行的方法。在我所联系的问题中，最高的投票回答可能是最好的解决方案。
Edit: since you say that it will be holding things like per-user settings (eg: widget colour), I'd actually recommend the method outlined above (with the three tables). Chances are that most people will leave things at the default, so you'll have a stack of useless information being stored. Please do read my answer in the other question because other readers have pointed out the shortcomings of this method.
编辑:既然你说它将保存像每个用户设置一样的东西(例如:小部件颜色)，我实际上会推荐上面概述的方法(包含三个表)。大多数人可能会把东西放在默认的位置，这样就会存储一堆无用的信息。请务必阅读我在另一个问题中的回答，因为其他读者已经指出了这种方法的缺点。
#5
4
there is no arbitrary limit; enough to get the job done is a good rule of thumb
没有任意的极限;完成这项工作是一个很好的经验法则
if you have a better db design, suggest it
如果你有一个更好的db设计，建议它。
if you want more detailed feedback, post the schema
如果您想要更详细的反馈，请发布模式
#6
4
The term "too many" is a relative one... You shouldn't split a table only for the sake of reducing the number of fields, especially if in every query you're going to have to join them back together because they are essentially one-to-one relationships. If the fields can be broken down into a separate, logical object then it would make sense. For example instead of storing address fields on a customer table, they could be moved into a separate address table. This is a crude example, but it illustrates my point.
“太多”这个词是相对的……您不应该仅仅为了减少字段的数量而拆分表，特别是如果在每个查询中都必须将它们连接到一起，因为它们本质上是一对一的关系。如果可以将字段分解成一个独立的、逻辑的对象，那么它将是有意义的。例如，与其将地址字段存储在客户表上，不如将它们移动到单独的地址表中。这是一个粗略的例子，但它说明了我的观点。
#7
3
The number of fields is usually not a problem, but you want to make sure your database is correctly noralized. Third normal form is a good start.
字段的数量通常不是问题，但是您需要确保您的数据库是正确的规范化。第三种形式是一个好的开始。
#8
2
If you have to ask, "Are there too many fields in this table?" Then there probably are.
如果你不得不问，“这个表格里有太多字段吗?”还有可能。
#9
2
OLTP
OLTP
From my experience of designing databases, there are very few tables in a normalized OLTP database that contain an insanely large number of columns.
根据我设计数据库的经验，规范化OLTP数据库中很少有包含大量列的表。
IMO 30 columns is too many.
IMO 30列太多了。
For me, no more than 10% of my OLTP tables have a large number(>10) of columns.
对于我来说，OLTP表中不超过10%的列数量很大(>10)。
OLAP
OLAP
Now if you're going to do a Dimensional / Reporting structure, some people may consider a 30 column table to be narrow.
现在如果你要做一个维度/报告结构，有些人可能会认为30列表很窄。
#10
1
The guerilla's guide to normalization-by-default:
《游击战规范》的默认指南:
- A table should have a primary key and at most one other column.
- 表应该有一个主键，最多只有一个其他列。
- Break rule number 1 only as often as required.
- 只在需要的时候打破规则1。
#11
1
There is no constraint on number of fields in database theory. A table can be limited to a primary key (even if this primary key is made of 2 fields), meaning that Apocalisp's answer is not very clear. At the opposit, a table can be made out of thousends of fields, as long as normal form rules are respected.
数据库理论中没有对字段数量的限制。一个表可以限制为一个主键(即使这个主键是由两个字段组成的)，这意味着罗布麻的答案不是很清楚。相反，只要遵守常规的形式规则，表格就可以由字段组成。
When groups of fields are obviously underused in a table, it can be smart to split this group of fields in another table with a 0-1 relation between the main table and the "sub" table.
当表中字段组明显未得到充分使用时，最好将这组字段分割到另一个表中，主表和“子”表之间的关系为0-1。
For security reasons, it was also often proposed (a long time ago: i think it was my first book of relationnal databases, first published in 197?) to split the confidential infos in another table with the same 0-1 relation between main and sub. It was then possible to easily restrict user access to "sub" table. Such a configuration can now be easily managed through views.
出于安全考虑,它也常常提出(很久以前:我认为这是我的第一本relationnal数据库,197年首次出版?)将机密信息在另一个表相同的0 - 1之间的关系主要和子。当时可能很容易限制用户访问表“子”。这样的配置现在可以通过视图轻松地进行管理。
#12
0
A tell-tale sign is just what you said. He has fields that should in theory be split out into a different table. Another giveaway is the presence of many optional fields.
你说的正是事实。他的字段理论上应该被分成不同的表格。另一个好处是有许多可选字段。
I'd say that a course in database design is in order for your DB "Expert". And I'd suggest that you brush up on it as well...it can only help you grow in your career :)
我想说，数据库设计的课程是为了让你的DB“专家”。我建议你也复习一下……它只会帮助你在事业上成长。
#1
12
Database tables can legitimately have 30 or more fields in them. What you need to look at is the normalization of the data and whether that normalization makes any sense. It will normally change in the future, as well. But, you want to try to minimize that.
数据库表中可以合法地包含30个或更多字段。你需要看的是数据的标准化以及这种正常化是否有意义。它通常也会在未来发生变化。但是，你要尽量把它最小化。
For instance, if you have a table that has addresses in it, do you include the city, state, and zip code fields in that table? Or, do you only include one field that "points" to a record in a separate table for those values? The separate table would contain unique city, state, zip code combinations. The effect of splitting the data into two tables is a reduction in the amount of data stored (most likely but not absolute) but a bit of added complexity when you go to run queries against the database. Now, you have to deal with 2 tables instead of just one. But, on the bright side, it's much cleaner, and much smaller (likely).
例如，如果有一个包含地址的表，是否在该表中包含城市、州和邮政编码字段?或者，您是否只包含一个字段，该字段在单独的表中为这些值“指向”一条记录?单独的表将包含唯一的城市、州、邮政编码组合。将数据分割为两个表的效果是减少了存储的数据量(很有可能但不是绝对的)，但是在对数据库运行查询时增加了一点复杂性。现在，你需要处理两个表而不是一个表。但是，从好的方面来看，它更清洁，也更小(可能)。
The real answer is it's okay to leave the city-state-zip data in the address table in the right circumstances. Or, you might want to "normalize" it out. Both are okay.
真正的答案是，在适当的情况下，可以将城市状态压缩数据保留在地址表中。或者，你可能想把它“正常化”。两者都是好的。
Find a good database administrator and hire them short term to review the plan, if it's in the budget. It will pay off in the long run.
找一个好的数据库管理员，短期雇佣他们来检查计划，如果计划在预算之内。从长远来看，这是值得的。
#2
11
The most obvious sign a table requires normalization that I've seen is fields ending with integers: CouponCode1, CouponCode2, CouponCode3.. you get the point. There will be exceptions to the rule as always though.
表中最明显的标志是，我所看到的是以整数结尾的字段:CouponCode1、CouponCode2、CouponCode3..你明白了吧。这条规则总会有例外的。
#3
9
Thirty fields is not too many - you just have to make sure your data is properly normalized (for which there are plenty of guides on the web).
30个字段并不太多——您只需确保您的数据是正确的规范化(web上有大量的指南)。
Based on your edit where you specify that many columns will be option-type fields which may be added or deleted over time, I would suggest the following is a better idea.
根据您的编辑，您指定许多列将是可随时间添加或删除的选项类型字段，我建议采用以下方法。
BaseTable:
Id
NonOptionFields
OptionTable:
Id
OptionName
OptionValue
Then you can tie all your options to the base record. This will mean you won't have to be adding and deleting columns to tables all the time ly normalized way to achieve what you want.
然后您可以将所有选项绑定到基本记录。这意味着您不必一直以规范化的方式向表添加和删除列，以实现您想要的结果。
#4
6
Of course, the standard answer is it depends. A table with that many fields could actually make quite a lot of sense in some situations.
当然，标准答案是要看情况而定。一个包含这么多字段的表实际上在某些情况下很有意义。
Think about the data you'll be storing in there. Is it likely that many of these fields will be NULL? What's the likelihood that these fields change (eg: more are added)?
想想你要存储的数据。这些字段中有很多可能是空的吗?这些领域发生变化的可能性是什么(如:增加更多)?
If only certain fields apply to certain objects, perhaps think about putting those fields into another table. Alternatively, store just the basic, common fields in one table, and the extra information in another table, one row per field. As I suggested for a different question (which might be helpful to you):
如果只有特定的字段应用于某些对象，或者考虑将这些字段放到另一个表中。或者，只在一个表中存储基本的、公共的字段，在另一个表中存储额外的信息，每个字段存储一行。如我所建议的另一个问题(可能对你有帮助):
refs (id, title, refType) -- title of the reference, and what type of reference it is fieldDef (id, fieldName, refType, dataType) -- name of the field, which reference types it applies to, and -- what type of data is stored in these fields (ISDN number, date, etc) fields (refId, fieldId, value) -- where you actually add data to the references.
Note that this was downvoted, and probably with good reason. This is an option, not necessarily the best option, but it's still a workable method. The highest voted answer in the question I linked to there might be the best solution however.
注意，这被否决了，而且可能有很好的理由。这是一个选项，不一定是最好的选项，但仍然是一个可行的方法。在我所联系的问题中，最高的投票回答可能是最好的解决方案。
Edit: since you say that it will be holding things like per-user settings (eg: widget colour), I'd actually recommend the method outlined above (with the three tables). Chances are that most people will leave things at the default, so you'll have a stack of useless information being stored. Please do read my answer in the other question because other readers have pointed out the shortcomings of this method.
编辑:既然你说它将保存像每个用户设置一样的东西(例如:小部件颜色)，我实际上会推荐上面概述的方法(包含三个表)。大多数人可能会把东西放在默认的位置，这样就会存储一堆无用的信息。请务必阅读我在另一个问题中的回答，因为其他读者已经指出了这种方法的缺点。
#5
4
there is no arbitrary limit; enough to get the job done is a good rule of thumb
没有任意的极限;完成这项工作是一个很好的经验法则
if you have a better db design, suggest it
如果你有一个更好的db设计，建议它。
if you want more detailed feedback, post the schema
如果您想要更详细的反馈，请发布模式
#6
4
The term "too many" is a relative one... You shouldn't split a table only for the sake of reducing the number of fields, especially if in every query you're going to have to join them back together because they are essentially one-to-one relationships. If the fields can be broken down into a separate, logical object then it would make sense. For example instead of storing address fields on a customer table, they could be moved into a separate address table. This is a crude example, but it illustrates my point.
“太多”这个词是相对的……您不应该仅仅为了减少字段的数量而拆分表，特别是如果在每个查询中都必须将它们连接到一起，因为它们本质上是一对一的关系。如果可以将字段分解成一个独立的、逻辑的对象，那么它将是有意义的。例如，与其将地址字段存储在客户表上，不如将它们移动到单独的地址表中。这是一个粗略的例子，但它说明了我的观点。
#7
3
The number of fields is usually not a problem, but you want to make sure your database is correctly noralized. Third normal form is a good start.
字段的数量通常不是问题，但是您需要确保您的数据库是正确的规范化。第三种形式是一个好的开始。
#8
2
If you have to ask, "Are there too many fields in this table?" Then there probably are.
如果你不得不问，“这个表格里有太多字段吗?”还有可能。
#9
2
OLTP
OLTP
From my experience of designing databases, there are very few tables in a normalized OLTP database that contain an insanely large number of columns.
根据我设计数据库的经验，规范化OLTP数据库中很少有包含大量列的表。
IMO 30 columns is too many.
IMO 30列太多了。
For me, no more than 10% of my OLTP tables have a large number(>10) of columns.
对于我来说，OLTP表中不超过10%的列数量很大(>10)。
OLAP
OLAP
Now if you're going to do a Dimensional / Reporting structure, some people may consider a 30 column table to be narrow.
现在如果你要做一个维度/报告结构，有些人可能会认为30列表很窄。
#10
1
The guerilla's guide to normalization-by-default:
《游击战规范》的默认指南:
- A table should have a primary key and at most one other column.
- 表应该有一个主键，最多只有一个其他列。
- Break rule number 1 only as often as required.
- 只在需要的时候打破规则1。
#11
1
There is no constraint on number of fields in database theory. A table can be limited to a primary key (even if this primary key is made of 2 fields), meaning that Apocalisp's answer is not very clear. At the opposit, a table can be made out of thousends of fields, as long as normal form rules are respected.
数据库理论中没有对字段数量的限制。一个表可以限制为一个主键(即使这个主键是由两个字段组成的)，这意味着罗布麻的答案不是很清楚。相反，只要遵守常规的形式规则，表格就可以由字段组成。
When groups of fields are obviously underused in a table, it can be smart to split this group of fields in another table with a 0-1 relation between the main table and the "sub" table.
当表中字段组明显未得到充分使用时，最好将这组字段分割到另一个表中，主表和“子”表之间的关系为0-1。
For security reasons, it was also often proposed (a long time ago: i think it was my first book of relationnal databases, first published in 197?) to split the confidential infos in another table with the same 0-1 relation between main and sub. It was then possible to easily restrict user access to "sub" table. Such a configuration can now be easily managed through views.
出于安全考虑,它也常常提出(很久以前:我认为这是我的第一本relationnal数据库,197年首次出版?)将机密信息在另一个表相同的0 - 1之间的关系主要和子。当时可能很容易限制用户访问表“子”。这样的配置现在可以通过视图轻松地进行管理。
#12
0
A tell-tale sign is just what you said. He has fields that should in theory be split out into a different table. Another giveaway is the presence of many optional fields.
你说的正是事实。他的字段理论上应该被分成不同的表格。另一个好处是有许多可选字段。
I'd say that a course in database design is in order for your DB "Expert". And I'd suggest that you brush up on it as well...it can only help you grow in your career :)
我想说，数据库设计的课程是为了让你的DB“专家”。我建议你也复习一下……它只会帮助你在事业上成长。