数据库设计：五张核心表的ER图与SQL建表语句

好，咱们直接进入正题。客户生命周期管理系统，说白了就是管好「谁买了什么、谁在跟进、进展如何」。数据库设计是地基，地基不稳，后面全是坑。我见过太多项目上线后改表结构改到崩溃的，所以这一章咱们把五张核心表——客户表、联系人表、商机表、活动表、订单表——彻底讲透。

一、ER图设计思路：先理关系，再写SQL

我个人习惯，画ER图之前先想清楚业务场景。你想想看：

• 客户是主体，一个客户可以有多个联系人（比如采购经理、技术负责人）。
• 一个客户可以有多个商机（比如同时谈两个项目）。
• 一个商机可以关联多个活动（比如拜访、演示、报价）。
• 商机最终成交，变成订单。

所以核心关系是：

• 客户 1 : N 联系人
• 客户 1 : N 商机
• 商机 1 : N 活动
• 商机 1 : 1 订单（一个商机成交后只对应一个订单）

嗯，这里要注意：订单和商机是一对一，但订单和客户是一对多。一个客户可以有多个订单，每个订单只属于一个客户。

二、客户表（customer）

客户表是核心中的核心。我在项目中遇到过一个问题：客户名称重复录入，导致数据混乱。所以一定要加唯一索引。

CREATE TABLE customer (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '客户ID',
    customer_name VARCHAR(100) NOT NULL COMMENT '客户名称',
    industry VARCHAR(50) COMMENT '所属行业',
    source VARCHAR(30) COMMENT '客户来源（如：线上、转介绍、展会）',
    status TINYINT DEFAULT 1 COMMENT '状态：1-潜在，2-跟进中，3-已成交，4-流失',
    phone VARCHAR(20) COMMENT '联系电话',
    email VARCHAR(100) COMMENT '邮箱',
    address VARCHAR(255) COMMENT '地址',
    created_by BIGINT COMMENT '创建人ID',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    UNIQUE KEY uk_customer_name (customer_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户表';

三、联系人表（contact）

联系人是客户的「触手」。一个客户可能有多个联系人，但每个联系人只属于一个客户。说白了就是多对一。

CREATE TABLE contact (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '联系人ID',
    customer_id BIGINT NOT NULL COMMENT '所属客户ID',
    contact_name VARCHAR(50) NOT NULL COMMENT '联系人姓名',
    position VARCHAR(50) COMMENT '职位',
    phone VARCHAR(20) COMMENT '手机号',
    email VARCHAR(100) COMMENT '邮箱',
    is_primary TINYINT DEFAULT 0 COMMENT '是否主要联系人：0-否，1-是',
    remark VARCHAR(255) COMMENT '备注',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_customer_id (customer_id),
    CONSTRAINT fk_contact_customer FOREIGN KEY (customer_id) REFERENCES customer(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='联系人表';

为什么加外键？因为删除客户时，联系人应该一起删掉。我见过有人不用外键，结果客户删了，联系人变成「孤儿数据」。嗯，这里用ON DELETE CASCADE最省心。

四、商机表（opportunity）

商机就是「正在谈的生意」。它关联客户，也关联后续的活动和订单。状态字段很关键，我一般用枚举值：1-初步接触，2-需求分析，3-方案报价，4-谈判中，5-赢单，6-输单。

CREATE TABLE opportunity (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '商机ID',
    customer_id BIGINT NOT NULL COMMENT '客户ID',
    opportunity_name VARCHAR(100) NOT NULL COMMENT '商机名称',
    expected_amount DECIMAL(15,2) DEFAULT 0.00 COMMENT '预计金额',
    stage TINYINT DEFAULT 1 COMMENT '阶段：1-初步接触，2-需求分析，3-方案报价，4-谈判中，5-赢单，6-输单',
    probability TINYINT DEFAULT 10 COMMENT '赢单概率（百分比）',
    expected_close_date DATE COMMENT '预计成交日期',
    owner_id BIGINT COMMENT '负责人ID',
    remark TEXT COMMENT '备注',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_customer_id (customer_id),
    INDEX idx_owner_id (owner_id),
    INDEX idx_stage (stage),
    CONSTRAINT fk_opp_customer FOREIGN KEY (customer_id) REFERENCES customer(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商机表';

五、活动表（activity）

活动记录的是「跟单过程」。比如打电话、发邮件、上门拜访。每个活动可以关联一个商机，也可以不关联（比如只是日常维护）。

CREATE TABLE activity (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '活动ID',
    opportunity_id BIGINT COMMENT '关联商机ID（可为空）',
    activity_type VARCHAR(30) NOT NULL COMMENT '活动类型：call-电话，email-邮件，meeting-会议，visit-拜访',
    subject VARCHAR(200) NOT NULL COMMENT '活动主题',
    content TEXT COMMENT '活动内容',
    activity_date DATETIME NOT NULL COMMENT '活动时间',
    owner_id BIGINT COMMENT '执行人ID',
    status TINYINT DEFAULT 0 COMMENT '状态：0-计划，1-已完成，2-取消',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_opportunity_id (opportunity_id),
    INDEX idx_owner_id (owner_id),
    INDEX idx_activity_date (activity_date),
    CONSTRAINT fk_act_opportunity FOREIGN KEY (opportunity_id) REFERENCES opportunity(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='活动表';

注意这里外键用了ON DELETE SET NULL。为什么？因为商机删除了，活动记录可能还有审计价值，不能直接删掉。我曾经踩过这个坑，删商机时把活动也级联删了，客户投诉说历史记录全没了。

六、订单表（orders）

订单是最终成果。一个商机成交后，生成一个订单。订单金额、付款状态、交付状态，这些是核心字段。

CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '订单ID',
    order_no VARCHAR(30) NOT NULL COMMENT '订单编号',
    customer_id BIGINT NOT NULL COMMENT '客户ID',
    opportunity_id BIGINT COMMENT '关联商机ID',
    total_amount DECIMAL(15,2) NOT NULL COMMENT '订单总金额',
    paid_amount DECIMAL(15,2) DEFAULT 0.00 COMMENT '已付款金额',
    payment_status TINYINT DEFAULT 0 COMMENT '付款状态：0-未付款，1-部分付款，2-已付清',
    delivery_status TINYINT DEFAULT 0 COMMENT '交付状态：0-未交付，1-部分交付，2-已交付',
    order_date DATE NOT NULL COMMENT '下单日期',
    remark VARCHAR(255) COMMENT '备注',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_order_no (order_no),
    INDEX idx_customer_id (customer_id),
    INDEX idx_opportunity_id (opportunity_id),
    CONSTRAINT fk_order_customer FOREIGN KEY (customer_id) REFERENCES customer(id) ON DELETE CASCADE,
    CONSTRAINT fk_order_opportunity FOREIGN KEY (opportunity_id) REFERENCES opportunity(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

七、五张表的关系总结

表名	主键	外键关联	核心字段
customer	id	无	客户名称、行业、状态
contact	id	customer_id	姓名、电话、是否主要联系人
opportunity	id	customer_id	商机名称、阶段、预计金额
activity	id	opportunity_id	活动类型、主题、时间
orders	id	customer_id, opportunity_id	订单编号、总金额、付款状态

说白了，这五张表就是一条线：客户 -> 商机 -> 活动 -> 订单。联系人依附于客户，活动依附于商机。你想想看，只要这条线理清了，后面写业务逻辑就顺了。

好，这一章就到这里。下一章咱们聊索引优化和查询性能，到时候我会分享一个真实案例：一张表500万数据，查询从30秒优化到0.1秒。