用户兴趣SQL Schema设计咨询：含预设与自定义兴趣的场景优化

针对你提到的场景，下面给出几种能从数据库层面约束用户只能选择自己创建的自定义兴趣的方案，避免仅依赖后端校验的潜在风险：

方案一：合并预设与自定义兴趣到单表 #

将预设兴趣和自定义兴趣统一存储在一张表中，通过字段区分类型，并利用约束保证数据合法性：

-- 统一的兴趣表
CREATE TABLE interests (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    type ENUM('preset', 'custom') NOT NULL, -- 区分预设/自定义兴趣
    user_id INT NULL, -- 自定义兴趣的创建者ID，预设兴趣为NULL
    FOREIGN KEY (user_id) REFERENCES users(id),
    -- 预设兴趣必须user_id为NULL，自定义兴趣必须user_id不为NULL
    CHECK (
        (type = 'preset' AND user_id IS NULL)
        OR (type = 'custom' AND user_id IS NOT NULL)
    ),
    -- 预设兴趣名称全局唯一，自定义兴趣按用户维度唯一
    UNIQUE KEY (name, user_id)
);

-- 用户关联兴趣表
CREATE TABLE user_interests (
    user_id INT NOT NULL,
    interest_id INT NOT NULL,
    PRIMARY KEY (user_id, interest_id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (interest_id) REFERENCES interests(id),
    -- 约束：自定义兴趣必须属于当前关联的用户
    CHECK (
        NOT EXISTS (
            SELECT 1 FROM interests i
            WHERE i.id = interest_id
            AND i.type = 'custom'
            AND i.user_id != user_id
        )
    )
);

该方案结构简洁，通过CHECK约束直接在数据库层面限制非法关联。注意部分数据库（如MySQL 8.0.16之前版本）对CHECK约束支持有限，可改用触发器替代对应逻辑。

方案二：拆分用户关联表 #

保留原有的预设兴趣表和自定义兴趣表，但将用户关联表拆分为两张，通过复合外键实现强约束：

-- 预设兴趣表
CREATE TABLE interests (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL UNIQUE
);

-- 自定义兴趣表，采用(id, user_id)作为复合主键
CREATE TABLE custom_interests (
    id INT AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    user_id INT NOT NULL,
    PRIMARY KEY (id, user_id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    UNIQUE KEY (user_id, name) -- 同一用户的自定义兴趣名称唯一
);

-- 用户关联预设兴趣表
CREATE TABLE user_preset_interests (
    user_id INT NOT NULL,
    interest_id INT NOT NULL,
    PRIMARY KEY (user_id, interest_id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (interest_id) REFERENCES interests(id)
);

-- 用户关联自定义兴趣表，复合外键确保只能关联自己的自定义兴趣
CREATE TABLE user_custom_interests (
    user_id INT NOT NULL,
    custom_interest_id INT NOT NULL,
    PRIMARY KEY (user_id, custom_interest_id),
    -- 复合外键关联custom_interests的(id, user_id)
    FOREIGN KEY (custom_interest_id, user_id) REFERENCES custom_interests(id, user_id)
);

这种方案结构清晰，通过复合外键直接约束关联逻辑，无需额外CHECK约束或触发器，兼容性覆盖大部分主流数据库。

方案三：增强原表的约束逻辑 #

如果想保留你最初的三表结构，可以通过添加触发器实现约束：

-- 保留原有的三张表结构，修改user_interests表
CREATE TABLE user_interests (
    id INT PRIMARY KEY AUTO_INCREMENT,
    interest_id INT NULL,
    custom_interest_id INT NULL,
    user_id INT NOT NULL,
    FOREIGN KEY (interest_id) REFERENCES interests(id),
    FOREIGN KEY (custom_interest_id) REFERENCES custom_interests(id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    -- 约束两个ID不能同时为空或同时不为空
    CHECK (
        (interest_id IS NULL AND custom_interest_id IS NOT NULL)
        OR (interest_id IS NOT NULL AND custom_interest_id IS NULL)
    )
);

-- 插入时校验自定义兴趣归属
DELIMITER //
CREATE TRIGGER validate_custom_interest_owner_insert
BEFORE INSERT ON user_interests
FOR EACH ROW
BEGIN
    IF NEW.custom_interest_id IS NOT NULL THEN
        IF NOT EXISTS (
            SELECT 1 FROM custom_interests ci
            WHERE ci.id = NEW.custom_interest_id
            AND ci.user_id = NEW.user_id
        ) THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '自定义兴趣不属于当前用户';
        END IF;
    END IF;
END //
DELIMITER ;

-- 更新时校验自定义兴趣归属
DELIMITER //
CREATE TRIGGER validate_custom_interest_owner_update
BEFORE UPDATE ON user_interests
FOR EACH ROW
BEGIN
    IF NEW.custom_interest_id IS NOT NULL THEN
        IF NOT EXISTS (
            SELECT 1 FROM custom_interests ci
            WHERE ci.id = NEW.custom_interest_id
            AND ci.user_id = NEW.user_id
        ) THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '自定义兴趣不属于当前用户';
        END IF;
    END IF;
END //
DELIMITER ;

该方案保留原有结构，但需依赖触发器维护约束，相比前两种方案，长期维护成本稍高。

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