创建新表或添加字段是否有意义
如果这只是询问 MySQL 表上的存储空间的另一种方式，我深表歉意，如下所示： MySQL 中的 NULL（性能和存储）
但我的问题确实与最佳实践有关。
问题
假设我有一个表，它在给定时间点收集客户的所有“必要”信息。 12 个月后，我意识到我也希望捕获此人的出生日期和性别（如果他们愿意的话）（字段数量有些无关紧要，但了解它可能是 1 或 50附加字段）。
最佳实践是将新字段添加到现有表中，设置并允许所有初始值为 NULL，然后使用新数据更新现有记录，
还是
创建新表并基于以下内容与主表建立关系主键是否存在？
I apologize if this is just another way of asking about storage space on a MySQL table such as these:
NULL in MySQL (Performance & Storage)
but my question really pertains to best practice.
The Problem
Let's assume I have a table which collects all 'necessary' information from a customer at a given point in time. 12 months down the road, I realize I would also like to capture the person's date of birth and gender if they wish to supply it (the number of fields is somewhat irrelevant but understand it could be one or 50 additional fields).
Is it best practice to add the new fields into my existing table, setting and allowing all initial values to be NULL, then updating existing records with the new data
OR
create a new table(s) and establish a relationship with the primary table based on the presence of the primary key?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
不必太担心列何时添加到表中，无论是今天还是 12 个月后。相反，只需考虑所涉及的关系即可。人与其性别和生日之间存在 1:1 的关系，因此将这些属性保留在现有表中而不是创建新表是有意义的。
Don't worry so much about when the columns are added to the table, whether it's today or 12 months later. Instead, just think about the relationships involved. There's a 1:1 relationship between a person and their gender and birthday, so it makes sense to keep these attributes in the existing table rather than creating a new one.
在我看来，最好向现有表中添加新列，因为以后维护它会更容易。而且，它在逻辑上属于同一个表。
In my opinion, it would be best to add new columns to existing table, because it would be much easier to maintain it later on the road. Also, it logically belongs in the same table.
我使用的经验法则是，如果我觉得该数据是该客户记录的扩展，将很少使用，我会使用扩展表并节省空间。
如果它是您的客户记录中不可分离的部分，我会选择一张带有空值的表。
在这里发挥作用的不仅仅是空间，还有特定产品的数据建模。
随着应用程序的发展和变化，建模可以帮助您理解并进一步设计应用程序。
希望有帮助。
a rule of thumb i use is that if i feel this data is an extension to this customer record that will be rarley used, i use extension table and save the space.
if it's a un-detachable part of your customer record i would go for one table with nulls.
it's not just the space that plays a part here but rather your data modeling for the specific product.
the modeling help you understand and further design your application as it grows and changes.
Hope it helped.
我建议添加新表。
关系模型中没有null的概念。使用封闭世界假设，通过关系（以及数据库）中缺失的信息来对缺失信息进行建模。 SQL 的三值逻辑不直观，并且会导致频繁出现错误（Stackoverflow 上有大量证据！）因此，不要试图在 SQL 表中引入可为空的值。标准化为最高范式 (6NF) 将导致您的新属性位于单独的表中。
另外，听起来您可能需要多个新表，例如一个用于出生日期，另一个用于不希望提供出生日期的人，另一个用于未要求提供出生日期的人（也许后者可以省略）而是从其他两个假设中推断出来，再次是封闭世界假设）。
小要点：性别 = 女性、男性等，性别 = 女性、男性等。
I suggest adding new tables.
There is no concept of null in the relational model. Using the Closed World Assumption, model missing information by its absense from a relation (and hence the database). SQL's three value logic is unintuitive and leads to frequent bugs (plenty of evidence of this on Stackoverflow!) Therefore, do not seek to introduce nullable values into your SQL tables. Normalizing to the highest normal form (6NF) would result in your new attributes being in separate tables.
Also, it sounds like you may need multiple new tables e.g. one for date of birth, another for person did not wish to supply date of birth and another for person has not been asked to supply their date of birth (perhaps the latter can be omitted and instead inferred from the other two, Closed World Assumption again).
Minor point: gender = feminine, masculine, etc, sex = female, male, etc.
我不是这方面的专家，所以这可能不是一个好的答案。只是想提出一点观点：
向现有表添加列可能有一个缺点：如果您使用 ORM（例如 hibernate），当您运行选择查询时，它将获取所有列。因此，如果行数变大，则需要获取更多数据，因此查询可能会变慢。如果该列不经常使用，那么创建单独的表可能有意义吗？
I am not an expert here, so this might not be a good answer. Just wanted to put one point in perspective:
There could be one downside of adding columns to existing table : If you are using an ORM (e.g. hibernate), it will fetch all the columns when you run a select query. So if the number of rows become larger there's more data to fetch so query may become slower. If the column is not used frequently it might make sense to create separate table?