php - 应该在 SQL 中规范化邮寄地址吗？

Question

包含城市、州和邮政编码的邮寄地址是否应该规范化？我目前只关心美国地址。我在这篇文章的底部展示了一个标准化表格和一个 ERD，以及一个非标准化表格。请理性回答。

请注意 To Normalize or Not To Normalize与本主题相关，但又不同。

谢谢

CREATE  TABLE IF NOT EXISTS states (
  id CHAR(2) NOT NULL ,
  name VARCHAR(45) NULL DEFAULT NULL ,
  PRIMARY KEY (id) ,
  INDEX states_name (name ASC) )
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS cities (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  name VARCHAR(45) NOT NULL ,
  states_id CHAR(2) NOT NULL ,
  PRIMARY KEY (id) ,
  INDEX fk_zipcodes_states1_idx (states_id ASC) ,
  UNIQUE INDEX makeUnique (states_id ASC, name ASC) ,
  INDEX cities_name (name ASC) ,
  CONSTRAINT fk_zipcodes_states1
    FOREIGN KEY (states_id )
    REFERENCES states (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;

CREATE  TABLE IF NOT EXISTS zipcode_types (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  name VARCHAR(45) NULL DEFAULT NULL ,
  PRIMARY KEY (id) )
ENGINE = InnoDB
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;

CREATE  TABLE IF NOT EXISTS counties (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  name VARCHAR(45) NOT NULL ,
  PRIMARY KEY (id) ,
  INDEX counties_name (name ASC) )
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS timezones (
  id CHAR(4) NOT NULL ,
  name VARCHAR(45) NOT NULL ,
  PRIMARY KEY (id) )
ENGINE = InnoDB
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;

CREATE  TABLE IF NOT EXISTS zipcodes (
  id CHAR(5) NOT NULL ,
  longitude DECIMAL(9,6) NOT NULL ,
  latitude DECIMAL(9,6) NOT NULL ,
  zipcode_types_id INT UNSIGNED NOT NULL ,
  counties_id INT UNSIGNED NOT NULL ,
  timezones_id CHAR(4) NOT NULL ,
  PRIMARY KEY (id) ,
  INDEX fk_zipcodes_zipcode_types1_idx (zipcode_types_id ASC) ,
  INDEX fk_zipcodes_counties1_idx (counties_id ASC) ,
  INDEX fk_zipcodes_timezones1_idx (timezones_id ASC) ,
  CONSTRAINT fk_zipcodes_zipcode_types1
    FOREIGN KEY (zipcode_types_id )
    REFERENCES zipcode_types (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_zipcodes_counties1
    FOREIGN KEY (counties_id )
    REFERENCES counties (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_zipcodes_timezones1
    FOREIGN KEY (timezones_id )
    REFERENCES timezones (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS cities_has_zipcodes (
  cities_id INT UNSIGNED NOT NULL ,
  zipcodes_id CHAR(5) NOT NULL ,
  PRIMARY KEY (cities_id, zipcodes_id) ,
  INDEX fk_cities_has_zipcodes_zipcodes1_idx (zipcodes_id ASC) ,
  INDEX fk_cities_has_zipcodes_cities1_idx (cities_id ASC) ,
  CONSTRAINT fk_cities_has_zipcodes_cities1
    FOREIGN KEY (cities_id )
    REFERENCES cities (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_cities_has_zipcodes_zipcodes1
    FOREIGN KEY (zipcodes_id )
    REFERENCES zipcodes (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS someRecord (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  data VARCHAR(45) NULL ,
  address VARCHAR(45) NULL ,
  cities_id INT UNSIGNED NOT NULL ,
  zipcodes_id CHAR(5) NOT NULL ,
  PRIMARY KEY (id) ,
  INDEX fk_someRecord_cities1_idx (cities_id ASC) ,
  INDEX fk_someRecord_zipcodes1_idx (zipcodes_id ASC) ,
  CONSTRAINT fk_someRecord_cities1
    FOREIGN KEY (cities_id )
    REFERENCES cities (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_someRecord_zipcodes1
    FOREIGN KEY (zipcodes_id )
    REFERENCES zipcodes (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

单表数据示例

CREATE  TABLE IF NOT EXISTS otherRecord (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  data VARCHAR(45) NULL ,
  address VARCHAR(45) NULL ,
  city VARCHAR(45) NULL ,
  state VARCHAR(45) NULL ,
  zipcode VARCHAR(45) NULL ,
  county VARCHAR(45) NULL ,
  longitude DECIMAL(9,6) NULL ,
  latitude DECIMAL(9,6) NULL ,
  timezone VARCHAR(45) NULL ,
  PRIMARY KEY (id) )
ENGINE = InnoDB;

Answer 1

是的，如果:

1. 您将根据地址分析您的数据，我的意思是根据地址的各个字段进行排序、过滤、分组和计数。

   如果您允许自由文本，那么您可能会使用国家/地区名称，例如 US、USA、U.S.A、United States。如果您想查看/统计/分组所有美国客户，这将是一件痛苦的事情。您的内部用户可能希望从大陆向下钻取到国家、州、县和城市，在这种情况下，您的数据需要规范化。

2. 您将针对外部来源进行匹配。例如，您有来自第三方供应商的数据，您需要匹配他们的公司 A 和您的公司 A。通常公司名称相似，您需要匹配(部分)地址。例如，您需要将“Acme, Inc | California”与“Acme Incorporated | CA”进行匹配。

3. 您想真正避免重复。如果您允许自由文本，那么您将获得“123-456 Main Street, Vancouver”和“Apt 123, 456 Main Street, Vancouver”的副本

4. 您需要真正有效的数据。如果您允许自由文本，那么任何人都可以输入任何内容。这很难，因为您需要大量引用数据，其中包含可用的国家/地区名称、州名称、县名称，甚至街道名称。您可以先从 geonames.org 获取一些数据。

请注意，爱尔兰不使用邮政编码，因此如果要走向全局，您的架构需要考虑到这一点。阅读 Hay 的企业模型模式以获得一些好的地址模型。