国家/地区/城市数据模型
国家：国家ID、国家名称
区域：regionID、countryID (FK)、regionName
城市：cityID、regionID (FK)、cityName
- 所有国家/地区都会有城市。
- 并非所有国家都会有地区。区域将仅包含以下行 有国家ID。只有一行的regionID为9999，countryID为NULL 区域名称为“无”。
- 任何城市没有regionID，都会引用9999regionID。
前端场景：
- 用户从下拉列表中选择一个国家
- /地区下拉列表已填充
- 城市下拉列表已填充 - 只有城市属于区域
我可以在我的设计中看到一个错误，它仅对具有区域的国家/地区有效。 如果国家/地区没有区域，则只需填充城市列表并忽略区域列表。
我可以将 CountryID 和 RegionID 放入 City 表中。不过，我需要你的建议。
长期稳定的最佳解决方案/设计是什么？
Country: countryID, countryName
Region: regionID, countryID (FK), regionName
City: cityID, regionID (FK), cityName
- All countries will have cities.
- Not will all countries will have regions. Region will only carry rows that
has countryID. There is only one row thats has regionID 9999 and countryID is NULL
regionName is NONE. - Any city has no regionID, it will be referred to 9999 regionID.
Front End Scenario:
- User picks a country from drops down list
- Region drop down gets populated
- City drop down gets populated - only city belongs to Region
I can see a mistake in my design, its only valid for country with regions.
If country has no regions then just populate city list and ignore that the regions list.
I could just put countryID and regionID within City table. However, i need your suggestion.
What would best solution/design that would be stable for the long run?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
如果：
和：
然后：
是最明显的建模方法。正如您所建议的，它不是分层模型。有 3 个独立的关系：
因此，您需要明确捕获它们。但是，您需要逻辑来确保一致性，即防止出现以下情况：
另一种选择是在同一个表中捕获国家和地区，我们将其称为“区域”：
这样做可以消除上述问题。每个城市都有一个指向区域表的强制性 FK，根据需要指向“国家/地区”或“地区”条目。它还允许层次结构更加灵活；例如，可以在区域内添加区域（如果需要），和/或添加其他类型的区域（例如大陆）。但是，您仍然需要强制执行一些逻辑（例如，无法将区域添加为国家/地区的父级）。
这两种解决方案都可行；哪个最好取决于其他要求（例如写入次数与读取次数）。
嗯。
If:
and:
then:
is the most obvious way to model it. As you suggest, it's not a hierarchical model. There are 3 separate relations:
Therefore you need to capture them explicitly. However you'll need logic to ensure consistency, i.e. to prevent situations such as:
Another option would be to capture Countries and Regions in the same table, lets call it
Area:Doing so removes the problem above. Each city has a single, mandatory FK to the Area table, pointing to either a 'Country' or 'Region' entry as appropriate. It also allows more flexibility in the hierarchy; e.g. it's possible to add regions within regions (if necessary), and/or add other types of area (e.g. Continent). However you'll still need to enforce some logic (e.g. can't add a Region as parent of a Country).
Either solution could work; which is best will depend on other requirements (e.g. number of writes vs. number of reads).
hth.
为什么不在城市表中也有一个国家ID，这样您就可以根据您当时的需要映射回地区或国家
why not have a countryID in the city table as well so you can map back to either region or country depending on your need at the time
好吧，如果你将regionID放入你的城市表中，如果你没有region，它在你的城市表中也将为空。所以没有任何好处。
您正在询问 0 对多关系，这是一种特殊形式的 1 对多关系。您允许区域部分为 NULL。如果将来您的国家“X”有区域，您以前的设计从长远来看是可行的。
Well,if you put regionID in you city table,and if you don't have region,it would be null in your city table too.So no benefit.
You are asking about a 0-To-Many relationship which is a 1-to-many relationship in a special form.you allow NULL for the region part. Your previous design is feasible for the long run if in future your country 'X' have regions.
你在这里有两个不同的问题。
尽管这两个问题有些交互，但它们之间确实没有太大关系。
对于数据库设计问题，旧金山的“全名”是
这与
San Francisco, Alabama, United States of America区分开来，后者甚至不存在。而且，更进一步，加利福尼亚州的全名是
这，以区别于
加利福尼亚州、苏格兰，后者同样不存在。这对您来说是一种有用的方法吗？
You have two different issues here.
Although those two issues interact somewhat, they really don't have much to do with each other.
For the database design issue, the "full name" of San Francisco is
That distinguishes it from, say,
San Francisco, Alabama, United States of America, which doesn't even exist.And, going a step further, the full name of California is
That distinguishes it from, say,
California, Scotland, which likewise doesn't exist.Does that suggest a useful approach to you?
我更喜欢原来的选项：tblCountry -> tblRegion->城市。如果不存在区域，则“区域 = 国家/地区”添加一个复选框以指示区域不存在并忽略重复条目。只是一个想法。
I prefer the original option: tblCountry -> tblRegion -> tblCity. If no Region exists have Region = Country add a check box to indicate Region does not exist and ignore duplicate entry. Just a thought.