如何设计“国家”、“省份”和“城市”桌子？
示例 1：
xTable: xTableID, CountryName, PovinceName, CityName
- 或
示例 2：
Country: CountryID, CountryName
Province: ProvinceID, ProvinceName
City: CityID, CityName
问题：
您是否建议我在应用程序级别填充国家、省份和城市列表，然后使用示例 1 设计？或者我应该像示例 2 那样在数据库级别填充它吗？
城市将根据所选省份（如果有）或国家/地区名称显示。省份（如果有）将根据所选国家/地区显示。省份仅适用于一个国家，城市仅适用于一个省份/国家。没有一对多的关系，所以我将其设计如下
注意：每个国家肯定会有一个城市，但不是每个国家都会有一个省名。
Country: CountryID (PK), CountryName
Province: CountryID (PK - FK), ProvinceName
City: CountryID (PK - FK), CityName
EXAMPLE 1:
xTable: xTableID, CountryName, PovinceName, CityName
- OR
EXAMPLE 2:
Country: CountryID, CountryName
Province: ProvinceID, ProvinceName
City: CityID, CityName
Question:
Do you recommend me to populate country, province and city lists at application level and then use EXAMPLE 1 design? or Should i populate it at DB level as in EXAMPLE 2?
City will show based on chosen province (if any) or country name. Province (if any) will show based on chosen country. Province will be only for one country and city will only be for one province/country. No one/many to many relations, so i designed it as below
NOTE: every country will have a city surely but not every country will have a province name.
Country: CountryID (PK), CountryName
Province: CountryID (PK - FK), ProvinceName
City: CountryID (PK - FK), CityName
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您需要从城市到国家、省到国家、城市到省的直接关系。
所有省份都在一个国家/地区，因此省份表需要一个 CountryID 作为 FK。同样，所有城市都在一个国家内，所以那里都是一样的。
由于并非所有国家/地区都有可以称为省份的区域，因此它们不能用于从城市导航到国家/地区。 ProvinceID 应位于 City 表中，但允许空值。
或者，您可以为没有省份的国家建立虚拟省份（例如，省份名称 = 无省份），并使用它们将城市与国家联系起来。如果有一个城市位于一个有省份的国家，但它本身并不在一个省份（如果存在这种情况，可能该省份就是该城市），那么这也将起作用。
You need a direct relationship from City to Country, Province to Country and City to Province.
All provinces are in a country, so the Province table needs a CountryID as FK. Likewise all Cities are in a Country, so same there.
As not all Countries have regions that could be called Provinces they can't be used to navigate from City to Country. ProvinceID should be in the City table, but allow null values.
Alternatively you could establish virtual provinces (e.g. Province name = No Province) for countries without Provinces and use them to relate City to Country. That would also work if there was a city which was in a country that has provinces, but wasn't itself in a province (if such a thing exists, possibly the province IS the city).
我建议您将 ProvinceId 和 CityId 添加到表 Province 和 City 中。一个省可以有很多城市，同样一个国家可以有很多省。无需将countryId保留在城市中，ProvinceId就足够了，因为它将由省份触发，而不是由国家/地区触发。
I suggest you to add ProvinceId and CityId to tables Province and City. A province can have many cities likewise a country can have many provinces. No need to keep countryId in city, ProvinceId would be enough since it will be triggered by Province but not country.
我已经修改并使用了它并取得了一些成功：
http://www.endswithsaurus.com/2009/07/ Lesson-in-address-storage.html
写这篇文章的人是一个非常活跃的SO用户：
本阿拉巴斯特
I have modified and used this with some success:
http://www.endswithsaurus.com/2009/07/lesson-in-address-storage.html
The person who wrote the article is a very active SO user:
BenAlabaster