CRM 数据库如何设计？客户、联系人、商机与跟进记录的关系

CRM 系统的核心：数据模型决定了业务边界

一个 CRM 系统的质量，很大程度上取决于数据库设计。如果表结构混乱，后续功能扩展会非常困难，查询性能也会急剧下降。本文从企业实际需求出发，讨论客户、联系人、商机、跟进记录这四个核心实体的关系，以及扩展字段、索引、审计和数据隔离的常见做法。

以下内容适用于计划自建 CRM 或定制开发的企业负责人和技术团队。我们不会罗列所有字段，而是聚焦于关键关系和设计决策。

一、核心实体与关系

1.1 客户（Account / Company）

客户是 CRM 中最顶层的实体，通常代表一个公司或组织。在设计时，需要考虑以下几点：

• **客户类型**：区分潜在客户、现有客户、合作伙伴等。类型字段可以是一个枚举值，但要注意后续可能新增类型，设计时建议使用代码表而非硬编码。
• **层级关系**：部分企业存在集团-子公司结构。可以通过一个 parent_id 自关联字段实现，但深度不宜超过三级，否则查询复杂度会显著增加。
• **唯一性**：客户名称、统一社会信用代码（国内场景）或注册号（海外场景）应作为唯一性约束。但实际业务中常有重名客户，建议允许重复名称，通过其他字段辅助识别。

常见的客户表字段示例（非完整列表）：
| 字段名 | 类型 | 说明 |
|--------|------|------|
| id | bigint | 主键 |
| name | varchar(255) | 客户名称 |
| type | smallint | 客户类型代码 |
| parent_id | bigint | 上级客户ID |
| industry | varchar(100) | 行业 |
| source | varchar(50) | 来源渠道 |
| status | smallint | 状态（跟进中/已成交/已流失） |
| created_at | timestamp | 创建时间 |
| updated_at | timestamp | 更新时间 |

1.2 联系人（Contact）

联系人是客户下的具体人员。一个客户可以有多个联系人，一个联系人也可以属于多个客户（例如顾问同时在多家公司任职）。常见设计有两种：

• **一对多**：联系人表包含 customer_id 字段，每个联系人只属于一个客户。适用于大多数 B2B 场景，实现简单。
• **多对多**：通过中间表（contact_customer）实现，适用于联系人跨公司场景。代价是查询时需要多一次 JOIN，且维护成本略高。

实际项目中，约 80% 的企业选择一对多设计。如果业务确实需要跨公司联系人，建议在中间表中增加“主要客户”标记字段。

联系人字段需要注意：

• 姓名、手机、邮箱是基本字段，但手机和邮箱可能存在多个。建议设计为可扩展的键值对表，或预留备用字段。
• 职位和部门字段建议单独建表或使用字典表，避免同一职位出现多种写法（如“技术总监”与“技术部总监”）。

1.3 商机（Opportunity）

商机代表一个可能的销售机会，通常关联到客户和联系人。商机表是 CRM 中数据变化最频繁的表之一，设计时需要重点关注：

• **阶段**：从“初步接触”到“谈判中”再到“赢单/输单”。阶段变更会触发一系列操作（如通知、任务创建），因此阶段字段不应直接更新，而是通过状态机管理。
• **金额与概率**：预计成交金额和成交概率。概率通常与阶段关联，但允许销售人员手动调整。
• **产品/服务**：如果涉及多产品，应使用商机明细表（opportunity_item），避免在商机主表中存储 JSON 数组。

商机表常见字段：
| 字段名 | 类型 | 说明 |
|--------|------|------|
| id | bigint | 主键 |
| account_id | bigint | 关联客户 |
| contact_id | bigint | 主要联系人（可选） |
| name | varchar(255) | 商机名称 |
| amount | decimal(15,2) | 预计金额 |
| probability | smallint | 成交概率（0-100） |
| stage_id | smallint | 当前阶段 |
| expected_close_date | date | 预计成交日期 |
| owner_id | bigint | 负责人 |
| created_at | timestamp | 创建时间 |

1.4 跟进记录（Activity / Note）

跟进记录是 CRM 中数据量最大的表，记录每一次与客户的交互。包括电话、邮件、会议、拜访等。

设计要点：

• **类型区分**：通过 type 字段区分不同活动类型。不同类型可能需要不同的附加字段（如通话时长、会议地点）。一种做法是使用单表加通用字段，另一种是使用子类表。对于大多数中小企业，单表加 JSON 扩展字段足够。
• **关联对象**：跟进记录可以关联到客户、联系人或商机。建议使用 polymorphic association（多态关联），即 target_type + target_id 字段，而不是为每种对象建一个外键。这样可以灵活扩展，但查询时需要注意索引。
• **内容存储**：跟进内容通常是长文本，建议使用 TEXT 类型。如果需要搜索，应考虑全文索引（MySQL 的 FULLTEXT 或 PostgreSQL 的 tsvector）。

跟进记录表字段示例：
| 字段名 | 类型 | 说明 |
|--------|------|------|
| id | bigint | 主键 |
| type | smallint | 活动类型（1-电话，2-邮件，3-会议） |
| target_type | varchar(50) | 关联对象类型（account/contact/opportunity） |
| target_id | bigint | 关联对象ID |
| content | text | 跟进内容 |
| start_time | timestamp | 开始时间 |
| duration | int | 持续时间（分钟） |
| creator_id | bigint | 创建人 |
| created_at | timestamp | 创建时间 |

二、扩展字段：如何应对业务变化

没有两个企业的 CRM 需求完全一样。扩展字段的设计直接影响系统的灵活性。

2.1 常见方案

• **预留字段方案**：在表中预留 5-10 个 varchar 字段（如 ext_field1, ext_field2）。优点是查询简单，缺点是可扩展性差，字段类型固定，且随着需求增加很快用完。
• **EAV（实体-属性-值）方案**：使用单独的扩展属性表（entity_id, attribute_name, attribute_value）。灵活但查询性能差，需要多次 JOIN 或 GROUP BY，不适合大量数据。
• **JSON/JSONB 字段方案**：在表中增加一个 JSON 类型字段存储扩展属性。PostgreSQL 的 JSONB 支持索引和部分查询，是当前推荐方案。MySQL 8.0+ 也支持 JSON 字段和虚拟索引。
• **自定义字段表方案**：由管理员在后台定义字段元数据（字段名、类型、验证规则），然后生成对应的列或存储。实现复杂但最灵活，适合大型系统。

对于大多数中小型项目，JSONB 方案在灵活性和性能之间取得了较好的平衡。如果预计扩展字段的查询非常频繁，可以考虑将高频扩展字段拆为独立列。

2.2 实际建议

在项目初期，不要过度设计扩展字段。先确定 80% 的通用字段，预留一个 JSON 字段即可。随着业务发展，再逐步将高频使用的 JSON 字段提升为独立列。

三、索引：查询性能的生命线

CRM 系统的查询模式相对固定，主要集中在：按客户名称搜索、按联系人手机/邮箱查找、查询某个商机的跟进记录、按负责人查看待办事项。针对这些模式设计索引，可以避免大部分性能问题。

3.1 必须建立的索引

• **外键索引**：account_id（联系人表）、account_id 和 contact_id（商机表）、target_type + target_id（跟进记录表）。这些是 JOIN 查询的必经之路。
• **常用查询字段**：客户名称（建议前缀索引）、联系人手机和邮箱、商机阶段和负责人、跟进记录的创建时间和类型。
• **排序和分组字段**：created_at、stage_id、owner_id 等。

3.2 复合索引

复合索引需要根据实际查询语句设计。例如，如果经常查询“某负责人在某阶段的商机”，可以建立 (owner_id, stage_id) 复合索引。如果查询条件还包括金额范围，则考虑 (owner_id, stage_id, amount)。

需要注意的是，复合索引的字段顺序很重要。将区分度高的字段放在前面，可以更有效地缩小查询范围。

3.3 全文索引

如果跟进记录的内容需要全文搜索，建议使用数据库自带的全文索引（MySQL FULLTEXT 或 PostgreSQL tsvector），而不是 LIKE '%keyword%'。后者在数据量超过十万条后性能会急剧下降。

四、审计日志：谁在什么时候做了什么

CRM 中的数据变更需要可追溯。审计日志不仅用于合规，也用于销售管理（如查看谁修改了商机金额）。

4.1 设计要点

• **记录粒度**：记录到字段级别还是行级别？字段级别可以精确知道哪个字段被修改，但数据量会很大。行级别记录更节省空间，但无法看到具体变更内容。
• **变更内容**：建议记录变更前和变更后的值（old_value, new_value）。对于长文本字段，可以只记录变更摘要。
• **触发器 vs 应用层**：使用数据库触发器可以保证审计日志的完整性，但会增加数据库负载。在应用层记录更灵活，但可能遗漏某些变更（如批量更新）。

实际项目中，推荐使用应用层加数据库触发器的混合方案：核心表（商机、客户）使用触发器强制记录，次要表在应用层记录。

4.2 存储与清理

审计日志数据增长很快。建议按月或按季度进行归档，将超过一年的日志迁移到历史表或冷存储。归档策略应在设计阶段就考虑，避免后期数据量过大导致维护困难。

五、数据隔离：多租户与权限控制

如果 CRM 系统需要服务多个部门或子公司，数据隔离是必须解决的问题。

5.1 隔离方案

• **独立数据库**：每个租户一个数据库。隔离性最强，但维护成本高，不适合大量租户。
• **共享数据库，独立 Schema**：每个租户一个 Schema。隔离性较好，但跨租户查询困难。
• **共享数据库，共享表**：通过 tenant_id 字段区分。实现最简单，但需要确保查询时始终带上 tenant_id 条件，否则可能泄露数据。

对于大多数企业自用 CRM，共享表方案足够。如果涉及跨公司数据共享（如集团 CRM），可以考虑独立 Schema 方案。

5.2 权限控制

除了租户隔离，还需要行级权限控制。例如，销售人员只能看到自己的客户，经理可以看到团队客户。

常见实现方式：

• **基于角色的权限**：定义角色（销售、经理、管理员），每个角色有对应的数据范围。
• **基于组织的权限**：通过组织树（部门层级）控制数据可见范围。
• **基于所有者的权限**：数据创建者自动获得所有权，可以共享给其他人。

在实际项目中，这三种方式通常组合使用。权限控制逻辑建议放在应用层，而不是数据库层，因为数据库层实现行级权限会显著增加查询复杂度。

六、总结与建议

一个好的 CRM 数据库设计，不是一蹴而就的。以下是一些经验总结：

• **核心关系**：客户-联系人使用一对多或中间表，商机关联客户和联系人，跟进记录使用多态关联。这是最稳定的基础结构。
• **扩展字段**：优先使用 JSONB，后续再根据使用频率逐步拆分为独立列。
• **索引**：针对查询模式设计索引，而不是为每个字段都建立索引。定期检查慢查询日志，调整索引策略。
• **审计日志**：核心表必须记录字段级别的变更，次要表可以只记录行级变更。提前规划归档策略。
• **数据隔离**：根据企业规模和业务需求选择隔离方案。对于大多数企业，共享表加 tenant_id 足够。

如果团队正在规划 CRM 系统，建议先从核心实体和关系开始，用最小可行产品验证业务逻辑，再逐步引入扩展字段、审计和权限控制。SystemDo 在多个 CRM 项目中采用的原则也是先稳定主数据模型，再围绕已经验证的查询和流程增加复杂度。