多对多三元关系下三表与Pivot表的增删改查及表单操作方案

一、数据库表结构设计 #

先明确四张表的关联逻辑，确保数据一致性：

1. 业务基础表 #

• 销售人员表 (salespeople)：存储销售核心信息

  CREATE TABLE salespeople (
      sales_id INT PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(50) NOT NULL,
      phone VARCHAR(20)
  );
  

• 客户表 (customers)：存储客户基本信息

  CREATE TABLE customers (
      customer_id INT PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(50) NOT NULL,
      email VARCHAR(100) UNIQUE
  );
  

• 航空公司表 (airlines)：存储航司基础数据

  CREATE TABLE airlines (
      airline_id INT PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(50) NOT NULL,
      code VARCHAR(10) UNIQUE
  );
  

2. 三元关联Pivot表（销售记录表） #

作为核心关联表，同时存储销售业务数据：

CREATE TABLE sales_records (
    record_id INT PRIMARY KEY AUTO_INCREMENT,
    sales_id INT NOT NULL,
    customer_id INT NOT NULL,
    airline_id INT NOT NULL,
    ticket_count INT NOT NULL CHECK (ticket_count > 0),
    sale_date DATE NOT NULL DEFAULT CURRENT_DATE,
    -- 外键约束，确保关联记录合法
    FOREIGN KEY (sales_id) REFERENCES salespeople(sales_id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE,
    FOREIGN KEY (airline_id) REFERENCES airlines(airline_id) ON DELETE CASCADE,
    -- 联合唯一约束，避免同一销售-客户-航司的重复记录（按需启用）
    UNIQUE KEY unique_sale (sales_id, customer_id, airline_id)
);

二、核心CRUD操作实现 #

1. 插入操作 #

场景1：基于已有业务数据插入

若销售人员、客户、航司均已存在，直接插入关联记录：

-- 示例：销售1给客户2售出航司3的5张机票
INSERT INTO sales_records (sales_id, customer_id, airline_id, ticket_count)
VALUES (1, 2, 3, 5);

场景2：表单录入含新业务数据（如新增客户）

后台先校验业务数据是否存在，不存在则先插入业务表，再关联销售记录：

# 伪代码示例（Python+MySQL）
def create_sale(sales_name, customer_email, airline_code, ticket_count):
    # 1. 确保销售人员存在，不存在则创建
    sales_id = get_or_create_salesperson(sales_name)
    # 2. 确保客户存在，不存在则创建
    customer_id = get_or_create_customer(customer_email)
    # 3. 确保航空公司存在，不存在则创建
    airline_id = get_or_create_airline(airline_code)
    # 4. 插入/更新销售记录（重复则累加票数）
    insert_sql = """
        INSERT INTO sales_records (sales_id, customer_id, airline_id, ticket_count)
        VALUES (%s, %s, %s, %s)
        ON DUPLICATE KEY UPDATE ticket_count = ticket_count + VALUES(ticket_count)
    """
    execute_sql(insert_sql, (sales_id, customer_id, airline_id, ticket_count))

2. 查询操作 #

场景1：查询某销售人员的所有销售记录

SELECT 
    sp.name AS sales_name,
    c.name AS customer_name,
    a.name AS airline_name,
    sr.ticket_count,
    sr.sale_date
FROM sales_records sr
JOIN salespeople sp ON sr.sales_id = sp.sales_id
JOIN customers c ON sr.customer_id = c.customer_id
JOIN airlines a ON sr.airline_id = a.airline_id
WHERE sp.sales_id = 1;

场景2：查询某客户的全渠道购买记录

SELECT 
    sp.name AS sales_name,
    a.name AS airline_name,
    SUM(sr.ticket_count) AS total_tickets
FROM sales_records sr
JOIN salespeople sp ON sr.sales_id = sp.sales_id
JOIN airlines a ON sr.airline_id = a.airline_id
WHERE sr.customer_id = 2
GROUP BY sp.sales_id, a.airline_id;

场景3：多条件组合查询（如某航司月度销售）

SELECT 
    sp.name AS sales_name,
    c.name AS customer_name,
    sr.ticket_count
FROM sales_records sr
JOIN salespeople sp ON sr.sales_id = sp.sales_id
JOIN customers c ON sr.customer_id = c.customer_id
WHERE sr.airline_id = 3 AND sr.sale_date BETWEEN '2024-01-01' AND '2024-06-01';

3. 更新操作 #

场景1：修改单条销售记录的票数

UPDATE sales_records
SET ticket_count = 6
WHERE record_id = 1;

场景2：批量更新某三元组合的累计销量

-- 销售1给客户2的航司3订单，新增3张机票
UPDATE sales_records
SET ticket_count = ticket_count + 3
WHERE sales_id = 1 AND customer_id = 2 AND airline_id = 3;

4. 删除操作 #

场景1：删除单条销售记录

DELETE FROM sales_records WHERE record_id = 1;

场景2：删除某客户的所有购买记录

DELETE FROM sales_records WHERE customer_id = 2;

业务表删除注意事项

若删除销售人员/客户/航司，因外键设置了ON DELETE CASCADE，关联销售记录会自动删除；若需保留销售记录，可将外键约束改为ON DELETE SET NULL（需对应字段允许为空），或先手动删除关联记录再删业务表。

三、前端表单与后台自动维护方案 #

1. 单个表单设计 #

表单核心字段：

• 销售人员：下拉选择框（支持加载现有列表、模糊搜索或新增）
• 客户：下拉选择框（支持通过邮箱/姓名搜索或新增）
• 航空公司：下拉选择框（支持通过代码/名称搜索或新增）
• 机票数量：数字输入框（限制最小值为1）
• 销售日期：日期选择器（默认当前日期）

2. 后台自动维护逻辑 #

表单提交后，后台按以下步骤处理：

1. 字段合法性校验：检查票数是否为正整数、日期是否合法等。
2. 业务数据校验与创建：对三个业务实体分别查询数据库，不存在则自动插入对应业务表并获取ID。
3. 销售记录处理：根据三元ID判断是否已有关联记录，无则插入，有则更新票数（累加或覆盖，依业务需求）。
4. 结果返回：向前端反馈操作成功状态或错误信息。

四、关键注意事项 #

• 外键约束需匹配业务场景：级联删除适合“删除业务数据时同步清理关联销售”的需求，否则用置空或限制删除。
• 联合唯一约束可避免重复的三元关联记录，若业务允许同一组合多次下单，可移除该约束，用record_id区分订单。
• 批量操作需用事务保证数据一致性，确保业务表插入与销售记录操作要么全成功，要么全回滚。

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