SQL表设计建议
我正在建立一个社区网站，通过电子邮件登录，成员将能够更改他们的姓名/昵称。
您认为我应该将会员姓名/昵称与会员的其他属性一起保留在会员表中，还是创建另一个表，在该表上写入会员姓名/昵称并关联会员的 ID。
我赞成第二种选择，因为我认为从中提取成员姓名会更快。
这是正确/更好的方法吗？
更新：其他表的原因是我需要提取不同部分的用户名。例如论坛。在一个小表中查询来自主题中每个帖子的每个用户名不是更快吗？
I am building a community site where logon will be by email and members will be able to change their name/nick name.
Do you think I should keep member name/nick name in my members table with other properties of member or create another table, write member name/nick name on that table and associate member’s id.
I am in favour of second option because, I think it would be faster to pull members name from it.
Is it right/better way?
Update: reason is for other table is that I need to pull username for different sections. For example forums. Wouldn't it be faster to query a small table for each username for each post in a from topic?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我会将其保留在一张表中，并对该表中的电子邮件设置唯一的约束。
我看不出添加另一个表有什么好处。
I would keep it one table and set a unique constraint on Email in that table.
I can't see a single advantage in adding another table.
为什么您认为第二种选择会更快？
如果昵称与会员 ID 需要一对一关系，则存储它们的适当位置是在同一个表中。这仍然是索引单记录搜索，因此它应该或多或少与其他选项一样快。
事实上，这个解决方案可能会更快，因为您可以在获取其他信息的相同 SELECT 中获取昵称。
更新以回答问题的更新：
第二个表的行数并不小。 SQL 搜索中的主要因素是 1) 表中的记录数和 2) 搜索的索引部分中可能匹配的数量。
在这种情况下，较小表中的记录数将与较大表中的记录数完全相同。并且索引返回的可能匹配记录的数量将始终为1，因为成员ID是唯一的。
您正在搜索的表中的列数通常与返回数据所花费的时间无关（您在 SELECT 语句中实际列出的列数可能会产生影响，但无论您在哪个表中，这都是相同的）搜索）。
SQL 数据库非常非常擅长查找数据。正确构建数据并让数据库负责将其恢复。正如他们所说，过早的优化是万恶之源。
Why do you think the second option would be faster?
If nickname is a required one-to-one relation to member ID the appropriate place to store them is in the same table. This is still a indexed single-record search so it should be more-or-less as fast as your other option.
In fact, this solution would probably be faster, since you could get the nickname in the same SELECT as you get the other information.
Update to answer the update to the question:
The second table isn't any smaller in terms of the number of rows. The main factors in a SQL search are 1) number of records in the table and 2) number of possible matches from the indexed part of the search.
In this case, the number of records in your smaller table would be exactly the same as the larger table. And the number of possible matching records returned by the index will always be 1 because the member ID is unique.
The number of columns in the table you're searching is generally irrelevant to the time taken to return the data (the number of column you actually list in the SELECT statement can have an effect, but that's the same no matter which table you're searching).
SQL databases are very, very good at finding data. Structure your data correctly and let the database worry about getting it back to. Premature optimization is, as they say, the root of all evil.
选择第一个选项：将姓名/昵称保留在成员表中。在这种情况下，无需引入额外的表以及随之而来的联接的开销。
Go with the first option: keep the name/nick name in the members table. There's no need to introduce an additional table, and the overhead of a join that goes with it, in this case.
是的，将会员 ID 与其他属性相关联是正确的方法。
您只需在名称上创建索引即可加快查询速度。
Yes, associating member's ID to the other properties is the right way to go.
You can simply create an index on name to speed up your queries.