用户和地址是否应该在单独的表中？ [英] Should User and Address be in separate tables?
问题描述
目前，我的用户表具有以下字段
Currently my users table has the below fields
我不知道我是否应该将地址字段放在另一个表中。我听说如果我不明白为什么我将打破3NF。有人可以解释一下吗？
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?
推荐答案
有几点绝对不是3NF;另外还有一些可疑的：
There are several points that are definitely not 3NF; and some questionable ones in addition:
- 每个用户可能有多个地址？
- 地址是可选还是必填？
- 城市，国家/地区中的信息在地址中是否重复？
- 用户是否可以使用多个TelNos？
- TelNo是可选还是强制性？
- >
- 是否可选或强制使用MobNo？
- 用户可以使用多个电子邮件吗？
- 电子邮件是可选的还是必需的
- 是否根据用户数量计算NoOfMembers？
- 可以有多个UserAttempts？
- 每个用户可以有多个BlockTime吗？
- Could there could be multiple addresses per user?
- Is an address optional or mandatory?
- Does the information in City, Country, Region duplicate that in Address?
- Could a user have multiple TelNos?
- Is a TelNo optional or mandatory?
- Could a user have multiple MobNos?
- Is a MobNo optional or mandatory?
- Could a user have multiple Emails?
- Is an Email optional or mandatory?
- Is NoOfMembers calculated from the count of users?
- Can there be more than one UserAttempts?
- Can there be more than one BlockTime per user?
如果任何这些问题的答案是肯定的，那么它表示问题在该地区有3NF。 3NF的原因是删除重复的数据;确保更新，插入和删除以一致的形式保留数据;并尽可能减少数据的存储 - 特别是不需要将数据存储为尚未知/未知/空。
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".
除了这里提出的问题还有一个问题是什么构成了你的桌面的主要关键 - 我猜这是与用户有关的，但是你所提供的名称和其他信息不太可能是唯一的，所以不能像PK一样。 （如果你认为名字加姓是独一无二的，你建议你永远不会有一个约翰·史密斯？）
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?)
编辑：
鉴于进一步某些字段是可选的信息，我建议您将可选字段分离成不同的表，并在新表和用户表之间建立1-1个链接。该链接将通过在新表中创建外键来引用用户表的主键来建立。你说没有一个字段可以有多个值，那么它们现在不大可能给你问题。然而，如果任何这些更改，那么不会将它们分解出来将会在升级应用程序和数据以支持应用程序时提供问题。您仍然需要解决主要问题。
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.
这篇关于用户和地址是否应该在单独的表中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
