多对多关系数据库表设计最佳实践及人员多归属场景咨询

你提到的两个方案都不是多对多关系的正确实现方式，各自存在明显问题：

方案一：新增重复person记录的问题 #

在person表插入除id和company_id外完全相同的Blake记录，会导致严重的数据冗余。比如后续要修改Blake的姓名或职位时，必须同时更新所有相关的重复记录，一旦遗漏就会出现数据不一致的情况，长期维护成本极高，完全不符合数据库设计的范式要求。

方案二：company_id存储列表的问题 #

在company_id列存逗号分隔的ID列表（比如1,2），会带来一系列查询和维护问题：

• 无法直接用WHERE company_id = 1这类高效查询，只能用LIKE或FIND_IN_SET这类低性能的方式，数据量一大查询速度会急剧下降
• 无法建立外键约束，无法保证关联的location_id是有效的（比如随便写个不存在的ID也能存进去）
• 统计、排序等操作都会变得异常复杂，完全不适合业务扩展

正确的最佳实践：使用中间关联表 #

person和location（你的location表实际存储的是公司信息，命名可根据业务调整）是典型的多对多关系，正确的做法是创建一个中间关联表（也叫连接表），用来存储两者的关联关系，具体步骤如下：

1. 调整person表（可选但推荐）

去掉person表中的company_id字段，因为人员和地点的关联关系不再存储在person表中：

CREATE TABLE person(
    id int AUTO_INCREMENT,
    name varchar(255),
    role varchar(255),
    PRIMARY KEY(id)
);

2. 创建中间关联表

创建person_location表，通过联合主键避免重复关联，同时设置外键约束保证数据完整性：

CREATE TABLE person_location(
    person_id int,
    location_id int,
    -- 联合主键，确保同一个人不会重复关联同一个地点
    PRIMARY KEY(person_id, location_id),
    -- 外键关联person表，删除人员时自动删除关联记录（可选，根据业务需求调整）
    FOREIGN KEY(person_id) REFERENCES person(id) ON DELETE CASCADE,
    -- 外键关联location表，删除地点时自动删除关联记录（可选）
    FOREIGN KEY(location_id) REFERENCES location(id) ON DELETE CASCADE
);

3. 插入关联数据

比如要让Blake（id=2）同时属于Company A（location_id=1）和Company B（location_id=2），只需插入两条关联记录：

INSERT INTO person_location(person_id, location_id) VALUES (2, 1), (2, 2);

其他人员的关联数据按实际需求插入即可。

4. 查询示例

要查询属于Company A的所有人员：

SELECT p.* 
FROM person p
JOIN person_location pl ON p.id = pl.person_id
WHERE pl.location_id = 1;

要查询属于Company B的所有人员：

SELECT p.* 
FROM person p
JOIN person_location pl ON p.id = pl.person_id
WHERE pl.location_id = 2;

这两个查询都会返回Blake的信息，完全满足你的需求。

这种方案的优势 #

• 无数据冗余：人员信息只存储一次，修改时只需更新一条记录，避免数据不一致
• 数据完整性强：外键约束确保关联的人员和地点都是真实存在的，不会出现无效数据
• 查询高效灵活：可以利用索引快速查询，支持各种复杂的统计、筛选需求，扩展性强
• 符合数据库设计范式：结构清晰，便于后续维护和迭代

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