处理无限列表及装配部件多阶段定价的数据库设计方案咨询

核心思路：结构化实体-关系设计 #

放弃EAV灵活但易混乱的特性，采用分层实体+关联表的模式，既能覆盖部件嵌套、多阶段定价的需求，又能保持数据库结构的清晰性和可维护性。

1. 基础实体表 #

部件表 (parts)

存储所有部件（含基础件、组装后的复合件）的核心信息：

CREATE TABLE parts (
    part_id INT PRIMARY KEY AUTO_INCREMENT,
    part_name VARCHAR(100) NOT NULL,
    part_type ENUM('basic', 'assembled') NOT NULL, -- 区分基础部件/组装部件
    description TEXT
);

定价阶段表 (pricing_stages)

预先定义所有可能的定价类型（如设置费、涂装费等），避免EAV的字段随意性：

CREATE TABLE pricing_stages (
    stage_id INT PRIMARY KEY AUTO_INCREMENT,
    stage_name VARCHAR(50) NOT NULL UNIQUE,
    stage_description TEXT
);

2. 关联关系表 #

部件-定价关联表 (part_pricing)

记录单个部件在各阶段的定价，支持同一部件多阶段、多有效期的定价规则：

CREATE TABLE part_pricing (
    part_pricing_id INT PRIMARY KEY AUTO_INCREMENT,
    part_id INT NOT NULL,
    stage_id INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    effective_date DATE, -- 支持定价生效时间
    FOREIGN KEY (part_id) REFERENCES parts(part_id),
    FOREIGN KEY (stage_id) REFERENCES pricing_stages(stage_id),
    UNIQUE KEY (part_id, stage_id, effective_date) -- 避免同阶段重复定价
);

部件组装结构表 (assembly_structure)

用结构化方式记录复合部件的组成关系，替代纯递归查询的模糊性：

CREATE TABLE assembly_structure (
    assembly_id INT PRIMARY KEY AUTO_INCREMENT,
    parent_part_id INT NOT NULL, -- 组装后的复合部件ID
    child_part_id INT NOT NULL, -- 子部件ID
    quantity INT NOT NULL DEFAULT 1, -- 子部件使用数量
    FOREIGN KEY (parent_part_id) REFERENCES parts(part_id),
    FOREIGN KEY (child_part_id) REFERENCES parts(part_id),
    CHECK (parent_part_id != child_part_id) -- 避免自引用循环
);

3. 方案优势 #

• 结构清晰：所有实体和关系预先定义，避免EAV的字段混乱，查询时无需动态拼接字段
• 扩展性强：新增定价阶段只需在pricing_stages添加记录，无需修改表结构；新增复合部件直接在parts和assembly_structure中补充
• 查询高效：统计复合部件总成本时，可通过JOIN组装结构表和定价关联表快速计算，无需复杂的EAV聚合查询
• 数据一致：通过外键约束确保定价阶段、部件关系的合法性，避免EAV中可能出现的无效数据

4. 复合部件总成本计算示例 #

用递归CTE快速计算某复合部件的全阶段总成本（含所有子部件的定价）：

WITH RECURSIVE part_hierarchy AS (
    SELECT child_part_id, quantity
    FROM assembly_structure
    WHERE parent_part_id = [目标复合部件ID]
    UNION ALL
    SELECT a.child_part_id, a.quantity * ph.quantity
    FROM assembly_structure a
    JOIN part_hierarchy ph ON a.parent_part_id = ph.child_part_id
)
SELECT ps.stage_name, SUM(pp.price * ph.quantity) AS total_cost
FROM part_hierarchy ph
JOIN part_pricing pp ON ph.child_part_id = pp.part_id
JOIN pricing_stages ps ON pp.stage_id = ps.stage_id
GROUP BY ps.stage_name;

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