mysql - 国家/地区 州 城市 表 - ID 或名称

Question

嗨，我正在重新考虑 mysql 数据库的设计以提高效率..

目前我有3张 table

表国家:国家 ID、国家名称

表状态:州 ID、州名称、国家 ID

表所在城市:城市 ID、城市名称、州 ID

我在想是否最好......

1. 表状态中的国家/地区名称而不是国家/地区 ID

2. 表城市中的州名称而不是州 ID

这是因为在我的代码中，我必须运行额外的查询来将国家/地区 ID、州 ID 和城市 ID 从数字转换为字母(例如 1 转换为美国)...按字母顺序引用不是更好吗..(更少的查询)

Answer 1

全世界大概有

260 country/regions
5000 states
many many cities

设计根据您的需要而有所不同。

1 - 出于小型存储的目的:

country(id,country)
state(id,state,country_id)
city(id,city,state_id)

2 - 为了快速查询:

city(id,city,state,country)

3 - 出于中间道路的目的:

country(code,country)
state(code,country) -- you might merge country and state into one table based on code
city(state_code,city)

您可能有兴趣查看 iso 代码:

https://en.wikipedia.org/wiki/ISO_3166-1例如美国

https://en.wikipedia.org/wiki/ISO_3166-2例如美国-纽约

因此，iso 国家代码包含 iso 国家/地区代码。

根据您提供的更多信息更新:

如果您正在为美国设计房地产网站。

1 - 您不需要国家/地区表，很可能所有属性都在美国境内

2 - 美国境内只有不到 60 个州，因此您可以使用枚举来保存州。由于几乎所有人都会理解 NY = 纽约，因此您不需要状态表。

3 - 所以你需要一个城市表。因为您将使用 city_id 来记录超过 10,000,000 条特性记录。

usa_cities(
  id int PK
  state enum('NY', 'LA', ...)
  city varchar
  ...
)

properties(
  id int PK
  city_id int,
  ....
)

AS属性表通常很大，您可以跳过状态表，并对较少连接的查询进行反规范化设计以加快查询速度:

properties (
  id int PK,
  state enum('NY', 'LA',...)
  city varchar
  ...
)

您也许也可以使用城市枚举，我不确定美国有多少个城市，但我一开始并不鼓励这样做。