有多少个字段就“太多”了？ 在桌子上？
我有一位同事正在为一个新应用程序规划一个数据库，该应用程序将包含多个表，每个表包含 30 多个字段。 这是否太过分了？ 也许我只是不够进取，无法理解。
编辑：另外，很多字段都是选项类型的东西（比如在请求表单上，您希望您的小部件是黄色还是绿色，他有一个带有枚举的“颜色”字段）。 随着时间的推移，这些内容很可能会被添加或删除。 我还没有真正完成数据库设计并尝试自己远离它，所以也许我完全愚蠢，但肯定有更好的方法来做到这一点？
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.
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??
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（12）
我见过的表需要标准化的最明显标志是以整数结尾的字段：CouponCode1、CouponCode2、CouponCode3.. 你明白了。 但一如既往，规则也会有例外。
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.
数据库表中可以合法地包含 30 个或更多字段。 您需要关注的是数据的标准化以及标准化是否有意义。 未来通常也会发生变化。 但是，您想尽量减少这种情况。
例如，如果您有一个包含地址的表，那么该表中是否包含城市、州和邮政编码字段？ 或者，您是否只包含一个“指向”这些值的单独表中的记录的字段？ 单独的表将包含唯一的城市、州、邮政编码组合。 将数据拆分为两个表的效果是减少了存储的数据量（很可能但不是绝对），但在对数据库运行查询时会增加一些复杂性。 现在，您必须处理两张表，而不是一张表。 但是，从好的方面来说，它更干净，而且更小（可能）。
真正的答案是，在正确的情况下将城市-州-邮政编码数据保留在地址表中是可以的。 或者，您可能想将其“标准化”。 两个都还好。
找到一名优秀的数据库管理员，并短期雇用他们来审查计划（如果预算范围内）。 从长远来看，它会得到回报。
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.
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.
三十个字段并不算太多 - 您只需确保您的数据正确标准化（网络上有大量指南）。
根据您指定的许多列将是选项类型字段（可能会随着时间的推移而添加或删除）的编辑，我建议以下是更好的主意。
然后您可以将所有选项与基本记录联系起来。 这意味着您不必一直以规范化的方式向表添加和删除列来实现您想要的目的。
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).
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.
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.
当然，标准答案是视情况而定。 在某些情况下，具有如此多字段的表实际上可能非常有意义。
考虑一下您将存储在其中的数据。 这些字段中的许多字段是否有可能为 NULL？ 这些字段发生变化的可能性有多大（例如：添加更多字段）？
如果只有某些字段适用于某些对象，也许可以考虑将这些字段放入另一个表中。 或者，仅将基本的公共字段存储在一个表中，将额外信息存储在另一个表中，每个字段一行。 正如我建议的另一个问题（可能对您有帮助）：
请注意，此问题被否决，并且可能有充分的理由。 这是一个选项，不一定是最好的选项，但它仍然是一个可行的方法。 然而，我链接到的问题中得票最高的答案可能是最好的解决方案。
编辑：既然您说它将保存诸如每个用户设置之类的内容（例如：小部件颜色），我实际上会推荐上面概述的方法（使用三个表格）。 大多数人很可能会保留默认设置，因此您将存储一堆无用的信息。 请阅读我在另一个问题中的回答，因为其他读者已经指出了这种方法的缺点。
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):
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.
没有任意限制； 足以完成工作是一个很好的经验法则
如果您有更好的数据库设计，
，如果您想要更详细的反馈，请建议它，发布架构
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
if you want more detailed feedback, post the schema
术语“太多”是一个相对的概念......您不应该仅仅为了减少字段数量而拆分表，特别是如果在每个查询中您都必须将它们重新连接在一起，因为它们是本质上是一对一的关系。 如果这些字段可以分解为一个单独的逻辑对象，那么这就有意义了。 例如，可以将地址字段移动到单独的地址表中，而不是将它们存储在客户表上。 这是一个粗略的例子，但它说明了我的观点。
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.
字段的数量通常不是问题，但您需要确保数据库正确规范化。 第三范式是一个好的开始。
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.
OLTP
根据我设计数据库的经验，标准化 OLTP 数据库中很少有表包含大量列。
IMO 30 列太多了。
对于我来说，不超过 10% 的 OLTP 表具有大量（>10）列。
OLAP
现在，如果您要创建维度/报告结构，有些人可能会认为 30 列的表很窄。
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.
IMO 30 columns is too many.
For me, no more than 10% of my OLTP tables have a large number(>10) of columns.
OLAP
Now if you're going to do a Dimensional / Reporting structure, some people may consider a 30 column table to be narrow.
如果你要问，“这个表的字段是否太多了？” 那么可能有。
If you have to ask, "Are there too many fields in this table?" Then there probably are.
默认标准化游击指南：
The guerilla's guide to normalization-by-default:
数据库理论中对字段的数量没有限制。 表可以限制为一个主键（即使该主键由 2 个字段组成），这意味着 Apocalisp的答案不是很清楚。 相反，只要遵循范式规则 受到尊重。
当表中的字段组明显未得到充分利用时，明智的做法是将这组字段拆分到另一个表中，并在主表和“子”表之间建立 0-1 关系。
出于安全原因，也经常有人提出（很久以前：我认为这是我的第一本关系数据库书，于 197 年首次出版？）将机密信息拆分到另一个表中，主表和表之间具有相同的 0-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.
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.
一个明显的迹象就是你所说的。 理论上，他的字段应该拆分到不同的表中。 另一个赠品是存在许多可选字段。
我想说数据库设计课程适合您的数据库“专家”。 我建议你也复习一下......它只会帮助你在职业生涯中成长:)
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 :)