用户和地址应该位于不同的表中吗？
目前我的用户表有以下字段
我不确定是否应该将地址字段放在另一个表中。我听说如果不这样做我就会违反 3NF，尽管我不明白为什么。有人可以解释一下吗？
Currently my users table has the below fields
I'm not sure if I should put the address fields in another table. I have heard that I will be breaking 3NF if I don't although I can't understand why. Can someone please explain?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
有几点绝对不是3NF；另外还有一些值得怀疑的问题：
如果这些问题中任何一个的答案是肯定的，则表明该区域存在 3NF 问题。 3NF的原因是为了去除重复数据；确保更新、插入和删除使数据保持一致的形式；并最大限度地减少数据存储 - 特别是不需要将数据存储为“未知/未知/空”。
除了这里提出的问题之外，还有一个问题是什么构成了表的主键 - 我猜这与用户有关，但是您提供的名称和其他信息不太可能是唯一的，因此不足以作为PK。 （如果您认为名字加姓氏是唯一的，您是否暗示您永远不会有多个约翰·史密斯？）
编辑：
鉴于某些字段是可选的进一步信息，我建议您将可选字段分离到不同的表中，并在新表和用户表之间建立 1-1 链接。该链接将通过在新表中创建引用用户表主键的外键来建立。正如您所说，没有一个字段可以有多个值，那么它们目前不太可能给您带来问题。然而，如果其中任何一个发生变化，那么不将它们分开将会给您带来升级应用程序和支持应用程序的数据的问题。您仍然需要解决主键问题。
There are several points that are definitely not 3NF; and some questionable ones in addition:
If the answer to any of these questions is yes, then it indicates a problem with 3NF in that area. The reason for 3NF is to remove duplication of data; to ensure that updates, insertions and deletions leave the data in consistent form; and to minimise the storage of data - in particular there is no need to store data as "not yet known/unknown/null".
In addition to the questions asked here, there is also the question of what constitutes the primary key for your table - I would guess it is something to do with user, but name and the other information you give is unlikely to be unique, so will not suffice as a PK. (If you think name plus surname is unique are you suggesting that you will never have more than one John Smith?)
EDIT:
In the light of further information that some fields are optional, I would suggest that you separate out the optional fields into different tables, and establish 1-1 links between the new tables and the user table. This link would be established by creating a foreign key in the new table referring to the primary key of the user table. As you say none of the fields can have multiple values then they are unlikely to give you problems at present. If however any of these change, then not splitting them out will give you problems in upgrading the application and the data to support the application. You still need to address the primary key issue.
只要每个用户都有一个地址，并且每个地址都属于一个用户，它们就应该位于同一张表中（一对一的关系）。但是，如果不需要用户输入地址（可选关系），则单独的表将是合适的。另外，在许多用户共享相同地址的奇怪情况下（例如，他们是同一所监狱的囚犯），您将具有一对多关系，在这种情况下，可以使用单独的表。编辑：是的，正如有人在评论中指出的那样，如果用户有多个地址（一对多，反之亦然），也应该有单独的表。
As long as every user has one address and every address belongs to one user, they should go in the same table (a 1-to-1 relationship). However, if users aren't required to enter addresses (an optional relationship) a separate table would be appropriate. Also, in the odd case that many users share the same address (e.g. they're convicts in the same prison), you have a 1-to-many relationship, in which case a separate table would be the way to go. EDIT: And yes, as someone pointed out in the comments, if users have multiple address (a 1-to-many the other way around), there should also be separate tables.
正如我认为可能对解决这个问题的人有所帮助一样，我曾经遇到过一种情况，我将地址直接放在用户/站点/公司/等表中，因为我想，为什么我需要多个地址呢？然后，在我们完成所有工作后，另一个部门引起我的注意，我们需要记录送货地址和帐单地址的可能性。
这个故事的寓意是，这是一个常见的要求，因此，如果您认为您曾经可能想要记录送货和帐单地址，或者可以想到任何其他类型的您可能想为用户记录地址，请继续将其放入单独的表中。
在当今时代，我认为电话号码存储在单独的表中也是理所当然的。每个人都有手机号码、家庭号码、工作号码、传真号码等，即使你只打算要一个，人们仍然会在字段中输入两个并用分号分隔（相信我）。只是数据库设计中需要考虑的其他事情。
Just as point that I think might help someone in this question, I once had a situation where I put addresses right in the user/site/company/etc tables because I thought, why would I ever need more than one address for them? Then after we completed everything it was brought to my attention by a different department that we needed the possibility of recording both a shipping address and a billing address.
The moral of the story is, this is a frequent requirement, so if you think you ever might want to record shipping and billing addresses, or can think of any other type of address you might want to record for a user, go ahead and put it in a separate table.
In today's age, I think phone numbers are a no brainer as well to be stored in a separate table. Everyone has mobile numbers, home numbers, work numbers, fax numbers, etc., and even if you only plan on asking for one, people will still put two in the field and separate them by a semi-colon (trust me). Just something else to consider in your database design.
关键是，如果您想象将来同一用户有两个地址，您应该立即拆分并拥有一个地址表，其中 FK 指向用户表。
PS 你的表缺少一个用作 PK 的身份，例如 Id 或 UserId 或 DataId，按照你想要的方式称呼它......
the point is that if you imagine to have two addresses for the same user in the future, you should split now and have an address table with a FK pointing back to the users table.
P.S. Your table is missing an identity to be used as PK, something like Id or UserId or DataId, call it the way you want...
通过将它们添加到单独的表中，如果您以后决定这样做，您将可以更轻松地扩展您的应用程序。我通常有一个简单的用户表，其中包含 user_id 或 id、user_name、first_name、last_name、password、created_at &更新于。然后我有一个包含其他信息的配置文件表。
不过这确实是所有偏好。
By adding them to separate table, you will have a easier time expanding your application if you decide to later. I generally have a simple user table with user_id or id, user_name, first_name, last_name, password, created_at & updated_at. I then have a profile table with the other info.
Its really all preference though.
您永远不应该将两种不同类型的数据分组在一个表（句点）中。原因是，如果您的应用程序打算在生产中使用，迟早会出现不同的用例，这将需要您更高的规范化表结构。
我的建议 - 即使在数据库设计中也坚持 SOLID 原则。
You should never group two different types of data in a single table, period. The reason is if your application is intended to be used in production, sooner or later different use-cases will come which will need you to higher normalised table structure.
My recommendation - Adhere to SOLID principles even in DB design.