我确信这是一个基本问题，但我是 SQL 新手，所以无论如何，对于我的用户配置文件，我想显示以下内容：如果用户住在好莱坞，则位置 =“好莱坞，加利福尼亚州 - 美国”。所以我假设在用户表中会有 1 列，例如 current_city，其 ID 为 1232，它是城市表的 FK，其中该 PK 的 city_name = Hollywood。然后连接州表和国家/地区表以查找名称 CA 和 USA，因为城市查找表将仅存储 ID（例如 CA = 21 和 USA = 345）
这是设计表的最佳方式还是我在想我应该将诸如 city_id 和 city_name 之类的 2 列添加到 user_table 中吗？并且还将country_id、country_name、state_id、state_name 添加到city 表中。这样我就可以节省前往其他父表只是为了获取 ID 的名称的次数。
这只是一个示例用例，但我有很多查找 ID 表，因此一旦我知道如何做到最好，我就会将相同的原则应用于所有表。我的要求是可扩展性和性能，所以最适合这些的就是我想要的。
I am sure this is a basic question but I am new to SQL so anyways, for my user profile I want to display this: location = "Hollywood, CA - USA" if a user lives in Hollywood. So I assume in the user table there will be 1 column like current_city which will have ID say 1232 which is a FK to the city table where city_name for this PK = Hollywood. Then connect with the state table and the country table to find the names CA and USA as the city lookup table will only store the IDs (like CA = 21 and USA = 345)
Is this the best way to design the table OR I was thinking should I add 2 columns like city_id and city_name to the user_table. And also add country_id, country_name, state_id, state_name to the city table. This way i save on trips to other parent tables just to fetch the name for the IDs.
This is only a sample use case but I have lots of lookup ID tables so I will apply the same principle to all tables once i know how to do it best. My requirement is scalability and performance so whatever works best for these is what i would like.
发布评论
评论（1）
您描述的第一种方法几乎总是更好。
在用户表中同时拥有 city_id 和 city_name （或任何此类类型的对）并不是最佳实践，因为它可能会导致数据差异 - 错误的更新可能会导致 city_id 与 city_name 不匹配，然后系统行为变得意外。
如前所述，您的第一个建议是常见的，通常也是最好的方法。如果表键设计得当，那么所有 select 语句都可以有效地使用它们，这也将提供最佳性能。
例如，在用户表中只包含 city_name 可以更快地查找并显示一个用户的城市，但是当尝试运行其他查询时（例如查找城市 X 中的所有用户），这就没有意义了。
您可以在这里找到一系列适合初学者的有关数据库规范化的精彩文章：
http://databases.about.com/od/specificproducts/a/2nf.htm 。本文有一个示例，与您想要实现的目标非常相似，相关文章可能会帮助您设计数据库中的许多其他表。
祝你好运！
The first way you described is almost always better.
Having both the city_id and city_name (or any pair of that kind) in the users table is not best practice since it may cause data discrepancies - a wrong update may result in a city_id that does not match the city_name and then the system behavior becomes unexpected.
As said, your first suggestion would be the common and usually the best way to do this. If table keys are designed properly so all select statements can use them efficiently this would also give the best performance.
For example, having just the city_name in the users table would make it a little quicker to find and show the city for one user, but when trying to run other queries - like finding all users in city X, that would make much less sense.
You can find a nice series of articles for beginners about DB normalization here:
http://databases.about.com/od/specificproducts/a/2nf.htm. This article has an example which is very much like what you are trying to achieve, and the related articles will probably help you design many other tables in your DB.
Good luck!