PostgreSQL游戏数据库冗余传递关系消除及模式规范化问询

问题背景 #

正在开发一款融合《幽浮：未知敌人》与《模拟人生》的游戏，采用PostgreSQL存储游戏状态，当前数据库结构存在以下问题：

• 冗余关联：士兵的所属基地同时存在直接关联（trooper.base_id）和间接关联（通过指派的设施/载具的base_id）两条路径
• 数据不一致风险：数据库层面允许士兵被指派到其他基地的设施/载具，只能依赖业务逻辑限制，无法从结构上避免

需求：通过结构修改而非复杂约束消除冗余与不一致，实现通过trooper.id唯一确定其关联的base.id

现有原表结构 #

CREATE TABLE base (
  id int PRIMARY KEY
);

CREATE TABLE facility (
  id int PRIMARY KEY,
  base_id int REFERENCES base
);

CREATE TABLE craft (
  id int PRIMARY KEY,
  base_id int REFERENCES base
);

CREATE TABLE trooper (
  id int PRIMARY KEY,
  assigned_facility_id int REFERENCES facility,
  assigned_craft_id int REFERENCES craft,
  base_id int REFERENCES base
);

规范化方案 #

核心思路 #

移除trooper表中直接存储的base_id，让士兵的所属基地完全通过其指派的设施/载具间接推导，同时通过给每个基地创建默认"待命"设施的方式，解决士兵未指派状态的关联问题，从结构上消除冗余与不一致可能。

优化后的表结构 #

1. 基础表调整（增加必要约束与字段）

CREATE TABLE base (
  id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY -- 改用自增主键，更符合游戏开发习惯
);

CREATE TABLE facility (
  id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  base_id int NOT NULL REFERENCES base(id), -- 强制设施必须属于某个基地
  type varchar(50) NOT NULL -- 标记设施类型，用于区分默认待命兵营与其他功能设施
);

CREATE TABLE craft (
  id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  base_id int NOT NULL REFERENCES base(id) -- 强制载具必须属于某个基地
);

2. 重构士兵表（移除冗余字段）

CREATE TABLE trooper (
  id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  -- 士兵只能关联到一个设施或一个载具，二者互斥
  assigned_facility_id int REFERENCES facility(id),
  assigned_craft_id int REFERENCES craft(id),
  -- 可选：简单检查约束，确保两个指派字段不同时非空（数据库层面强制互斥，不属于复杂约束）
  CHECK (
    (assigned_facility_id IS NOT NULL AND assigned_craft_id IS NULL) OR
    (assigned_facility_id IS NULL AND assigned_craft_id IS NOT NULL)
  )
);

配套业务规则 #

1. 默认设施初始化：每个基地创建时，自动生成一个type = 'barracks'的待命兵营设施，作为未指派士兵的关联对象
2. 士兵状态管理：
   • 未执行任务的士兵，关联到所属基地的待命兵营
   • 被指派到设施执行防御任务的士兵，关联到对应功能设施
   • 被指派到载具执行进攻任务的士兵，关联到对应载具
3. 基地归属推导：士兵的所属基地通过以下方式获取：
   • 若assigned_facility_id非空：查询facility.base_id
   • 若assigned_craft_id非空：查询craft.base_id

方案优势 #

• 消除冗余：没有多条基地关联路径，士兵的基地归属唯一由指派对象决定
• 避免不一致：设施与载具本身严格绑定到特定基地，士兵无法关联到其他基地的对象（除非业务逻辑错误，但数据库外键已确保关联对象存在）
• 逻辑清晰：士兵的状态完全通过指派关系体现，符合游戏内的角色逻辑

内容的提问来源于stack exchange，提问作者Anthony L. Gershman