浏览器游戏中地图与村庄的关联数据建模问题咨询

我正在开发一款浏览器游戏，目前在地图位置和村庄的数据模型设计上碰到了困惑，想和大家聊聊我的思路以及当前遇到的问题，希望能得到一些建议。

初始设计的表结构 #

最开始我设计了两张表：

• 地图表map，存储所有地图位置的坐标：

CREATE TABLE map (
    id serial NOT NULL,
    x_pos integer NOT NULL,
    y_pos integer NOT NULL,
    PRIMARY KEY (id),
    UNIQUE (x_pos, y_pos)
);

• 村庄表villages，存储村庄信息，每个村庄必须对应一个地图位置，但初始设计里没关联位置：

CREATE TABLE villages (
    id serial NOT NULL,
    name VARCHAR(256) NOT NULL,
    population INT4 NOT NULL,
    owner_id INT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (owner_id) REFERENCES users(id)
);

核心需求 #

我需要实现两个关键功能：

• 判断某个地图位置是否空闲
• 如果位置已被占用，能快速找到对应的村庄

最初的尝试：关联表方案 #

一开始我想过创建第三张关联表map_to_villages来关联位置和村庄，但很快意识到这是多对多关系的典型设计，而我的场景里一个位置最多只能有一个村庄，一个村庄也只会对应一个位置，用关联表有点多余。而且这种方案下，查询空闲位置需要对比整个关联表，效率也不高。

关联表的设计如下：

CREATE TABLE map_to_villages (
    id serial NOT NULL,
    location_id INT NOT NULL,
    village_id INT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (location_id) REFERENCES map(id),
    FOREIGN KEY (village_id) REFERENCES villages(id)
);

采纳建议后的改进方案 #

后来听了Asa的建议，我调整了表结构：

1. map表新增is_available字段，标记位置是否空闲：

CREATE TABLE map (
    id serial NOT NULL,
    x_pos integer NOT NULL,
    y_pos integer NOT NULL,
    is_available BOOL NOT NULL, -- TRUE表示位置可用
    PRIMARY KEY (id),
    UNIQUE (x_pos, y_pos)
);

2. villages表新增location_id字段，通过外键关联到map的主键，明确村庄所在的位置：

CREATE TABLE villages (
    id serial NOT NULL,
    name VARCHAR(256) NOT NULL,
    population INT4 NOT NULL,
    owner_id INT NOT NULL,
    location_id INT NOT NULL, -- 村庄所在的位置ID
    PRIMARY KEY (id),
    FOREIGN KEY (owner_id) REFERENCES users(id),
    FOREIGN KEY (location_id) REFERENCES map(id) -- 新增外键关联
);

当前遇到的问题 #

调整后的设计更贴合我的需求，但我发现一个漏洞：villages表目前没有限制location_id的唯一性，这意味着可能会出现多个村庄关联到同一个地图位置的情况，而实际游戏里一个位置只能存在一个村庄。

想请教大家，针对这个问题，有没有什么优雅的解决办法？比如能不能通过数据库约束来避免这种情况，或者在业务逻辑层面需要做哪些处理？

备注：内容来源于stack exchange，提问作者Pierre-Alexandre