如何规范化关联父表的表关系，避免跨企业书籍作者关联？

你的核心问题是当前设计无法保证关联的书籍和作者属于同一企业，这属于数据完整性约束范畴，以下是几种可行方案，按推荐程度排序：

1. 给中间表添加company_id并设置复合外键（最推荐） #

修改books_authors表结构，新增company_id字段，然后创建两个复合外键：

CREATE TABLE books_authors (
    book_id INT NOT NULL,
    author_id INT NOT NULL,
    company_id INT NOT NULL,
    PRIMARY KEY (book_id, author_id),
    FOREIGN KEY (book_id, company_id) REFERENCES books(id, company_id),
    FOREIGN KEY (author_id, company_id) REFERENCES authors(id, company_id)
);

这个设计的逻辑是：

• 插入关联记录时必须指定company_id，且该ID必须同时匹配书籍和作者所属的企业
• 数据库会自动验证外键约束，从根源上杜绝跨企业关联的可能
• 性能最优，外键是数据库原生支持的约束，无需额外查询或触发器

2. 使用检查约束（依赖数据库支持） #

如果不想修改表结构，可以尝试添加检查约束，直接验证书籍和作者的company_id是否一致：

-- 以PostgreSQL为例
ALTER TABLE books_authors
ADD CONSTRAINT same_company_check
CHECK (
    (SELECT company_id FROM books WHERE id = book_id) = 
    (SELECT company_id FROM authors WHERE id = author_id)
);

注意：

• MySQL 5.7及更早版本不支持生效的检查约束（语法允许但不执行），MySQL 8.0+才支持
• 这种方式每次插入/更新都要执行两次子查询，性能比复合外键差
• 部分数据库可能不允许在检查约束中使用子查询

3. 用触发器实现验证（兼容性最强） #

如果你的数据库不支持复合外键或检查约束，可以写触发器手动验证：

-- 以MySQL为例，插入前验证
DELIMITER //
CREATE TRIGGER check_same_company_before_insert
BEFORE INSERT ON books_authors
FOR EACH ROW
BEGIN
    DECLARE book_company INT;
    DECLARE author_company INT;
    SELECT company_id INTO book_company FROM books WHERE id = NEW.book_id;
    SELECT company_id INTO author_company FROM authors WHERE id = NEW.author_id;
    IF book_company != author_company THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Book and author must belong to the same company';
    END IF;
END //
DELIMITER ;

-- 同样需要编写更新触发器

这种方式兼容性最好，但存在缺点：

• 触发器属于程序性逻辑，不如外键直观，维护成本更高
• 性能不如复合外键，每次操作都要执行查询和判断

补充说明 #

你的原始设计符合数据库规范化要求，但规范化主要解决数据冗余和更新异常问题，而你需要的是业务规则约束，因此需要额外添加上述机制来保证数据合理性。

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