如何在不违反规范化的前提下，在同表展示产品与序列号？

核心思路 #

通过可选字段+数据约束的方式，在单表中兼容两种产品类型的交易记录，既符合数据库规范化要求（无冗余、数据一致），又能直接展示产品与序列号信息。

数据库表结构设计 #

1. 产品主表（补充序列化属性）

先在产品主表中标记产品是否需要序列化，避免交易表冗余存储产品类型：

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    is_serialized BOOLEAN NOT NULL DEFAULT FALSE, -- TRUE=需序列化，FALSE=无需序列化
    -- 其他产品属性字段（如规格、分类等）
);

2. 库存交易表（单表兼容两种类型）

交易表中保留serial_number作为可选字段，通过约束强制数据一致性：

CREATE TABLE inventory_transactions (
    transaction_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    serial_number VARCHAR(100) NULL, -- 序列化产品必填，非序列化产品为NULL
    transaction_type VARCHAR(20) NOT NULL, -- 如：入库/出库/调拨/盘点
    quantity INT NOT NULL, -- 非序列化产品记录批量数量；序列化产品固定为1（单个序列号对应一笔交易）
    transaction_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    -- 核心约束：保证序列化规则的一致性
    CONSTRAINT chk_serial_consistency CHECK (
        EXISTS (
            SELECT 1 FROM products p 
            WHERE p.product_id = inventory_transactions.product_id 
            AND (
                (p.is_serialized = TRUE AND inventory_transactions.serial_number IS NOT NULL AND inventory_transactions.quantity = 1)
                OR (p.is_serialized = FALSE AND inventory_transactions.serial_number IS NULL)
            )
        )
    )
);

注：若使用MySQL 8.0.16之前的版本，CHECK约束不生效，可改用触发器实现相同的规则校验。

同表展示查询方案 #

直接通过SQL查询即可在单结果集中展示所有交易的产品与序列号信息，无需关联子表：

SELECT 
    it.transaction_id,
    p.product_name,
    CASE WHEN p.is_serialized THEN it.serial_number ELSE '无序列号' END AS serial_number,
    it.transaction_type,
    it.quantity,
    DATE_FORMAT(it.transaction_date, '%Y-%m-%d %H:%i:%s') AS transaction_date
FROM inventory_transactions it
JOIN products p ON it.product_id = p.product_id
ORDER BY it.transaction_date DESC;

设计优势 #

• 符合规范化：无冗余数据，serial_number仅在需要时存储，约束保证数据逻辑一致；
• 查询高效：无需关联子序列号表，单表查询即可获取全部信息；
• 扩展性强：若后续新增产品类型，只需调整products表的is_serialized属性，交易表无需修改。

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