SQL表设计咨询：如何让一列值取自两表之一及关联方案选择

首先明确：不建议在后端处理并存储冗余数据，因为冗余会带来数据一致性问题——当company1或company2的质量值更新时，production表的corrected_quality_value如果没有同步更新，就会出现数据不一致，后续维护成本极高。更合理的方式是在数据库设计层面通过关联逻辑实现需求，以下是两种可行方案：

方案1：使用视图（优先推荐） #

如果不需要持久化corrected_quality_value，直接创建视图动态计算结果，完全避免冗余，且数据始终与源表一致：

CREATE VIEW production_view AS
SELECT 
    p.id,
    p.prod_id,
    COALESCE(c2.company2_quality_value, c1.company1_quality_value) AS corrected_quality_value
FROM production p
LEFT JOIN company2 c2 ON p.prod_id = c2.prod_id
LEFT JOIN company1 c1 ON p.prod_id = c1.prod_id;

查询时直接使用这个视图即可，每次查询都会实时计算出最新的修正后质量值。

方案2：持久化数据（用触发器/计算列） #

如果业务必须将corrected_quality_value存储在production物理表中，可通过触发器或数据库原生的计算列来自动维护值的一致性：

方式A：触发器（以MySQL为例） #

创建触发器，在production表插入/更新时自动计算值，同时监听company1、company2的更新来同步production表：

-- 插入production时自动计算
DELIMITER //
CREATE TRIGGER trg_production_insert
BEFORE INSERT ON production
FOR EACH ROW
BEGIN
    SELECT COALESCE(c2.company2_quality_value, c1.company1_quality_value)
    INTO NEW.corrected_quality_value
    FROM company1 c1
    LEFT JOIN company2 c2 ON c1.prod_id = c2.prod_id
    WHERE c1.prod_id = NEW.prod_id;
END //
DELIMITER ;

-- 更新production时重新计算
DELIMITER //
CREATE TRIGGER trg_production_update
BEFORE UPDATE ON production
FOR EACH ROW
BEGIN
    SELECT COALESCE(c2.company2_quality_value, c1.company1_quality_value)
    INTO NEW.corrected_quality_value
    FROM company1 c1
    LEFT JOIN company2 c2 ON c1.prod_id = c2.prod_id
    WHERE c1.prod_id = NEW.prod_id;
END //
DELIMITER ;

-- company2更新时同步关联的production记录
DELIMITER //
CREATE TRIGGER trg_company2_update
AFTER UPDATE ON company2
FOR EACH ROW
BEGIN
    UPDATE production p
    JOIN company1 c1 ON p.prod_id = c1.prod_id
    SET p.corrected_quality_value = COALESCE(NEW.company2_quality_value, c1.company1_quality_value)
    WHERE p.prod_id = NEW.prod_id;
END //
DELIMITER ;

方式B：计算列（部分数据库支持） #

比如PostgreSQL的生成列、MySQL的生成列，直接在表定义中嵌入计算逻辑：

-- PostgreSQL示例
CREATE TABLE production(
    id int NOT NULL,
    prod_id int NOT NULL,
    corrected_quality_value int GENERATED ALWAYS AS (
        COALESCE(
            (SELECT company2_quality_value FROM company2 WHERE prod_id = production.prod_id),
            (SELECT company1_quality_value FROM company1 WHERE prod_id = production.prod_id)
        )
    ) STORED
);

-- MySQL示例（子查询需用函数封装）
DELIMITER //
CREATE FUNCTION get_corrected_quality(p_prod_id INT) RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE val2 INT;
    SELECT company2_quality_value INTO val2 FROM company2 WHERE prod_id = p_prod_id;
    RETURN IF(val2 IS NOT NULL, val2, (SELECT company1_quality_value FROM company1 WHERE prod_id = p_prod_id));
END //
DELIMITER ;

CREATE TABLE production(
    id int NOT NULL,
    prod_id int NOT NULL,
    corrected_quality_value INT AS (get_corrected_quality(prod_id)) STORED
);

总结 #

• 优先使用视图，无冗余、无一致性问题，实现成本最低；
• 若必须持久化数据，优先选择数据库原生的计算列（如果支持），其次用触发器自动维护，避免手动更新带来的错误。

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