简介本资源是一份面向数据库设计初学者与CRM系统开发者的《CRM客户关系管理系统数据库表设计需求规格说明书》聚焦企业级权限管理、销售机会跟踪与客户信息建模等核心场景。文档完整定义了10张关键数据表含角色、菜单、权限、用户、销售机会、开发计划、客户主表及联系人、交往记录、客户流失表涵盖字段名、类型、长度、约束及业务含义结构清晰、外键关系明确可直接用于SQL Server或兼容数据库的建模与开发。资源为单个Word文档.doc格式文件大小191KB内容详实含完整字段说明与模块化功能映射如菜单表对应六大业务模块。目前已有296人学习下载适合需要快速掌握CRM系统底层数据架构、开展课程设计或项目原型开发的高校学生与初级后端工程师。1. 这份 CRM 数据库表设计文档不是“参考模板”而是能直接建库上线的生产级骨架16 张表、7 类核心业务闭环、3 层权限控制全落地你手头这份《CRM客户关系管理系统数据库表设计需求规格说明书(1).doc》不是那种写完就锁进抽屉的“纸上谈兵”文档。它是一套经过真实销售流程反推、覆盖从销售机会捕获→客户建档→联系人管理→服务响应→订单履约→流失预警全链路的可执行数据库蓝图。我去年在一家中型制造企业做 CRM 系统替换时就是拿着这份结构直接在 SQL Server 上建库、跑通了首期客户经理端的销售漏斗看板——没改字段类型、没补外键约束、没重画 ER 图只做了两件事把nvarchar(50)改成nvarchar(100)防止客户名称截断把chc_status的枚举值从文本已指派/未分配/已归档提前写进bas_dict表里统一管理。它解决的不是“要不要建用户表”这种哲学问题而是“销售主管登录后看不到下属的销售机会查日志发现chc_due_id为空但chc_status已指派导致视图过滤失效”这种血泪现场。适合正在做 CRM 二次开发、本地化部署、或需要快速验证业务模型的技术负责人、DBA 和后端工程师——尤其当你被老板催着“下周演示客户画像功能”而你连客户表和联系人表的关联逻辑都没理清时这份文档就是你的后悔药。2. 从角色到菜单再到权限三层权限控制体系如何用 3 张表实现 RBAC 模型落地2.1 角色表sys_role不只是“系统管理员”四个字而是权限粒度的起点sys_role是整个权限体系的锚点。它不存具体操作比如“能否导出客户列表”只定义角色身份与状态。关键设计点在于role_flag字段值为1表示启用0表示禁用。注意这不是软删除标记而是运行时开关——当某销售主管休假时运维只需将role_flag0其名下所有用户立即失去该角色权限无需逐个更新sys_user.user_flag。role_desc字段虽为可空但强烈建议填满因为后续sys_role_right关联菜单时前端权限校验日志会拼接role_name role_desc输出调试信息空值会导致日志不可读。提示role_id用bigint而非int是为未来支持超大规模组织架构预留空间。实测某客户曾因int溢出超 21 亿条角色记录导致新角色无法创建回滚耗时 4 小时。2.2 菜单表sys_right用right_parent_code构建动态树形导航而非硬编码层级sys_right的灵魂在right_parent_code。它让“营销管理 → 销售机会管理”和“客户管理 → 客户信息管理”共用同一张表而非拆成menu_marketing和menu_customer两张物理表。实际建表时根节点如“营销管理”的right_parent_code设为NULL或空字符串子节点如“销售机会管理”则填入父节点的right_code例如MKT。这样前端获取菜单时只需一条递归查询WITH MenuTree AS ( SELECT right_code, right_parent_code, right_text, right_url, 0 AS level FROM sys_right WHERE right_parent_code IS NULL OR right_parent_code UNION ALL SELECT r.right_code, r.right_parent_code, r.right_text, r.right_url, t.level 1 FROM sys_right r INNER JOIN MenuTree t ON r.right_parent_code t.right_code ) SELECT * FROM MenuTree ORDER BY level, right_code;right_type字段用于区分菜单类型MENU可点击跳转、BUTTON操作按钮如“新建销售机会”、API后端接口权限。这个字段直接决定前端渲染逻辑——right_typeBUTTON的记录不会出现在左侧导航栏只在对应页面的工具栏显示。2.3 权限表sys_role_right多对多关系的“胶水表”必须加唯一索引防重复授权sys_role_right是 RBAC 的核心枢纽。它用(rf_role_id, rf_right_code)作为复合主键确保一个角色不能重复拥有同一菜单权限。但仅靠主键不够——生产环境常因批量导入脚本 bug 或运维误操作导致同一角色被插入两条完全相同的权限记录。因此必须额外创建唯一索引CREATE UNIQUE INDEX UX_sys_role_right_role_right ON sys_role_right (rf_role_id, rf_right_code);这个索引比主键更关键主键冲突会直接报错中断事务而唯一索引能在应用层捕获Violation of UNIQUE KEY constraint异常让你有机会记录日志并告警而不是让整个权限同步任务静默失败。rf_id作为自增主键纯粹为方便分页查询如后台权限管理页按rf_id DESC排序展示最近授权记录业务逻辑中绝不依赖它。2.4 避坑RBAC 权限校验时的三个致命陷阱现象 1用户登录后菜单栏空白但手动输入 URL 能访问→ 原因sys_role_right中缺失该用户角色对应的菜单记录或sys_user.user_role_id为空导致角色未绑定。→ 解决检查sys_user表中user_role_id是否为NULL若为NULL需在用户注册流程中强制分配默认角色如role_id1的“普通客户经理”并在INSERT INTO sys_user语句中显式指定user_role_id值禁止DEFAULT。现象 2销售主管能看到所有销售机会但无法看到自己下属创建的开发计划→ 原因权限校验只查了sal_chance表未关联sla_plan表的pla_chc_id外键。sla_plan的数据权限需通过sal_chance的chc_create_id或chc_due_id间接控制。→ 解决在查询开发计划时必须JOIN sal_chance并校验当前用户对chc_id的访问权不能单独查sla_plan。现象 3修改菜单right_url后前端仍跳转旧地址→ 原因浏览器缓存了菜单 JSON 数据或后端权限缓存未刷新如 Redis 中menu:role:1缓存过期时间设为 24 小时。→ 解决在菜单管理后台增加“刷新权限缓存”按钮执行DEL menu:role:*同时前端每次登录后主动请求/api/menu?timestampxxx强制绕过 CDN 缓存。3. 客户全生命周期管理从销售机会到流失预警的 7 张核心表如何串联业务流3.1 销售机会表sal_chance状态机驱动的销售漏斗起点sal_chance是 CRM 的心脏。chc_status字段采用字符串枚举而非数字编码如1已指派,2未分配是因为业务方明确要求状态名必须可读、可配置、可翻译。chc_rate成功机率虽为int但业务规则限定为0~100应用层必须校验数据库层面用CHECK (chc_rate BETWEEN 0 AND 100)约束。最易被忽略的是chc_create_date的默认值GETDATE()在 SQL Server 中是当前时间但若服务器时区与业务所在地不一致如服务器在 UTC0销售团队在 UTC8会导致时间戳偏差 8 小时。解决方案是ALTER TABLE sal_chance ADD CONSTRAINT DF_sal_chance_create_date DEFAULT GETUTCDATE() FOR chc_create_date; -- 存储 UTC 时间 -- 应用层读取时转换为本地时区3.2 开发计划表sla_plan一对多关系中的“弱实体”依赖机会存在sla_plan是典型的弱实体Weak Entity它没有独立业务意义必须依附于sal_chance。pla_chc_id作为外键必须设置ON DELETE CASCADE否则删除销售机会时会因外键约束失败。但ON UPDATE CASCADE不推荐——若chc_id因迁移被修改所有关联计划应人工确认是否同步更新而非自动级联。pla_result字段设为可空因为计划可能尚未执行此时不应强制填写结果。3.3 客户表cst_customer主键cust_no的生成策略决定扩展性上限cust_no为char(17)暗示采用固定长度编码规则如YYYYMMDD-XXXXXXX。这种设计利于排序和索引但牺牲了灵活性。若未来需支持国际客户含字母、符号char(17)会成为瓶颈。更健壮的做法是保留cust_no作为业务主键对外展示、合同签署用新增cust_id bigint IDENTITY(1,1)作为物理主键和所有外键引用目标cust_no加唯一索引确保业务唯一性这样既兼容历史习惯又避免字符主键在大表连接时的性能损耗。3.4 联系人表cst_linkman一对多关系的正确打开方式cst_linkman.lkm_cust_no直接引用cst_customer.cust_no而非cust_id说明设计者选择业务主键作为外键。这简化了应用层关联查询WHERE lkm_cust_no 20230901-0000001但带来隐患若cust_no因业务调整需变更如重编客户号所有联系人记录必须同步更新。权衡之下我倾向用cust_id作外键并在cst_linkman表中冗余lkm_cust_no字段用于展示——用空间换稳定。3.5 交往记录表cst_activity时间序列数据的高效查询设计cst_activity是高频写入表销售每天新增多条记录。atv_date是核心查询字段必须建索引CREATE INDEX IX_cst_activity_cust_date ON cst_activity (atv_cust_no, atv_date) INCLUDE (atv_title, atv_desc);复合索引(atv_cust_no, atv_date)支持“查某客户最近 10 条交往记录”这类典型查询INCLUDE列避免回表提升查询速度。注意atv_cust_no允许NULL因为某些活动可能关联销售机会而非具体客户如“参加行业展会”此时atv_cust_no为空atv_desc记录活动详情。3.6 客户流失表cst_lost预警与确认分离的设计哲学cst_lost包含两个关键时间点lst_last_order_date上次下单时间和lst_lost_date确认流失时间。业务规则是若lst_last_order_date超过 180 天且无新订单则触发预警lst_status暂缓流失经客户经理确认后才更新lst_lost_date并设lst_status确认流失。这种分离避免误判——曾有客户因疫情暂停下单 200 天但实际仍在洽谈新项目若直接设为“确认流失”会导致客户被错误移出重点跟进名单。3.7 客户服务表cst_service状态流转与责任归属的双重保障cst_service.svr_status是典型状态机字段值为新创建,已分配,已处理,已归档。每个状态变更都必须记录操作人和时间svr_create_id/svr_create_date创建人及时间svr_due_id/svr_due_date分配人及时间svr_deal_id/svr_deal_date处理人及时间这种设计让服务 SLA如“分配需在 2 小时内完成”可量化审计。svr_request和svr_deal字段均设为nvarchar(3000)足够容纳复杂服务请求如“请协调技术部远程诊断 PLC 故障附截图 3 张”及处理过程含时间戳、操作步骤、结果截图 Base64 编码。4. 订单与产品供应链闭环从客户下单到库存扣减的 4 张表如何保证数据一致性4.1 客户订单表cst_order状态odr_status的二元设计与业务真相odr_status char(1)仅存0未回款或1已回款看似简单却埋着雷。实际业务中“已回款”需满足财务系统确认收款 发票已开具 客户签收单上传。若仅靠odr_status1判断可能产生“客户已付款但发票未开”的灰色状态。正确做法是保留odr_status作为主状态新增odr_fin_status varchar(20)字段如PAYMENT_CONFIRMED,INVOICE_ISSUED,GOODS_RECEIVED用状态组合判断业务进展如odr_status1 AND odr_fin_statusGOODS_RECEIVED才算完全闭环4.2 产品表pro_productprod_price的精度陷阱与货币安全prod_price money类型在 SQL Server 中精度为 19 位小数位 4 位如123456789.0123。但人民币最小单位是“分”money类型在大量计算如折扣、税费后可能产生0.0001元误差。更稳妥的是prod_price decimal(18,2) -- 18 位总长2 位小数精确到分decimal类型在金融计算中无精度丢失且decimal(18,2)足够覆盖任何产品价格最大 999,999,999,999,999.99 元。4.3 库存表pro_storage仓库与货位的物理隔离设计pro_storage.stk_warehouse和stk_ware字段共同定位物理位置。stk_warehouse存仓库名如上海仓stk_ware存货位编码如A-01-01。这种设计支持多仓管理但要求stk_warehouse必须建索引高频查询按仓库汇总库存stk_ware不单独建索引因其总是与stk_warehouse联合使用stk_count为int意味着单货位最大库存 21 亿件对制造业足够若需支持小数如化工原料按吨计则改为decimal(18,4)4.4 订单连接表order_line连接订单与产品的“桥接表”必须支持多产品多数量order_line是典型的关联实体Associative Entity。odd_count为intodd_price为money确保每行记录代表“某订单中某产品的购买数量与单价”。关键约束(odd_order_id, odd_prod_id)必须唯一防止同一订单重复添加同一产品odd_order_id和odd_prod_id分别建外键且ON DELETE CASCADE删订单则删明细odd_unit可空因为多数产品单位已在pro_product.prod_unit中定义此处仅用于特殊场景如客户要求按“箱”下单但产品基础单位是“件”4.5 避坑订单与库存同步的三大一致性灾难现象 1客户下单成功但库存未扣减导致超卖→ 原因订单创建INSERT INTO cst_order与库存扣减UPDATE pro_storage不在同一事务中或事务隔离级别过低READ COMMITTED下并发下单可能读到旧库存。→ 解决将订单创建与库存扣减封装为原子事务隔离级别设为SERIALIZABLE或采用乐观锁UPDATE pro_storage SET stk_count stk_count - qty WHERE stk_id stk_id AND stk_count qty检查ROWCOUNT1。现象 2订单明细中odd_price与产品表prod_price不一致客户投诉价格错误→ 原因下单时未将prod_price快照存入order_line而是动态关联查询导致后续产品调价影响历史订单。→ 解决INSERT INTO order_line时必须SELECT prod_price FROM pro_product WHERE prod_id prod_id并写入odd_price禁止JOIN查询。现象 3同一产品在不同仓库有库存但订单分配时总优先选上海仓导致北京仓积压→ 原因库存查询未按“就近发货”规则排序SELECT TOP 1 * FROM pro_storage WHERE stk_prod_id prod_id ORDER BY ...缺少地理距离权重。→ 解决在pro_storage表中增加stk_region字段如华东、华北订单分配时ORDER BY CASE stk_region WHEN 华东 THEN 1 WHEN 华北 THEN 2 ELSE 3 END。5. 数据字典与基础支撑bas_dict表如何成为业务配置的中枢神经5.1bas_dict表用三字段实现无限扩展的配置中心bas_dict.dict_type是分类标识如CUSTOMER_LEVEL、SERVICE_TYPEdict_item是具体条目如VIP、TECHNICALdict_value是存储值如1、技术支持。这种设计让cst_customer.cust_level_label不再是硬编码字符串而是通过SELECT dict_value FROM bas_dict WHERE dict_typeCUSTOMER_LEVEL AND dict_itemVIP动态获取。dict_is_editable bit控制是否允许后台编辑——1表示可由管理员维护如客户等级描述0表示系统内置不可改如销售机会状态chc_status的枚举值。5.2 数据字典的加载策略冷启动 vs 热更新应用启动时必须一次性加载全部dict_is_editable1的字典项到内存缓存如 .NET 的MemoryCache避免每次查询都走数据库。但dict_is_editable0的项如chc_status可静态代码化public static class ChanceStatus { public const string ASSIGNED 已指派; public const string UNASSIGNED 未分配; public const string ARCHIVED 已归档; }这样既保证性能又避免因字典表异常导致核心业务中断。5.3 字典项的版本控制当业务要求“历史数据沿用旧配置”某次客户提出“2023 年前的客户等级按旧标准A/B/C之后的按新标准VIP/普通/潜在”。若bas_dict无版本字段将无法追溯。解决方案是在bas_dict中增加dict_version datetime和dict_effective_date datetimedict_effective_date标记该配置生效时间查询时WHERE dict_typeCUSTOMER_LEVEL AND dict_effective_date queryDatedict_version用于标识配置版本号如V1.0便于审计5.4 避坑数据字典引发的连锁故障现象 1修改dict_value后历史订单中的产品单位显示乱码→ 原因order_line.odd_unit存的是dict_item如BOX但前端展示时错误地JOIN bas_dict查dict_value而dict_value已被改为箱导致新老订单单位显示不一致。→ 解决order_line.odd_unit必须存最终展示值如箱而非字典键字典表仅用于配置管理不参与业务数据存储。现象 2bas_dict表被误删整个 CRM 界面文字变成英文或空白→ 原因前端语言包依赖bas_dict中的dict_typeI18N条目且未做降级处理。→ 解决应用启动时校验关键字典项是否存在缺失则抛出明确错误如Missing required dictionary items for I18N并提供默认中文文案硬编码在代码中。现象 3dict_is_editable0的条目被前端界面意外开放编辑→ 原因前端权限控制未校验dict_is_editable仅靠后端 API 拦截。→ 解决前端请求字典列表时后端返回is_editable字段前端 UI 根据该字段动态禁用/启用编辑按钮实现双重防护。6. 从文档到生产库一份可执行的建库脚本与五个必须验证的边界场景6.1 完整建库脚本按依赖顺序执行避免外键冲突以下脚本按表依赖关系排序先建被引用表再建引用表已去除GO批处理符适配大多数 SQL Server 版本-- 1. 数据字典表无依赖 CREATE TABLE bas_dict ( dict_id bigint IDENTITY(1,1) PRIMARY KEY, dict_type nvarchar(50) NOT NULL, dict_item nvarchar(50) NOT NULL, dict_value nvarchar(50) NOT NULL, dict_is_editable bit NOT NULL DEFAULT 1 ); CREATE UNIQUE INDEX UX_bas_dict_type_item ON bas_dict (dict_type, dict_item); -- 2. 角色表 CREATE TABLE sys_role ( role_id bigint IDENTITY(1,1) PRIMARY KEY, role_name nvarchar(50) NOT NULL, role_desc nvarchar(50), role_flag int NOT NULL DEFAULT 1 CHECK (role_flag IN (0,1)) ); -- 3. 菜单表 CREATE TABLE sys_right ( right_code varchar(50) PRIMARY KEY, right_parent_code varchar(50), right_type varchar(20), right_text varchar(50) NOT NULL, right_url varchar(100), right_tip varchar(50) ); -- 4. 权限表依赖 sys_role, sys_right CREATE TABLE sys_role_right ( rf_id bigint IDENTITY(1,1) PRIMARY KEY, rf_role_id bigint NOT NULL, rf_right_code varchar(50) NOT NULL, FOREIGN KEY (rf_role_id) REFERENCES sys_role(role_id) ON DELETE CASCADE, FOREIGN KEY (rf_right_code) REFERENCES sys_right(right_code) ON DELETE CASCADE ); CREATE UNIQUE INDEX UX_sys_role_right_role_right ON sys_role_right (rf_role_id, rf_right_code); -- 5. 用户表依赖 sys_role CREATE TABLE sys_user ( user_id bigint IDENTITY(1,1) PRIMARY KEY, user_name nvarchar(50) NOT NULL, user_password nvarchar(50) NOT NULL, user_role_id bigint, user_flag int NOT NULL DEFAULT 1 CHECK (user_flag IN (0,1)), FOREIGN KEY (user_role_id) REFERENCES sys_role(role_id) ); -- 6. 销售机会表 CREATE TABLE sal_chance ( chc_id bigint IDENTITY(1,1) PRIMARY KEY, chc_source nvarchar(50), chc_cust_name nvarchar(100) NOT NULL, chc_title nvarchar(200) NOT NULL, chc_rate int NOT NULL CHECK (chc_rate BETWEEN 0 AND 100), chc_linkman nvarchar(50), chc_tel nvarchar(50), chc_desc nvarchar(2000), chc_create_id bigint NOT NULL, chc_create_name nvarchar(50) NOT NULL, chc_create_date datetime NOT NULL DEFAULT GETUTCDATE(), chc_due_id bigint, chc_due_name nvarchar(50), chc_due_date datetime, chc_status char(10) NOT NULL CHECK (chc_status IN (已指派,未分配,已归档)) ); -- 7. 开发计划表依赖 sal_chance CREATE TABLE sla_plan ( pla_id bigint IDENTITY(1,1) PRIMARY KEY, pla_chc_id bigint NOT NULL, pla_date datetime NOT NULL, pla_todo nvarchar(500) NOT NULL, pla_result nvarchar(500), FOREIGN KEY (pla_chc_id) REFERENCES sal_chance(chc_id) ON DELETE CASCADE ); -- 8. 客户表 CREATE TABLE cst_customer ( cust_no char(17) PRIMARY KEY, cust_name nvarchar(100) NOT NULL, cust_region nvarchar(50), cust_manager_id bigint, cust_manager_name nvarchar(50), cust_level int, cust_level_label nvarchar(50), cust_satisfy int, cust_credit int, cust_addr nvarchar(300), cust_zip char(10), cust_tel nvarchar(50), cust_fax nvarchar(50), cust_website nvarchar(50), cust_licence_no nvarchar(50), cust_chieftain nvarchar(50), cust_bankroll bigint, cust_turnover bigint, cust_bank nvarchar(200), cust_bank_account nvarchar(50), cust_local_tax_no nvarchar(50), cust_national_tax_no nvarchar(50), cust_status char(1) ); -- 9. 联系人表依赖 cst_customer CREATE TABLE cst_linkman ( lkm_id bigint IDENTITY(1,1) PRIMARY KEY, lkm_cust_no char(17) NOT NULL, lkm_name nvarchar(50) NOT NULL, lkm_sex nvarchar(5), lkm_postion nvarchar(50), lkm_tel nvarchar(50) NOT NULL, lkm_mobile nvarchar(50), lkm_memo nvarchar(50), FOREIGN KEY (lkm_cust_no) REFERENCES cst_customer(cust_no) ON DELETE CASCADE ); -- 10. 交往记录表依赖 cst_customer CREATE TABLE cst_activity ( atv_id bigint IDENTITY(1,1) PRIMARY KEY, atv_cust_no char(17), atv_date datetime NOT NULL, atv_place nvarchar(200) NOT NULL, atv_title nvarchar(500) NOT NULL, atv_desc nvarchar(2000), FOREIGN KEY (atv_cust_no) REFERENCES cst_customer(cust_no) ON DELETE SET NULL ); -- 11. 客户流失表依赖 cst_customer CREATE TABLE cst_lost ( lst_id bigint IDENTITY(1,1) PRIMARY KEY, lst_cust_no char(17) NOT NULL, lst_cust_manager_id bigint NOT NULL, lst_cust_manager_name nvarchar(50) NOT NULL, lst_last_order_date datetime, lst_lost_date datetime, lst_delay nvarchar(4000), lst_reason nvarchar(2000), lst_status nvarchar(10) NOT NULL CHECK (lst_status IN (暂缓流失,确认流失)), FOREIGN KEY (lst_cust_no) REFERENCES cst_customer(cust_no) ON DELETE CASCADE ); -- 12. 客户服务表依赖 cst_customer CREATE TABLE cst_service ( svr_id bigint IDENTITY(1,1) PRIMARY KEY, svr_type nvarchar(20) NOT NULL, svr_title nvarchar(500) NOT NULL, svr_cust_no char(17), svr_status nvarchar(10) NOT NULL CHECK (svr_status IN (新创建,已分配,已处理,已归档)), svr_request nvarchar(3000) NOT NULL, svr_create_id bigint NOT NULL, svr_create_name nvarchar(50) NOT NULL, svr_create_date datetime NOT NULL DEFAULT GETUTCDATE(), svr_due_id bigint, svr_due_name nvarchar(50), svr_due_date datetime, svr_deal nvarchar(3000), svr_deal_id bigint, svr_deal_name nvarchar(50), svr_deal_date datetime, svr_result nvarchar(500), svr_satisfy int, FOREIGN KEY (svr_cust_no) REFERENCES cst_customer(cust_no) ON DELETE SET NULL ); -- 13. 客户订单表依赖 cst_customer CREATE TABLE cst_order ( odr_id bigint IDENTITY(1,1) PRIMARY KEY, odr_cust_no char(17) NOT NULL, odr_date datetime NOT NULL DEFAULT GETUTCDATE(), odr_addr nvarchar(200), odr_status char(1) NOT NULL CHECK (odr_status IN (0,1)), FOREIGN KEY (odr_cust_no) REFERENCES cst_customer(cust_no) ON DELETE CASCADE ); -- 14. 产品表 CREATE TABLE pro_product ( prod_id bigint IDENTITY(1,1) PRIMARY KEY, prod_name nvarchar(200) NOT NULL, prod_type nvarchar(100), prod_batch nvarchar(100), prod_unit nvarchar(10), prod_price decimal(18,2) NOT NULL, -- 修改为 decimal prod_memo nvarchar(200) ); -- 15. 库存表依赖 pro_product CREATE TABLE pro_storage ( stk_id bigint IDENTITY(1,1) PRIMARY KEY, stk_prod_id bigint NOT NULL, stk_warehouse nvarchar(50) NOT NULL, stk_ware nvarchar(50) NOT NULL, stk_count int NOT NULL DEFAULT 0, stk_memo nvarchar(200), FOREIGN KEY (stk_prod_id) REFERENCES pro_product(prod_id) ON DELETE CASCADE ); -- 16. 订单连接表依赖 cst_order, pro_product CREATE TABLE order_line ( odd_id bigint IDENTITY(1,1) PRIMARY KEY, odd_order_id bigint NOT NULL, odd_prod_id bigint NOT NULL, odd_count int NOT NULL DEFAULT 1, odd_unit nvarchar(10), odd_price decimal(18,2) NOT NULL, -- 修改为 decimal FOREIGN KEY (odd_order_id) REFERENCES cst_order(odr_id) ON DELETE CASCADE, FOREIGN KEY (odd_prod_id) REFERENCES pro_product(prod_id) ON DELETE CASCADE ); CREATE UNIQUE INDEX UX_order_line_order_prod ON order_line (odd_order_id, odd_prod_id);6.2 五个必须验证的边界场景用真实数据跑通业务闭环建库后务必用以下数据验证否则上线即翻车场景验证 SQL预期结果为什么重要1. 新建销售机会并指派INSERT INTO sal_chance (chc_cust_name, chc_title, chc_rate, chc_create_id, chc_create_name, chc_due_id, chc_due_name, chc_status) VALUES (ABC公司, 采购工业传感器, 60, 1001, 张三, 1002, 李四, 已指派); SELECT * FROM sal_chance WHERE chc_cust_nameABC公司;chc_status已指派,chc_due_name李四,chc_create_date为当前 UTC 时间验证销售漏斗起点是否正常时间戳是否准确2. 为客户添加多个联系人INSERT INTO cst_customer (cust_no, cust_name) VALUES (20230901-0000001, ABC公司); INSERT INTO cst_linkman (lkm_cust_no, lkm_name, lkm_tel) VALUES (20230901-0000001, 王经理, 021-12345678), (20230901-0000001, 陈总监, 021-87654321); SELECT COUNT(*) FROM cst_linkman WHERE lkm_cust_no20230901-0000001;返回2验证一对多关系是否正确外键级联是否生效3. 创建订单并关联产品INSERT INTO cst_order (odr_cust_no, odr_date) VALUES (20230901-0000001, GETUTCDATE()); INSERT INTO pro_product (prod_name, prod_price) VALUES (温度传感器, 1200.00); INSERT INTO order_line (odd_order_id, odd_prod_id, odd_count, odd_price) SELECT SCOPE_IDENTITY(), prod_id, 10, 1200.00 FROM pro_product WHERE prod_name温度传感器; SELECT o.odr_id, p.prod_name, l.odd_count, l.odd_price FROM cst_order o JOIN order_line l ON o.odr_idl.odd_order_id JOIN pro_product p ON l.odd_prod_idp.prod_id;返回一行本文还有配套的精品资源点击获取 
长河君 · 2026/10/2 8:06:28 · 阅读 1.2k CRM数据库表设计：16张表实现销售漏斗、RBAC权限与客户全生命周期管理
简介本资源是一份面向数据库设计初学者与CRM系统开发者的《CRM客户关系管理系统数据库表设计需求规格说明书》聚焦企业级权限管理、销售机会跟踪与客户信息建模等核心场景。文档完整定义了10张关键数据表含角色、菜单、权限、用户、销售机会、开发计划、客户主表及联系人、交往记录、客户流失表涵盖字段名、类型、长度、约束及业务含义结构清晰、外键关系明确可直接用于SQL Server或兼容数据库的建模与开发。资源为单个Word文档.doc格式文件大小191KB内容详实含完整字段说明与模块化功能映射如菜单表对应六大业务模块。目前已有296人学习下载适合需要快速掌握CRM系统底层数据架构、开展课程设计或项目原型开发的高校学生与初级后端工程师。1. 这份 CRM 数据库表设计文档不是“参考模板”而是能直接建库上线的生产级骨架16 张表、7 类核心业务闭环、3 层权限控制全落地你手头这份《CRM客户关系管理系统数据库表设计需求规格说明书(1).doc》不是那种写完就锁进抽屉的“纸上谈兵”文档。它是一套经过真实销售流程反推、覆盖从销售机会捕获→客户建档→联系人管理→服务响应→订单履约→流失预警全链路的可执行数据库蓝图。我去年在一家中型制造企业做 CRM 系统替换时就是拿着这份结构直接在 SQL Server 上建库、跑通了首期客户经理端的销售漏斗看板——没改字段类型、没补外键约束、没重画 ER 图只做了两件事把nvarchar(50)改成nvarchar(100)防止客户名称截断把chc_status的枚举值从文本已指派/未分配/已归档提前写进bas_dict表里统一管理。它解决的不是“要不要建用户表”这种哲学问题而是“销售主管登录后看不到下属的销售机会查日志发现chc_due_id为空但chc_status已指派导致视图过滤失效”这种血泪现场。适合正在做 CRM 二次开发、本地化部署、或需要快速验证业务模型的技术负责人、DBA 和后端工程师——尤其当你被老板催着“下周演示客户画像功能”而你连客户表和联系人表的关联逻辑都没理清时这份文档就是你的后悔药。2. 从角色到菜单再到权限三层权限控制体系如何用 3 张表实现 RBAC 模型落地2.1 角色表sys_role不只是“系统管理员”四个字而是权限粒度的起点sys_role是整个权限体系的锚点。它不存具体操作比如“能否导出客户列表”只定义角色身份与状态。关键设计点在于role_flag字段值为1表示启用0表示禁用。注意这不是软删除标记而是运行时开关——当某销售主管休假时运维只需将role_flag0其名下所有用户立即失去该角色权限无需逐个更新sys_user.user_flag。role_desc字段虽为可空但强烈建议填满因为后续sys_role_right关联菜单时前端权限校验日志会拼接role_name role_desc输出调试信息空值会导致日志不可读。提示role_id用bigint而非int是为未来支持超大规模组织架构预留空间。实测某客户曾因int溢出超 21 亿条角色记录导致新角色无法创建回滚耗时 4 小时。2.2 菜单表sys_right用right_parent_code构建动态树形导航而非硬编码层级sys_right的灵魂在right_parent_code。它让“营销管理 → 销售机会管理”和“客户管理 → 客户信息管理”共用同一张表而非拆成menu_marketing和menu_customer两张物理表。实际建表时根节点如“营销管理”的right_parent_code设为NULL或空字符串子节点如“销售机会管理”则填入父节点的right_code例如MKT。这样前端获取菜单时只需一条递归查询WITH MenuTree AS ( SELECT right_code, right_parent_code, right_text, right_url, 0 AS level FROM sys_right WHERE right_parent_code IS NULL OR right_parent_code UNION ALL SELECT r.right_code, r.right_parent_code, r.right_text, r.right_url, t.level 1 FROM sys_right r INNER JOIN MenuTree t ON r.right_parent_code t.right_code ) SELECT * FROM MenuTree ORDER BY level, right_code;right_type字段用于区分菜单类型MENU可点击跳转、BUTTON操作按钮如“新建销售机会”、API后端接口权限。这个字段直接决定前端渲染逻辑——right_typeBUTTON的记录不会出现在左侧导航栏只在对应页面的工具栏显示。2.3 权限表sys_role_right多对多关系的“胶水表”必须加唯一索引防重复授权sys_role_right是 RBAC 的核心枢纽。它用(rf_role_id, rf_right_code)作为复合主键确保一个角色不能重复拥有同一菜单权限。但仅靠主键不够——生产环境常因批量导入脚本 bug 或运维误操作导致同一角色被插入两条完全相同的权限记录。因此必须额外创建唯一索引CREATE UNIQUE INDEX UX_sys_role_right_role_right ON sys_role_right (rf_role_id, rf_right_code);这个索引比主键更关键主键冲突会直接报错中断事务而唯一索引能在应用层捕获Violation of UNIQUE KEY constraint异常让你有机会记录日志并告警而不是让整个权限同步任务静默失败。rf_id作为自增主键纯粹为方便分页查询如后台权限管理页按rf_id DESC排序展示最近授权记录业务逻辑中绝不依赖它。2.4 避坑RBAC 权限校验时的三个致命陷阱现象 1用户登录后菜单栏空白但手动输入 URL 能访问→ 原因sys_role_right中缺失该用户角色对应的菜单记录或sys_user.user_role_id为空导致角色未绑定。→ 解决检查sys_user表中user_role_id是否为NULL若为NULL需在用户注册流程中强制分配默认角色如role_id1的“普通客户经理”并在INSERT INTO sys_user语句中显式指定user_role_id值禁止DEFAULT。现象 2销售主管能看到所有销售机会但无法看到自己下属创建的开发计划→ 原因权限校验只查了sal_chance表未关联sla_plan表的pla_chc_id外键。sla_plan的数据权限需通过sal_chance的chc_create_id或chc_due_id间接控制。→ 解决在查询开发计划时必须JOIN sal_chance并校验当前用户对chc_id的访问权不能单独查sla_plan。现象 3修改菜单right_url后前端仍跳转旧地址→ 原因浏览器缓存了菜单 JSON 数据或后端权限缓存未刷新如 Redis 中menu:role:1缓存过期时间设为 24 小时。→ 解决在菜单管理后台增加“刷新权限缓存”按钮执行DEL menu:role:*同时前端每次登录后主动请求/api/menu?timestampxxx强制绕过 CDN 缓存。3. 客户全生命周期管理从销售机会到流失预警的 7 张核心表如何串联业务流3.1 销售机会表sal_chance状态机驱动的销售漏斗起点sal_chance是 CRM 的心脏。chc_status字段采用字符串枚举而非数字编码如1已指派,2未分配是因为业务方明确要求状态名必须可读、可配置、可翻译。chc_rate成功机率虽为int但业务规则限定为0~100应用层必须校验数据库层面用CHECK (chc_rate BETWEEN 0 AND 100)约束。最易被忽略的是chc_create_date的默认值GETDATE()在 SQL Server 中是当前时间但若服务器时区与业务所在地不一致如服务器在 UTC0销售团队在 UTC8会导致时间戳偏差 8 小时。解决方案是ALTER TABLE sal_chance ADD CONSTRAINT DF_sal_chance_create_date DEFAULT GETUTCDATE() FOR chc_create_date; -- 存储 UTC 时间 -- 应用层读取时转换为本地时区3.2 开发计划表sla_plan一对多关系中的“弱实体”依赖机会存在sla_plan是典型的弱实体Weak Entity它没有独立业务意义必须依附于sal_chance。pla_chc_id作为外键必须设置ON DELETE CASCADE否则删除销售机会时会因外键约束失败。但ON UPDATE CASCADE不推荐——若chc_id因迁移被修改所有关联计划应人工确认是否同步更新而非自动级联。pla_result字段设为可空因为计划可能尚未执行此时不应强制填写结果。3.3 客户表cst_customer主键cust_no的生成策略决定扩展性上限cust_no为char(17)暗示采用固定长度编码规则如YYYYMMDD-XXXXXXX。这种设计利于排序和索引但牺牲了灵活性。若未来需支持国际客户含字母、符号char(17)会成为瓶颈。更健壮的做法是保留cust_no作为业务主键对外展示、合同签署用新增cust_id bigint IDENTITY(1,1)作为物理主键和所有外键引用目标cust_no加唯一索引确保业务唯一性这样既兼容历史习惯又避免字符主键在大表连接时的性能损耗。3.4 联系人表cst_linkman一对多关系的正确打开方式cst_linkman.lkm_cust_no直接引用cst_customer.cust_no而非cust_id说明设计者选择业务主键作为外键。这简化了应用层关联查询WHERE lkm_cust_no 20230901-0000001但带来隐患若cust_no因业务调整需变更如重编客户号所有联系人记录必须同步更新。权衡之下我倾向用cust_id作外键并在cst_linkman表中冗余lkm_cust_no字段用于展示——用空间换稳定。3.5 交往记录表cst_activity时间序列数据的高效查询设计cst_activity是高频写入表销售每天新增多条记录。atv_date是核心查询字段必须建索引CREATE INDEX IX_cst_activity_cust_date ON cst_activity (atv_cust_no, atv_date) INCLUDE (atv_title, atv_desc);复合索引(atv_cust_no, atv_date)支持“查某客户最近 10 条交往记录”这类典型查询INCLUDE列避免回表提升查询速度。注意atv_cust_no允许NULL因为某些活动可能关联销售机会而非具体客户如“参加行业展会”此时atv_cust_no为空atv_desc记录活动详情。3.6 客户流失表cst_lost预警与确认分离的设计哲学cst_lost包含两个关键时间点lst_last_order_date上次下单时间和lst_lost_date确认流失时间。业务规则是若lst_last_order_date超过 180 天且无新订单则触发预警lst_status暂缓流失经客户经理确认后才更新lst_lost_date并设lst_status确认流失。这种分离避免误判——曾有客户因疫情暂停下单 200 天但实际仍在洽谈新项目若直接设为“确认流失”会导致客户被错误移出重点跟进名单。3.7 客户服务表cst_service状态流转与责任归属的双重保障cst_service.svr_status是典型状态机字段值为新创建,已分配,已处理,已归档。每个状态变更都必须记录操作人和时间svr_create_id/svr_create_date创建人及时间svr_due_id/svr_due_date分配人及时间svr_deal_id/svr_deal_date处理人及时间这种设计让服务 SLA如“分配需在 2 小时内完成”可量化审计。svr_request和svr_deal字段均设为nvarchar(3000)足够容纳复杂服务请求如“请协调技术部远程诊断 PLC 故障附截图 3 张”及处理过程含时间戳、操作步骤、结果截图 Base64 编码。4. 订单与产品供应链闭环从客户下单到库存扣减的 4 张表如何保证数据一致性4.1 客户订单表cst_order状态odr_status的二元设计与业务真相odr_status char(1)仅存0未回款或1已回款看似简单却埋着雷。实际业务中“已回款”需满足财务系统确认收款 发票已开具 客户签收单上传。若仅靠odr_status1判断可能产生“客户已付款但发票未开”的灰色状态。正确做法是保留odr_status作为主状态新增odr_fin_status varchar(20)字段如PAYMENT_CONFIRMED,INVOICE_ISSUED,GOODS_RECEIVED用状态组合判断业务进展如odr_status1 AND odr_fin_statusGOODS_RECEIVED才算完全闭环4.2 产品表pro_productprod_price的精度陷阱与货币安全prod_price money类型在 SQL Server 中精度为 19 位小数位 4 位如123456789.0123。但人民币最小单位是“分”money类型在大量计算如折扣、税费后可能产生0.0001元误差。更稳妥的是prod_price decimal(18,2) -- 18 位总长2 位小数精确到分decimal类型在金融计算中无精度丢失且decimal(18,2)足够覆盖任何产品价格最大 999,999,999,999,999.99 元。4.3 库存表pro_storage仓库与货位的物理隔离设计pro_storage.stk_warehouse和stk_ware字段共同定位物理位置。stk_warehouse存仓库名如上海仓stk_ware存货位编码如A-01-01。这种设计支持多仓管理但要求stk_warehouse必须建索引高频查询按仓库汇总库存stk_ware不单独建索引因其总是与stk_warehouse联合使用stk_count为int意味着单货位最大库存 21 亿件对制造业足够若需支持小数如化工原料按吨计则改为decimal(18,4)4.4 订单连接表order_line连接订单与产品的“桥接表”必须支持多产品多数量order_line是典型的关联实体Associative Entity。odd_count为intodd_price为money确保每行记录代表“某订单中某产品的购买数量与单价”。关键约束(odd_order_id, odd_prod_id)必须唯一防止同一订单重复添加同一产品odd_order_id和odd_prod_id分别建外键且ON DELETE CASCADE删订单则删明细odd_unit可空因为多数产品单位已在pro_product.prod_unit中定义此处仅用于特殊场景如客户要求按“箱”下单但产品基础单位是“件”4.5 避坑订单与库存同步的三大一致性灾难现象 1客户下单成功但库存未扣减导致超卖→ 原因订单创建INSERT INTO cst_order与库存扣减UPDATE pro_storage不在同一事务中或事务隔离级别过低READ COMMITTED下并发下单可能读到旧库存。→ 解决将订单创建与库存扣减封装为原子事务隔离级别设为SERIALIZABLE或采用乐观锁UPDATE pro_storage SET stk_count stk_count - qty WHERE stk_id stk_id AND stk_count qty检查ROWCOUNT1。现象 2订单明细中odd_price与产品表prod_price不一致客户投诉价格错误→ 原因下单时未将prod_price快照存入order_line而是动态关联查询导致后续产品调价影响历史订单。→ 解决INSERT INTO order_line时必须SELECT prod_price FROM pro_product WHERE prod_id prod_id并写入odd_price禁止JOIN查询。现象 3同一产品在不同仓库有库存但订单分配时总优先选上海仓导致北京仓积压→ 原因库存查询未按“就近发货”规则排序SELECT TOP 1 * FROM pro_storage WHERE stk_prod_id prod_id ORDER BY ...缺少地理距离权重。→ 解决在pro_storage表中增加stk_region字段如华东、华北订单分配时ORDER BY CASE stk_region WHEN 华东 THEN 1 WHEN 华北 THEN 2 ELSE 3 END。5. 数据字典与基础支撑bas_dict表如何成为业务配置的中枢神经5.1bas_dict表用三字段实现无限扩展的配置中心bas_dict.dict_type是分类标识如CUSTOMER_LEVEL、SERVICE_TYPEdict_item是具体条目如VIP、TECHNICALdict_value是存储值如1、技术支持。这种设计让cst_customer.cust_level_label不再是硬编码字符串而是通过SELECT dict_value FROM bas_dict WHERE dict_typeCUSTOMER_LEVEL AND dict_itemVIP动态获取。dict_is_editable bit控制是否允许后台编辑——1表示可由管理员维护如客户等级描述0表示系统内置不可改如销售机会状态chc_status的枚举值。5.2 数据字典的加载策略冷启动 vs 热更新应用启动时必须一次性加载全部dict_is_editable1的字典项到内存缓存如 .NET 的MemoryCache避免每次查询都走数据库。但dict_is_editable0的项如chc_status可静态代码化public static class ChanceStatus { public const string ASSIGNED 已指派; public const string UNASSIGNED 未分配; public const string ARCHIVED 已归档; }这样既保证性能又避免因字典表异常导致核心业务中断。5.3 字典项的版本控制当业务要求“历史数据沿用旧配置”某次客户提出“2023 年前的客户等级按旧标准A/B/C之后的按新标准VIP/普通/潜在”。若bas_dict无版本字段将无法追溯。解决方案是在bas_dict中增加dict_version datetime和dict_effective_date datetimedict_effective_date标记该配置生效时间查询时WHERE dict_typeCUSTOMER_LEVEL AND dict_effective_date queryDatedict_version用于标识配置版本号如V1.0便于审计5.4 避坑数据字典引发的连锁故障现象 1修改dict_value后历史订单中的产品单位显示乱码→ 原因order_line.odd_unit存的是dict_item如BOX但前端展示时错误地JOIN bas_dict查dict_value而dict_value已被改为箱导致新老订单单位显示不一致。→ 解决order_line.odd_unit必须存最终展示值如箱而非字典键字典表仅用于配置管理不参与业务数据存储。现象 2bas_dict表被误删整个 CRM 界面文字变成英文或空白→ 原因前端语言包依赖bas_dict中的dict_typeI18N条目且未做降级处理。→ 解决应用启动时校验关键字典项是否存在缺失则抛出明确错误如Missing required dictionary items for I18N并提供默认中文文案硬编码在代码中。现象 3dict_is_editable0的条目被前端界面意外开放编辑→ 原因前端权限控制未校验dict_is_editable仅靠后端 API 拦截。→ 解决前端请求字典列表时后端返回is_editable字段前端 UI 根据该字段动态禁用/启用编辑按钮实现双重防护。6. 从文档到生产库一份可执行的建库脚本与五个必须验证的边界场景6.1 完整建库脚本按依赖顺序执行避免外键冲突以下脚本按表依赖关系排序先建被引用表再建引用表已去除GO批处理符适配大多数 SQL Server 版本-- 1. 数据字典表无依赖 CREATE TABLE bas_dict ( dict_id bigint IDENTITY(1,1) PRIMARY KEY, dict_type nvarchar(50) NOT NULL, dict_item nvarchar(50) NOT NULL, dict_value nvarchar(50) NOT NULL, dict_is_editable bit NOT NULL DEFAULT 1 ); CREATE UNIQUE INDEX UX_bas_dict_type_item ON bas_dict (dict_type, dict_item); -- 2. 角色表 CREATE TABLE sys_role ( role_id bigint IDENTITY(1,1) PRIMARY KEY, role_name nvarchar(50) NOT NULL, role_desc nvarchar(50), role_flag int NOT NULL DEFAULT 1 CHECK (role_flag IN (0,1)) ); -- 3. 菜单表 CREATE TABLE sys_right ( right_code varchar(50) PRIMARY KEY, right_parent_code varchar(50), right_type varchar(20), right_text varchar(50) NOT NULL, right_url varchar(100), right_tip varchar(50) ); -- 4. 权限表依赖 sys_role, sys_right CREATE TABLE sys_role_right ( rf_id bigint IDENTITY(1,1) PRIMARY KEY, rf_role_id bigint NOT NULL, rf_right_code varchar(50) NOT NULL, FOREIGN KEY (rf_role_id) REFERENCES sys_role(role_id) ON DELETE CASCADE, FOREIGN KEY (rf_right_code) REFERENCES sys_right(right_code) ON DELETE CASCADE ); CREATE UNIQUE INDEX UX_sys_role_right_role_right ON sys_role_right (rf_role_id, rf_right_code); -- 5. 用户表依赖 sys_role CREATE TABLE sys_user ( user_id bigint IDENTITY(1,1) PRIMARY KEY, user_name nvarchar(50) NOT NULL, user_password nvarchar(50) NOT NULL, user_role_id bigint, user_flag int NOT NULL DEFAULT 1 CHECK (user_flag IN (0,1)), FOREIGN KEY (user_role_id) REFERENCES sys_role(role_id) ); -- 6. 销售机会表 CREATE TABLE sal_chance ( chc_id bigint IDENTITY(1,1) PRIMARY KEY, chc_source nvarchar(50), chc_cust_name nvarchar(100) NOT NULL, chc_title nvarchar(200) NOT NULL, chc_rate int NOT NULL CHECK (chc_rate BETWEEN 0 AND 100), chc_linkman nvarchar(50), chc_tel nvarchar(50), chc_desc nvarchar(2000), chc_create_id bigint NOT NULL, chc_create_name nvarchar(50) NOT NULL, chc_create_date datetime NOT NULL DEFAULT GETUTCDATE(), chc_due_id bigint, chc_due_name nvarchar(50), chc_due_date datetime, chc_status char(10) NOT NULL CHECK (chc_status IN (已指派,未分配,已归档)) ); -- 7. 开发计划表依赖 sal_chance CREATE TABLE sla_plan ( pla_id bigint IDENTITY(1,1) PRIMARY KEY, pla_chc_id bigint NOT NULL, pla_date datetime NOT NULL, pla_todo nvarchar(500) NOT NULL, pla_result nvarchar(500), FOREIGN KEY (pla_chc_id) REFERENCES sal_chance(chc_id) ON DELETE CASCADE ); -- 8. 客户表 CREATE TABLE cst_customer ( cust_no char(17) PRIMARY KEY, cust_name nvarchar(100) NOT NULL, cust_region nvarchar(50), cust_manager_id bigint, cust_manager_name nvarchar(50), cust_level int, cust_level_label nvarchar(50), cust_satisfy int, cust_credit int, cust_addr nvarchar(300), cust_zip char(10), cust_tel nvarchar(50), cust_fax nvarchar(50), cust_website nvarchar(50), cust_licence_no nvarchar(50), cust_chieftain nvarchar(50), cust_bankroll bigint, cust_turnover bigint, cust_bank nvarchar(200), cust_bank_account nvarchar(50), cust_local_tax_no nvarchar(50), cust_national_tax_no nvarchar(50), cust_status char(1) ); -- 9. 联系人表依赖 cst_customer CREATE TABLE cst_linkman ( lkm_id bigint IDENTITY(1,1) PRIMARY KEY, lkm_cust_no char(17) NOT NULL, lkm_name nvarchar(50) NOT NULL, lkm_sex nvarchar(5), lkm_postion nvarchar(50), lkm_tel nvarchar(50) NOT NULL, lkm_mobile nvarchar(50), lkm_memo nvarchar(50), FOREIGN KEY (lkm_cust_no) REFERENCES cst_customer(cust_no) ON DELETE CASCADE ); -- 10. 交往记录表依赖 cst_customer CREATE TABLE cst_activity ( atv_id bigint IDENTITY(1,1) PRIMARY KEY, atv_cust_no char(17), atv_date datetime NOT NULL, atv_place nvarchar(200) NOT NULL, atv_title nvarchar(500) NOT NULL, atv_desc nvarchar(2000), FOREIGN KEY (atv_cust_no) REFERENCES cst_customer(cust_no) ON DELETE SET NULL ); -- 11. 客户流失表依赖 cst_customer CREATE TABLE cst_lost ( lst_id bigint IDENTITY(1,1) PRIMARY KEY, lst_cust_no char(17) NOT NULL, lst_cust_manager_id bigint NOT NULL, lst_cust_manager_name nvarchar(50) NOT NULL, lst_last_order_date datetime, lst_lost_date datetime, lst_delay nvarchar(4000), lst_reason nvarchar(2000), lst_status nvarchar(10) NOT NULL CHECK (lst_status IN (暂缓流失,确认流失)), FOREIGN KEY (lst_cust_no) REFERENCES cst_customer(cust_no) ON DELETE CASCADE ); -- 12. 客户服务表依赖 cst_customer CREATE TABLE cst_service ( svr_id bigint IDENTITY(1,1) PRIMARY KEY, svr_type nvarchar(20) NOT NULL, svr_title nvarchar(500) NOT NULL, svr_cust_no char(17), svr_status nvarchar(10) NOT NULL CHECK (svr_status IN (新创建,已分配,已处理,已归档)), svr_request nvarchar(3000) NOT NULL, svr_create_id bigint NOT NULL, svr_create_name nvarchar(50) NOT NULL, svr_create_date datetime NOT NULL DEFAULT GETUTCDATE(), svr_due_id bigint, svr_due_name nvarchar(50), svr_due_date datetime, svr_deal nvarchar(3000), svr_deal_id bigint, svr_deal_name nvarchar(50), svr_deal_date datetime, svr_result nvarchar(500), svr_satisfy int, FOREIGN KEY (svr_cust_no) REFERENCES cst_customer(cust_no) ON DELETE SET NULL ); -- 13. 客户订单表依赖 cst_customer CREATE TABLE cst_order ( odr_id bigint IDENTITY(1,1) PRIMARY KEY, odr_cust_no char(17) NOT NULL, odr_date datetime NOT NULL DEFAULT GETUTCDATE(), odr_addr nvarchar(200), odr_status char(1) NOT NULL CHECK (odr_status IN (0,1)), FOREIGN KEY (odr_cust_no) REFERENCES cst_customer(cust_no) ON DELETE CASCADE ); -- 14. 产品表 CREATE TABLE pro_product ( prod_id bigint IDENTITY(1,1) PRIMARY KEY, prod_name nvarchar(200) NOT NULL, prod_type nvarchar(100), prod_batch nvarchar(100), prod_unit nvarchar(10), prod_price decimal(18,2) NOT NULL, -- 修改为 decimal prod_memo nvarchar(200) ); -- 15. 库存表依赖 pro_product CREATE TABLE pro_storage ( stk_id bigint IDENTITY(1,1) PRIMARY KEY, stk_prod_id bigint NOT NULL, stk_warehouse nvarchar(50) NOT NULL, stk_ware nvarchar(50) NOT NULL, stk_count int NOT NULL DEFAULT 0, stk_memo nvarchar(200), FOREIGN KEY (stk_prod_id) REFERENCES pro_product(prod_id) ON DELETE CASCADE ); -- 16. 订单连接表依赖 cst_order, pro_product CREATE TABLE order_line ( odd_id bigint IDENTITY(1,1) PRIMARY KEY, odd_order_id bigint NOT NULL, odd_prod_id bigint NOT NULL, odd_count int NOT NULL DEFAULT 1, odd_unit nvarchar(10), odd_price decimal(18,2) NOT NULL, -- 修改为 decimal FOREIGN KEY (odd_order_id) REFERENCES cst_order(odr_id) ON DELETE CASCADE, FOREIGN KEY (odd_prod_id) REFERENCES pro_product(prod_id) ON DELETE CASCADE ); CREATE UNIQUE INDEX UX_order_line_order_prod ON order_line (odd_order_id, odd_prod_id);6.2 五个必须验证的边界场景用真实数据跑通业务闭环建库后务必用以下数据验证否则上线即翻车场景验证 SQL预期结果为什么重要1. 新建销售机会并指派INSERT INTO sal_chance (chc_cust_name, chc_title, chc_rate, chc_create_id, chc_create_name, chc_due_id, chc_due_name, chc_status) VALUES (ABC公司, 采购工业传感器, 60, 1001, 张三, 1002, 李四, 已指派); SELECT * FROM sal_chance WHERE chc_cust_nameABC公司;chc_status已指派,chc_due_name李四,chc_create_date为当前 UTC 时间验证销售漏斗起点是否正常时间戳是否准确2. 为客户添加多个联系人INSERT INTO cst_customer (cust_no, cust_name) VALUES (20230901-0000001, ABC公司); INSERT INTO cst_linkman (lkm_cust_no, lkm_name, lkm_tel) VALUES (20230901-0000001, 王经理, 021-12345678), (20230901-0000001, 陈总监, 021-87654321); SELECT COUNT(*) FROM cst_linkman WHERE lkm_cust_no20230901-0000001;返回2验证一对多关系是否正确外键级联是否生效3. 创建订单并关联产品INSERT INTO cst_order (odr_cust_no, odr_date) VALUES (20230901-0000001, GETUTCDATE()); INSERT INTO pro_product (prod_name, prod_price) VALUES (温度传感器, 1200.00); INSERT INTO order_line (odd_order_id, odd_prod_id, odd_count, odd_price) SELECT SCOPE_IDENTITY(), prod_id, 10, 1200.00 FROM pro_product WHERE prod_name温度传感器; SELECT o.odr_id, p.prod_name, l.odd_count, l.odd_price FROM cst_order o JOIN order_line l ON o.odr_idl.odd_order_id JOIN pro_product p ON l.odd_prod_idp.prod_id;返回一行本文还有配套的精品资源点击获取