为全球城市工作的城市设计数据库模式 [英] Designing a database schema for cities that works for cities throughout the world

问题描述

在美国，每个可交付的邮件地址（无论如何我知道）都有邮政编码。对于适用于美国的模式，我已经提出了以下表格：

国家

country_id（PK）

iso3166_1_alpha2

iso3166_1_alpha3

名称（NN，UQ）

metro

metro_id（PK）

名称（NN，UQ）

city

city_id（PK）

country_id（FK，NN）

metro_id（FK）

name（NN ）

lng

lat

邮政

postal_id（ PK）

country_id（FK，NN）

名称（NN，UQ）

lng

lat

city_postal

city_id（FK）

postal_id（FK）

这里的逻辑如下：

1. 每个城市都属于一个国家（例如波特兰 - >美国）。


2. 一些国家有地区（例如，阿拉斯加州，美国），但不是
   。


3. 区域不重要，因为邮政编码是唯一的
   标识符（例如，只有一个波特兰由97267邮政
   代码标识）。


4. 没有邮政编码的国家（例如爱尔兰）有独特的
   城市名称（我的假设：爱尔兰不存在两个戈尔韦尔省）。

我想确定的是，这种结构适用于全世界的城市。我的假设是错的，唯一重要的是城市和国家吗？我应该使用地区吗？例如，是否有地区不使用邮政编码，并且有重复的城市名称，因为城市在不同的地区？

编辑 / p>

如果重要，我使用PostgreSQL 9.3与PostGIS扩展。此外，城市表上有UNIQUE约束，country_id和name的名称（同样适用于邮政表）。所以在美国只有一个波特兰;但是，这很好，因为美国我将使用邮政编码。

解决方案

世界各地的邮寄地址是FUBAR。

爱尔兰不使用邮政编码（都柏林以外）。

英国是一个国家包含其他国家（和省）。

一些城市包含邮政编码，一些邮政编码包含城市。

某些拉链横跨国界。

各国邮政编码并不是独一无二的（50170出现在10个国家/地区）。

有时一个邮寄地址不在一个城市或城市（只是一个被调查的区域）

我将阅读Hay的企业模式模式第12章，我的地址。

您可以尝试以下方式：

  MAILING_ADDRESS 
 id 
 postal_code null FK POSTAL_CODES 
 surveyed_area FK GEO_AREA（必须是城市或受访区域）
 suite null 
 
 GEO_AREA 
 id 
名称
 
国家/地区：GEO_AREA 
 
 PRIMARY_DIVISION（STATE / PROV / UK COUNTRY）：GEO AREA 
 country_id FK GEO_AREA（必须是国家/地区）
 
 CITY：GEO_AREA 
 primary_division_id FK GEO_AREA（必须是主要部门）
 
 SURVEYED_AREA：GEO_AREA 
 parent_id FK GEO_AREA（必须是主要部门或城市） 
  

In the United States every deliverable mail address (that I know of anyway) has a postal code. For a schema that works for the United States, I have come up with the following tables:

country

country_id (PK)
iso3166_1_alpha2
iso3166_1_alpha3
name (NN,UQ)

metro

metro_id (PK)
name (NN,UQ)

city

city_id (PK)
country_id (FK,NN)
metro_id (FK)
name (NN)
lng
lat

postal

postal_id (PK)
country_id (FK, NN)
name (NN, UQ)
lng
lat

city_postal

city_id (FK)
postal_id (FK)

The logic here is as follows:

1. Every city belongs to a country (e.g., Portland -> United States).
2. Some countries have regions (e.g., Alaska, United States), but not all do.
3. Regions do not matter because postal codes are unique identifiers (e.g., Only one Portland is identified by 97267 postal code).
4. Countries without postal codes (e.g., Ireland), have unique city names (my assumption: there does not exist two Galways in Ireland).

What I want to be sure of is that this structure works for cities throughout the world. Is my assumption wrong that the only thing that matters are cities and countries? Should I be using regions? For example, are there areas that do not use postal codes, and have duplicate city names because the cities are in different regions?

EDIT

Just in case it matters, I am using PostgreSQL 9.3 with PostGIS extension. Also, there is a UNIQUE constraint on city table, columns country_id and name (same goes for postal table). So, there is only one Portland in the United States; however, that is fine because for the United States I will be using postal codes.

解决方案

Mailing addresses throughout the world are FUBAR.

Ireland doesn't use postal codes (outside of Dublin).

The United Kingdom is a country that contains other countries (and a province).

Some cities contain postal codes, some postal codes contain cities.

Some zips cross state boundaries.

Postal codes are not unique across countries ("50170" appears in 10 countries).

Sometimes a mailing address is not in a town or city (just a "surveyed area")

I would read Hay's Enterprise Model Patterns chapter 12 on int'l addresses.

You could try something like this:

MAILING_ADDRESS
id
postal_code null FK POSTAL_CODES
surveyed_area FK GEO_AREA (must be a city or surveyed area)
suite null

GEO_AREA
id
name

COUNTRY : GEO_AREA

PRIMARY_DIVISION (STATE/PROV/UK COUNTRY) : GEO AREA
country_id FK GEO_AREA (must be a country)

CITY : GEO_AREA
primary_division_id FK GEO_AREA (must be a primary division)

SURVEYED_AREA : GEO_AREA
parent_id FK GEO_AREA (must be a primary division or city)

这篇关于为全球城市工作的城市设计数据库模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！