国家，城市，国家，邮编表设计 [英] Country, city, state, Post code table design

问题描述

我有一个数据库，其中有一个表中有大量记录的国家，城市，州，邮政编码。我想规范化数据库结构，以避免冗余和删除任何数据重复。

I have a database in which there is Country, city , states , postcodes in one table with huge set of records. I want to normalize the database structure to avoid redundancy and remove any data duplication. Should I split database and make separate tables for Country, City, state and postcode?

或者在地址表中合并国家/地区，城市，州/省，邮政编码？

Or merge country , city , state, postcode in Address table?

推荐答案

以下表格可用于北美的地址。

The following tables could be used for addresses in North America. Counties (which would also contain Parishes and other similar geographical subdivisions) is US-centric.

在地址表中我允许 city_id 和 county_id 为 NULL 根据 HLGEM 的意见，您可以在城市或县，但不能在弗吉尼亚州。应用程序代码需要强制执行以下规则：这些字段中至少有一个不是 NULL 。

In the addresses table I allow the city_id and county_id to be NULL because, based on HLGEM's comment, you can be in a city OR a county but not both in the state of Virginia. The application code would need to enforce the rule that at least one of these fields is not NULL.

addresses
    id              unsigned int(P)
    street          varchar(50)
    extended        varchar(50) // Default NULL
    city_id         unsigned int(F cities.id) Default NULL
    county_id       unsigned int(F counties.id) Default NULL
    zip             varchar(6) // Will handle all north American zips
    zip4            char(4) // Default NULL
    lat             decimal(10,8) // Allows for ~1mm accuracy at equator. Default NULL
    lon             decimal(11,8) // Allows for ~1mm accuracy at equator. Default NULL

cities
    id                  unsigned int(P)
    state_id            unsigned int(F states.id)
    name                varchar(45)
    fips                unsigned int // Default NULL
    census_code         unsigned int // Default NULL
    census_class_code   char(2) // Default NULL
    gsa_code            unsigned int // Default NULL
    opm_code            unsigned int // Default NULL

city_id 和 county_id 形成主键，并且是其各自表格的外键。

city_id and county_id form the Primary key and are foreign keys to their respective tables.

cities_counties
    city_id             unsigned int(F cities.id) ---\_(P)
    county_id           unsigned int(F counties.id)--/

counties
    id                  unsigned int(P)
    state_id            unsigned int(F states.id)
    name                varchar(50)
    fips                unsigned int // Default NULL

请参阅 http：//en.wikipedia .org / wiki / ISO_3166-1

countries
    id                  char(2)(P)
    iso3                char(3)(U)
    iso_num             char(3)(U)
    name                varchar(44)(U)

请参阅 FIPS州代码， FIPS区域代码和 ISO 3166-2 。

states
    id                  unsigned int(P)
    country_id          char(2)(F countries.id)
    code                varchar(3)(I)
    name                varchar(45)
    fips                unsigned int // Default NULL

这篇关于国家，城市，国家，邮编表设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！