数据库表设计
我有一张房产清单表。我需要将城市添加到这些列表中。将城市列表拆分到它自己的表中是最佳实践吗？
我希望用户在添加新属性时能够从城市列表中进行选择。
顺便说一句，这是一个 Rails 项目。
I have a table of property listings. I need to add cities to these listings. Is it best practice to split a list of cities into it's own table?
I would like the user when adding a new property to be able to select from a list of cities.
By the way this is a Rails project.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
在这种情况下，城市查找表是有意义的。
如果需要的话，这还允许您将来为每个城市添加更多信息。
A cities lookup table makes sense in this case.
This will also allow you to add more information for each city in the future, if needed.
如果每个属性只有一个城市，那么将其放入属性表中并没有什么大问题。如果更多的话，没有什么好的选择，只能使用城市表。
或者，如果您想从下拉列表中选择城市且不允许添加，那么拥有城市表可能是个好主意。如果您这样做，那么您可能希望在属性表中存储 cityid 而不是城市名称。这样，当有人更改城市名称时（诚然，这种情况可能不会经常发生），您只需更改一条记录。当然，如果您确实有城市表，则必须有外键并确保 city_id 在属性表中建立索引以维护数据完整性。
If there is only one city per property, there is nothing terribly wrong with putting it in the properties table. If there are more, there is no good choice but to use a cities table.
Alternatively, if you want to pick the cities from a drop down list with no additions allowed, having a cities table may be a good idea. If you do that then you probably want to store the cityid not the city name in the property table. That way when someone changes the name of a city (which admittedly probably doesn't happen very often) you only have to change one record. Of course if you do have a cities table, you must have a foreign key and make sure city_id is indexed in the properties table to maintain your data integrity.
是的。通常，最佳实践是规范化数据库架构，这样您就不会在房产列表表的多个房产列表记录中重复相同的城市名称。
在某些情况下，出于性能原因您可能希望进行非规范化。我不会认为您的情况属于这些情况之一，直到它证明自己是这样的（即表读取变得非常慢）。即使如此，您也可以在对模式进行非规范化之前进行一些优化。
Yes. It is generally a best practice to normalize your database schema such that you are not repeating the same city names in multiple property listing records in your property listing table.
There are cases where you would want to denormalize for performance reasons. I would not consider your case to be one of these cases until it proves itself to be (i.e. table reads become very slow.) Even then, there are optimizations you could undertake prior to denormalizing your schema.