数据库设计：1 个表还是 2 个表？
我见过一些数据库设计，其中帐户表中包含所有用户信息，包括密码、电子邮件、出生日期、名字、姓氏等。
我见过其他一些数据库设计有两个表
用户名（或电子邮件）、密码、状态（激活等）、组（管理员、所有者、用户等）
和
名字、姓氏、出生日期、出生月份、出生年份等
上述方法的优缺点是什么？
I have seen a few database designs where it has all user information in an account table, including password, email, DOB, First Name, Last Name etc.
I have seen some others that have two tables
username(or email), password,state(activated etc), group(admin, owner, user etc)
and
nameFirst, nameLast, birthDay,birthMonth,birthYear etc
What are the pro's and cons of the above methods?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
两种设计之间的区别主要在于灵活性。如果帐户和用户数据共享一个表，则每个用户必须有一个帐户，并且每个帐户只能有一个用户（除非您添加另一个表以允许除了与该帐户一起生活的用户之外还添加子用户）数据，或者除非您在每条记录中添加具有重复帐户详细信息的新记录，这是非常糟糕的并且与数据库应该做的事情相反）。
使用两个表，您可以轻松地在每个帐户中拥有多个用户，并且还可以选择允许帐户没有用户或用户没有帐户的情况，如果这样做对您的用例有利的话。
权衡是，如果您想要执行诸如确定用户（或帐户中的用户）的帐户之类的操作，并且使用两个表，则必须进行联接。如果您有一个表，您所要做的就是获取行来获取此信息。
The difference between the two designs is mostly one of flexibility. If the account and user data share a single table, then each user must have an account, and each account can have only one user (unless you add another table to allow child users to be added in addition to the user that lives with the account data, or unless you add new records with duplicate account details in each one, which is very bad and antithetical to what databases are supposed to do).
With two tables, you can easily have multiple users in each account, and might also choose to allow circumstances where an account has no users, or where a user does not have an account, if doing so would benefit your use-case.
The tradeoff is that if you want to do something like determine the account for a user (or the user(s) in an account), you have to do a join if you are using two tables. If you have one table all you have to do is fetch the row to get this information.
嗯，明显的主要问题是，如果您想从两个表中获取信息，则必须处理两个表。这可能会使您的查询稍微复杂化并可能降低性能。
由于所有这些信息都依赖于单个关键字段（此处最有可能是用户名），因此我倾向于将其全部放在一个表中，除非在一种非常特定的情况下：例如，如果您想给出有人可以访问第一个表中的详细信息，但不能访问第二个表中的详细信息，出于安全目的，您可以将其拆分（向所有人开放第一个表，但将第二个表限制为仅需要额外详细信息的人 - 但我可能会移动在这种情况下是第二个表的密码）。
除此之外，我会尽量减少对象的数量，只要它不妨碍维持第三范式。
Well, the obvious main problem is that you have to deal with two tables if you want information from both. That's likely to complicate your queries a little and possibly reduce performance.
Since all that information is dependent on a single key field (username most likely here), I tend to put it al in one table except in one very specific scenario: if, for example, you wanted to give someone access to the details in the first table but not the second, you could split it up for the purposes of security (opening up the first to everyone but restricting the second to only those who need the extra detail - but I'd probably move the password to the second table in that case).
Other than that, I'd minimise the number of objects as long as it didn't get in the way of maintaining third normal form.
我相信这取决于数据库设计者你。只要您将来通过服务器端语言进行工作并不困难 - 它就是一个可行的解决方案。
我已经在一个表中设置了带有凭据和敏感数据（加密）的数据库，并在另一个表中设置了所有其他内容。我还设置了数据库，其中有一个表来容纳所有这些数据。
无论哪种方式，只需要 1 个查询语句即可从一个或两个表中获取和/或操作数据。
I believe it is up to you the database designer. As long as it is not difficult for you to work with in the future via server side languages - it is a viable solution.
I have set up dbs with credentials and sensitive data (encrypted) in one table and all the other stuff in another table. I have also set up dbs that have a table to house all of that data.
Either way it takes only 1 query statement to get and/or manipulate the data from one or two tables.
如果我们谈论同一个“对象”（用户和她的额外信息属于一起），我更愿意避免将信息保存在单独的表中。
但我可以想到将它们分开的两个很好的理由：
如果您已经设计或正在使用带有自己的表的单独身份验证系统，例如
User，但您需要添加额外的内容信息。或者您有一个标准系统，但用户的信息/字段取决于您的客户端：字段名称、字段数量......然后您可以保持身份验证部分标准，并且已知额外信息部分是灵活的。如果在您的数据模型中您有一个复杂的
Person/People模型，其中包含地址、出生日期等您没有的信息，您可以选择使用同一个表来为您的用户存储该信息以及。因此，您的用户也会有一个person_id或类似的东西。希望这有帮助。
Keeping information in seperate tables if we are talking about the same 'object' (the user and her extra information belong together), is something i prefer to avoid.
But I can think of two good reasons to split them up:
If you have designed, or are using, a seperate authentication system with its own table, e.g.
User, but you need to add additional information. Or you have a standard system, but the information/fields for a user depends on your client: names of fields, amount of fields ... Then you can keep the authentication part standard, and the extra information part is known to be flexible.If inside your data-model you have an elaborate
Person/Peoplemodel, with adresses, birthdates, what have you not, you could choose to use the same table to store that information for your users as well. So youuserwould then also have aperson_idor something similar.Hope this helps.