PostgreSQL用户表与多级地址表关联优化方案咨询

问题背景 #

我有一个包含User、Country、City、Street四张表的PostgreSQL数据库。User表存储用户信息，包含姓名、级别（可选值为"Country"、"City"、"Street"），以及对应用户所属国家、城市、街道的ID。当前设计使用三个独立外键列，查询时需要根据级别分支执行JOIN操作，感觉冗余且不够优雅，希望找到更合理的设计方案，同时保证数据完整性并遵循标准数据库设计原则。

原有表结构如下：

CREATE TABLE "user" (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  level VARCHAR(10) NOT NULL, --ENUM('Country', 'City', 'Street')
  id_country INT NULL,
  id_city INT NULL,
  id_street INT NULL,
  FOREIGN KEY (id_country) REFERENCES Country(id) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (id_city) REFERENCES City(id) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (id_street) REFERENCES Street(id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE Country (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  code INT NOT NULL
);

CREATE TABLE City (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  code INT NOT NULL,
  id_country INT NOT NULL,
  FOREIGN KEY (id_country) REFERENCES Country(id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE Street (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  code INT NOT NULL,
  id_city INT NOT NULL,
  FOREIGN KEY (id_city) REFERENCES City(id) ON UPDATE CASCADE ON DELETE CASCADE
);

优化方案 #

方案1：改进原有设计，添加约束保证数据一致性 #

原有设计的核心问题是允许同时填写多个位置ID，违反了用户级别与对应位置的匹配规则。可以通过检查约束和部分索引修复这个问题，同时保留原有结构的兼容性：

表结构修改

CREATE TABLE "user" (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  level VARCHAR(10) NOT NULL CHECK (level IN ('Country', 'City', 'Street')),
  id_country INT NULL,
  id_city INT NULL,
  id_street INT NULL,
  FOREIGN KEY (id_country) REFERENCES Country(id) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (id_city) REFERENCES City(id) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (id_street) REFERENCES Street(id) ON UPDATE CASCADE ON DELETE CASCADE,
  -- 强制级别与对应ID匹配，其他位置ID必须为NULL
  CHECK (
    (level = 'Country' AND id_country IS NOT NULL AND id_city IS NULL AND id_street IS NULL)
    OR (level = 'City' AND id_city IS NOT NULL AND id_country IS NULL AND id_street IS NULL)
    OR (level = 'Street' AND id_street IS NOT NULL AND id_country IS NULL AND id_city IS NULL)
  )
);

-- 添加部分索引提升查询性能
CREATE INDEX idx_user_country ON "user"(id_country) WHERE level = 'Country';
CREATE INDEX idx_user_city ON "user"(id_city) WHERE level = 'City';
CREATE INDEX idx_user_street ON "user"(id_street) WHERE level = 'Street';

查询简化

可以用一个通用查询替代分支逻辑，通过LEFT JOIN并根据级别筛选有效数据：

SELECT 
  u.id, u.name, u.level,
  -- 根据级别取对应位置信息
  CASE u.level
    WHEN 'Country' THEN c.name
    WHEN 'City' THEN ci.name
    WHEN 'Street' THEN s.name
  END AS location_name,
  CASE u.level
    WHEN 'Country' THEN c.code
    WHEN 'City' THEN ci.code
    WHEN 'Street' THEN s.code
  END AS location_code
FROM "user" u
LEFT JOIN Country c ON u.level = 'Country' AND u.id_country = c.id
LEFT JOIN City ci ON u.level = 'City' AND u.id_city = ci.id
LEFT JOIN Street s ON u.level = 'Street' AND u.id_street = s.id;

方案2：使用PostgreSQL表继承特性 #

PostgreSQL支持表继承，可以把位置信息抽象为父表，让Country、City、Street继承它，然后User只关联父表，实现单一外键关联：

步骤1：创建位置父表

CREATE TABLE Location (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  code INT NOT NULL,
  type VARCHAR(10) NOT NULL CHECK (type IN ('Country', 'City', 'Street'))
);

步骤2：创建继承子表

-- Country表，继承父表字段并约束类型
CREATE TABLE Country (
  CONSTRAINT chk_country_type CHECK (type = 'Country')
) INHERITS (Location);

-- City表，继承父表字段，添加关联Country的外键
CREATE TABLE City (
  id_country INT NOT NULL REFERENCES Country(id) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT chk_city_type CHECK (type = 'City')
) INHERITS (Location);

-- Street表，继承父表字段，添加关联City的外键
CREATE TABLE Street (
  id_city INT NOT NULL REFERENCES City(id) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT chk_street_type CHECK (type = 'Street')
) INHERITS (Location);

步骤3：修改User表

CREATE TABLE "user" (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  location_id INT NOT NULL REFERENCES Location(id) ON UPDATE CASCADE ON DELETE CASCADE
);

查询优势

不需要分支逻辑，直接JOIN父表就能获取所有位置信息：

SELECT u.id, u.name, l.name AS location_name, l.code AS location_code, l.type AS level
FROM "user" u
JOIN Location l ON u.location_id = l.id;

注意：继承表查询默认会包含所有子表数据，插入子表时父表也会同步记录，适合层级结构清晰的场景。

方案3：单一关联列+触发器实现多态关联 #

模拟多态关联模式，通过location_id和location_type组合关联不同表，用触发器保证数据有效性：

表结构设计

CREATE TABLE "user" (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  location_id INT NOT NULL,
  location_type VARCHAR(10) NOT NULL CHECK (location_type IN ('Country', 'City', 'Street'))
);

添加触发器验证关联有效性

创建函数验证location_id在对应表中存在：

CREATE OR REPLACE FUNCTION validate_location()
RETURNS TRIGGER AS $$
BEGIN
  CASE NEW.location_type
    WHEN 'Country' THEN
      IF NOT EXISTS (SELECT 1 FROM Country WHERE id = NEW.location_id) THEN
        RAISE EXCEPTION 'Country ID % does not exist', NEW.location_id;
      END IF;
    WHEN 'City' THEN
      IF NOT EXISTS (SELECT 1 FROM City WHERE id = NEW.location_id) THEN
        RAISE EXCEPTION 'City ID % does not exist', NEW.location_id;
      END IF;
    WHEN 'Street' THEN
      IF NOT EXISTS (SELECT 1 FROM Street WHERE id = NEW.location_id) THEN
        RAISE EXCEPTION 'Street ID % does not exist', NEW.location_id;
      END IF;
  END CASE;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器到User表的INSERT和UPDATE操作
CREATE TRIGGER trigger_validate_location
BEFORE INSERT OR UPDATE ON "user"
FOR EACH ROW EXECUTE FUNCTION validate_location();

查询简化

用一个查询即可关联对应表：

SELECT 
  u.id, u.name, u.location_type AS level,
  COALESCE(c.name, ci.name, s.name) AS location_name,
  COALESCE(c.code, ci.code, s.code) AS location_code
FROM "user" u
LEFT JOIN Country c ON u.location_type = 'Country' AND u.location_id = c.id
LEFT JOIN City ci ON u.location_type = 'City' AND u.location_id = ci.id
LEFT JOIN Street s ON u.location_type = 'Street' AND u.location_id = s.id;

优缺点：表结构简洁，但无法直接用外键约束保证关联有效性，依赖触发器实现，适合追求结构精简的场景。

内容的提问来源于stack exchange，提问作者GisCat