第3章数据库设计与建模

第三章：数据库设计与建模

好，咱们进入第三章。这一章，说白了就是给CRM系统打地基。你想想看，房子盖得再漂亮，地基不稳也是白搭。数据库设计就是这样，前期多花点心思，后面能省下大把改代码的时间。

我个人习惯，在写第一行代码之前，先把ER图画出来。为什么？因为表结构一旦定下来，后面改起来特别痛苦。我在项目中遇到过好几次，因为前期没想清楚，后期不得不做数据迁移，那叫一个折腾。

3.1 ER图设计：先画图，再写代码

ER图，就是实体关系图。它帮我们把业务概念翻译成数据库语言。咱们这个CRM系统，核心实体就这几个：用户、客户、商机、合同、跟进记录。

它们之间的关系，我简单梳理一下：

一个用户，可以拥有多个客户（一对多）
一个客户，可以产生多个商机（一对多）
一个商机，最终可能签成一个合同（一对一或一对多）
一个客户或商机，会有多次跟进记录（一对多）

核心关系总结：

用户 → 客户 → 商机 → 合同，这是一条主线。跟进记录则像一条辅线，挂在客户和商机下面。

嗯，这里要注意。商机和合同的关系，很多人搞成一对一。但实际业务中，一个商机可能拆成多个合同，或者多个商机合并成一个合同。我建议保留一对多的灵活性。

3.2 用户表：系统的入口

用户表，就是谁在用这个系统。别小看它，很多坑都出在这里。

CREATE TABLE `sys_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `password` varchar(255) NOT NULL COMMENT '密码（加密后）',
  `real_name` varchar(50) DEFAULT NULL COMMENT '真实姓名',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `phone` varchar(20) DEFAULT NULL COMMENT '手机号',
  `avatar` varchar(255) DEFAULT NULL COMMENT '头像URL',
  `status` tinyint(4) DEFAULT '1' COMMENT '状态：1启用 0禁用',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

这里我特别想强调两点：

密码一定要加密存储。我曾经见过直接把明文密码放数据库的项目，那简直是灾难。用bcrypt或者PBKDF2，别偷懒。
用户名加唯一索引。这是基本操作，但有人会忘。没有唯一索引，注册时并发请求可能插入重复用户名。

我的小习惯：每个表都加上create_time和update_time，并且让update_time自动更新。这样排查问题的时候，你能知道数据是什么时候变的。

3.3 客户表：CRM的心脏

客户表，是整个系统最重要的表。所有业务都围绕客户展开。

CREATE TABLE `customer` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `owner_id` bigint(20) NOT NULL COMMENT '所属用户ID',
  `name` varchar(100) NOT NULL COMMENT '客户名称',
  `industry` varchar(50) DEFAULT NULL COMMENT '所属行业',
  `source` varchar(50) DEFAULT NULL COMMENT '客户来源',
  `phone` varchar(20) DEFAULT NULL COMMENT '联系电话',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `address` varchar(255) DEFAULT NULL COMMENT '地址',
  `website` varchar(255) DEFAULT NULL COMMENT '网站',
  `level` tinyint(4) DEFAULT '1' COMMENT '客户等级：1普通 2重要 3VIP',
  `status` tinyint(4) DEFAULT '1' COMMENT '状态：1潜在 2意向 3成交 4流失',
  `remark` text COMMENT '备注',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_owner_id` (`owner_id`),
  KEY `idx_name` (`name`),
  CONSTRAINT `fk_customer_user` FOREIGN KEY (`owner_id`) REFERENCES `sys_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户表';

你看，owner_id这个字段，就是关联到用户表的。它表示这个客户归谁管。实际开发中，经常要查「某个销售手底下有哪些客户」，所以这个字段一定要加索引。

客户等级和状态，我建议用数字枚举。为什么不用字符串？你想想看，如果用「普通」「重要」「VIP」这种字符串，万一哪天想改成「初级」「中级」「高级」，你得改所有数据。用数字，改一下枚举映射就行了。

我曾经踩过的坑：客户表没有加唯一约束，结果同一个客户被不同销售重复录入。后来我们加了手机号或者公司名的唯一索引，才解决这个问题。但要注意，不是所有客户都有手机号，所以唯一索引要设计成可空的。

3.4 商机表：从线索到成交

商机，就是潜在的销售机会。它从客户来，最终走向合同。

CREATE TABLE `opportunity` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `customer_id` bigint(20) NOT NULL COMMENT '关联客户ID',
  `owner_id` bigint(20) NOT NULL COMMENT '负责人ID',
  `title` varchar(200) NOT NULL COMMENT '商机标题',
  `amount` decimal(15,2) DEFAULT '0.00' COMMENT '预计金额',
  `stage` tinyint(4) DEFAULT '1' COMMENT '销售阶段：1初步接触 2需求分析 3方案报价 4谈判 5成交',
  `probability` tinyint(4) DEFAULT '10' COMMENT '赢单概率（%）',
  `expected_close_date` date DEFAULT NULL COMMENT '预计关闭日期',
  `description` text COMMENT '描述',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_owner_id` (`owner_id`),
  CONSTRAINT `fk_opp_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`),
  CONSTRAINT `fk_opp_user` FOREIGN KEY (`owner_id`) REFERENCES `sys_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商机表';

stage字段，我用了数字表示销售阶段。实际项目中，这个阶段可能会变。比如你们公司有6个阶段，或者8个阶段。用数字，改配置就行，不用改表结构。

probability是赢单概率。这个字段挺有意思，它和stage是联动的。比如「初步接触」阶段，概率可能只有10%；到了「谈判」阶段，概率可能升到80%。我建议在代码里做这个映射，而不是让用户随便填。

一个实用的设计：商机表里加一个「预计金额」字段。这样销售可以预估这个季度的业绩。很多CRM系统会漏掉这个字段，导致做报表的时候抓瞎。

3.5 合同表：最终交付物

合同，是商机转化的结果。它记录了最终的成交信息。

CREATE TABLE `contract` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `opportunity_id` bigint(20) DEFAULT NULL COMMENT '关联商机ID',
  `customer_id` bigint(20) NOT NULL COMMENT '客户ID',
  `contract_no` varchar(50) NOT NULL COMMENT '合同编号',
  `title` varchar(200) NOT NULL COMMENT '合同名称',
  `amount` decimal(15,2) NOT NULL COMMENT '合同金额',
  `sign_date` date DEFAULT NULL COMMENT '签订日期',
  `start_date` date DEFAULT NULL COMMENT '开始日期',
  `end_date` date DEFAULT NULL COMMENT '结束日期',
  `status` tinyint(4) DEFAULT '1' COMMENT '状态：1待审核 2已生效 3执行中 4已完成 5已终止',
  `attachment_url` varchar(500) DEFAULT NULL COMMENT '附件URL',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_contract_no` (`contract_no`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_opportunity_id` (`opportunity_id`),
  CONSTRAINT `fk_contract_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`),
  CONSTRAINT `fk_contract_opp` FOREIGN KEY (`opportunity_id`) REFERENCES `opportunity` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='合同表';

合同编号，我建议用业务规则生成。比如「HT-2024-0001」这种格式。它必须是唯一的，所以加了唯一索引。

注意，opportunity_id是可空的。为什么？因为有些合同可能不是从商机转化来的，比如老客户直接续约。这种情况，商机ID就是空的。

我的建议：合同金额用decimal(15,2)，别用float或double。浮点数有精度问题，算钱的时候会出大错。我见过因为用float导致对不上账的案例，那叫一个惨。

3.6 跟进记录表：业务的痕迹

跟进记录，就是销售和客户之间的每一次互动。打电话、发邮件、见面，都算。

CREATE TABLE `follow_up` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `customer_id` bigint(20) DEFAULT NULL COMMENT '关联客户ID',
  `opportunity_id` bigint(20) DEFAULT NULL COMMENT '关联商机ID',
  `owner_id` bigint(20) NOT NULL COMMENT '跟进人ID',
  `type` tinyint(4) DEFAULT '1' COMMENT '跟进类型：1电话 2邮件 3拜访 4其他',
  `content` text NOT NULL COMMENT '跟进内容',
  `next_plan` varchar(500) DEFAULT NULL COMMENT '下一步计划',
  `follow_up_time` datetime DEFAULT NULL COMMENT '跟进时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_opportunity_id` (`opportunity_id`),
  KEY `idx_owner_id` (`owner_id`),
  CONSTRAINT `fk_follow_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`),
  CONSTRAINT `fk_follow_opp` FOREIGN KEY (`opportunity_id`) REFERENCES `opportunity` (`id`),
  CONSTRAINT `fk_follow_user` FOREIGN KEY (`owner_id`) REFERENCES `sys_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='跟进记录表';

这个表的设计，我特别想说说。customer_id和opportunity_id都是可空的。为什么？因为跟进记录可以挂在客户上，也可以挂在商机上。比如你给客户打了个电话，聊的是某个商机，那这条记录就关联到商机。如果只是日常问候，就关联到客户。

content字段是text类型，因为跟进内容可能很长。别用varchar(255)，那肯定不够用。

我曾经犯过的错：跟进记录表没有加follow_up_time字段，直接用create_time代替。结果销售说「我昨天跟进的，今天才录入系统」，时间就对不上了。所以，业务时间和系统创建时间，一定要分开。

3.7 表关系总结

好了，五个核心表都讲完了。我画个简单的对应关系：

表名	核心字段	关联关系
sys_user	username, password, status	被customer、opportunity、follow_up引用
customer	name, owner_id, level, status	关联user，被opportunity、contract、follow_up引用
opportunity	customer_id, owner_id, stage, amount	关联customer和user，被contract和follow_up引用
contract	customer_id, opportunity_id, amount, status	关联customer和opportunity
follow_up	customer_id, opportunity_id, content, type	关联customer、opportunity和user

嗯，这一章内容不少。数据库设计就是这样，想得越细，后面写代码越顺。下一章，咱们就开始搭建项目框架，把Spring Boot跑起来。

记住一句话：表结构设计得好，CRUD写得少。别急着写代码，先把表设计好。

📚 课程目录

第三章：数据库设计与建模

3.1 ER图设计：先画图，再写代码

3.2 用户表：系统的入口

3.3 客户表：CRM的心脏

3.4 商机表：从线索到成交

3.5 合同表：最终交付物

3.6 跟进记录表：业务的痕迹

3.7 表关系总结