MySQL数据库架构设计：如何规避many-to-many关系关联产品与库存组件

核心表结构设计 #

1. 原始库存表（raw_inventory） #

存储所有原始库存组件的基础信息，作为组件的唯一数据源：

CREATE TABLE raw_inventory (
    inv_id VARCHAR(50) PRIMARY KEY, -- 示例值：inv_1、inv_2
    inv_name VARCHAR(100) NOT NULL,
    current_stock INT DEFAULT 0,
    unit_cost DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. 集合产品表（collections） #

存储集合产品的基础信息，每个集合是独立的销售单元：

CREATE TABLE collections (
    coll_id VARCHAR(50) PRIMARY KEY, -- 示例值：coll_a、coll_b
    coll_name VARCHAR(100) NOT NULL,
    sale_price DECIMAL(10,2) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

3. 集合-组件关联表（collection_component_map） #

通过拆分为两个一对多关系替代直接多对多：每个集合对应多条关联记录（每个组件一条），每个组件也可对应多条关联记录（属于多个集合），同时支持扩展组件数量等信息：

CREATE TABLE collection_component_map (
    map_id INT AUTO_INCREMENT PRIMARY KEY, -- 自增主键，唯一标识每条关联
    coll_id VARCHAR(50) NOT NULL,
    inv_id VARCHAR(50) NOT NULL,
    component_quantity INT DEFAULT 1, -- 每个集合包含该组件的数量
    FOREIGN KEY (coll_id) REFERENCES collections(coll_id) ON DELETE CASCADE,
    FOREIGN KEY (inv_id) REFERENCES raw_inventory(inv_id) ON DELETE CASCADE,
    UNIQUE KEY unique_coll_inv (coll_id, inv_id) -- 避免同一集合重复关联同一组件
);

数据插入示例 #

按照你的需求插入初始数据：

-- 插入原始库存组件
INSERT INTO raw_inventory (inv_id, inv_name, current_stock, unit_cost)
VALUES 
('inv_1', '组件1', 100, 10.00),
('inv_2', '组件2', 80, 15.00),
('inv_3', '组件3', 50, 20.00);

-- 插入集合产品
INSERT INTO collections (coll_id, coll_name, sale_price)
VALUES 
('coll_a', '两件套组合', 40.00),
('coll_b', '三件套组合', 60.00);

-- 插入集合与组件的关联关系
INSERT INTO collection_component_map (coll_id, inv_id, component_quantity)
VALUES 
('coll_a', 'inv_1', 1),
('coll_a', 'inv_2', 1),
('coll_b', 'inv_1', 1),
('coll_b', 'inv_2', 1),
('coll_b', 'inv_3', 1);

需求实现：追踪组件需求（销量） #

若需统计每个组件的总需求，可结合销售记录表计算。先创建销售记录表：

CREATE TABLE sales (
    sale_id INT AUTO_INCREMENT PRIMARY KEY,
    coll_id VARCHAR(50) NOT NULL,
    sale_quantity INT NOT NULL,
    sale_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (coll_id) REFERENCES collections(coll_id)
);

统计每个组件的总需求（销量对应的组件用量）：

SELECT 
    ri.inv_id,
    ri.inv_name,
    SUM(ccm.component_quantity * s.sale_quantity) AS total_demand
FROM sales s
JOIN collection_component_map ccm ON s.coll_id = ccm.coll_id
JOIN raw_inventory ri ON ccm.inv_id = ri.inv_id
GROUP BY ri.inv_id, ri.inv_name;

方案优势说明 #

直接的多对多关系扩展性差（无法记录组件数量等额外信息），此方案通过中间表拆解为两个一对多关系：

• collections 到 collection_component_map 是一对多（一个集合对应多个组件关联记录）
• raw_inventory 到 collection_component_map 是一对多（一个组件对应多个集合关联记录）

既满足关联需求，又保留扩展性（后续可在中间表添加装配顺序、组件备注等字段），同时避免了数据冗余与不一致问题。

关于单表方案的问题 #

你之前考虑的单表方案（集合与组件同存一表）会导致大量数据冗余：比如inv_1会在coll_a和coll_b的记录中重复出现，组件信息更新时需修改多条记录，极易出现数据不一致，不建议使用。

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