设计城市、州、国家表格的最佳方式是什么？

Question

我需要帮助设计我的国家、城市和州的表格。我会提供来自我的表格的样本数据，以便您更好地帮助我解决问题。

这是我的国家表格：

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

我的问题是，唯一有州字段的国家是美国。所有其他城市都具有null值。

我目前的解决方案是为美国创建一个特殊的城市表，然后所有其他国家都有另一个不带州字段的城市表。

我认为这只会使问题更加复杂，因为我要使用两个城市表。

如何改进设计？

- Kevin Lee

---

简单粗暴的解决方案是下载 Virtuemart 并使用其中的国家/州表 - 它有 200 多个国家和所有相关的州，然后再添加你的城市。 - GDP

Answer 1

为什么不使用关系型数据库？

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

“区域”这个词比“州”更加通用，意味着在任何地方使用都更加合理。

- judda

Answer 2

为什么不采用标准的三向链接表集？

table country (
   id   int primary key,
   name varchar(255)
);

table state (
    id int primary key,
    name varchar(255),
    country_id int foreign key country (id)
);

table city (
    id int primary key,
    name varchar(255)
    state_id int foreign key state (id)
);

这将适用于大多数情况，但对于一些像萨斯喀彻温省的 Lloydminster 这样跨越两个省份边界的特殊情况除外。

- Marc B

---

你好，城市表仍然有state_id。如果城市没有州，会发生什么？我真的很难想象三维表格。它让我的头痛。 - Kevin Lee

如果城市表中的state_id没有值，我该怎么办？ - Kevin Lee

一个没有州的城市？例如新加坡？ - Marc B

是的。如果我不想在state_id中放置值，怎么办？最好的方法是什么？如果允许空值，那么表格将充满空值。 - Kevin Lee

有点晚了，但我自己也在寻找这样的设计。当出现 NULL 记录时，我建议为每个表提供代表 NULL 对象的记录。例如，每个表中的 id 1 都有一个名称为“无”的记录，然后从您的城市表链接到 id 1 的州表。 - Ben Fransen

Answer 3

除了美国以外，许多国家在国家和市级之间存在政治分歧。澳大利亚有州，加拿大有省，日本有县等。

问题是如何跟踪这些信息并保持一致性？您可以在中间层添加“虚拟记录”来处理没有中间层的国家。另一种处理方法是将外键去规范化到包含地址的实体的所有级别。如果国家和城市是必填项，则它们的外键将不可为空，而您的州外键可以为空。

如果您选择去规范化路线，则需要应用程序逻辑来确保您的外键彼此一致。

如果您选择虚拟状态记录路线，则需要应用程序逻辑来确保虚拟层在用户界面中对用户隐藏。

- Joel Brown

---

是的，我的计划是将其规范化。我的第一个目标是获取美国的所有州。然后我将来会添加澳大利亚的州。我需要一个灵活的表设计，可以处理未来的更改。 - Kevin Lee