什么是设计城市，州，国家表的最佳方式？ [英] what is the best way to design a city, state, country table?

问题描述

我需要帮助设计我的国家，城市，州表。我将从我的表中提供样本数据，以便您可以更好地帮助我解决我的问题。

I need help designing my country, city, state tables. I will provide sample data from my table so that you can help me better on my problem.

这是我的国家表：

Country
______
code   name
US     United States
SG     Singapore
GB     United Kingdom

这是我的城市表：

City
_____
id   country   city        state
1    US        Birmingham  Alabama
2    US        Auburn      Alabama
.
.
29   GB        Cambridge   NULL
30   GB        Devon       NULL

我的问题是只有具有州领域的国家是美国。所有其他城市都有一个空值。

My problem is that the only country that has the state field is the US. All other cities have a null value.

我的临时解决方案是为美国创建一个特殊的城市表，然后所有其他国家有另一个城市表没有状态字段。

My temporary solution for this is to just create a special city table for the United States, then all other countries have another city table that doesn't have the state field.

我认为这会使问题复杂化，因为我有两个城市表。

I think this will just complicate the matter, because I have two tables for cities.

如何改进这个设计？

推荐答案

为什么不去关系？

Country ( CountryID, CountryCode, CountryName )
Region  ( RegionID, RegionCode, RegionName, CountryID )
City    ( CityID, CityCode, CityName, RegionID )

Region名称比State更通用，这意味着更有意义。

The 'Region' name is a big more generic than State, which means it would likely make more sense everywhere.

这篇关于什么是设计城市，州，国家表的最佳方式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！