支持多租户的CRM系统表结构设计方案

支持多租户的CRM系统表结构设计方案

一、设计概述

设计一套完整的多租户CRM系统数据库表结构，采用共享数据库+租户ID隔离的方案，这种方案具有成本低、维护简单的优势，适合大多数中小型SaaS CRM系统。

二、多租户隔离方案选择

1. 方案对比

隔离方案	安全性	性能影响	维护成本	适用场景
独立数据库	最高	无竞争，性能好	高（多库维护）	大客户、高安全需求
共享数据库+独立Schema	中等	有一定竞争	中等	中大型客户、需逻辑隔离
共享数据库+租户ID隔离	基础安全	有竞争需优化	低	中小型系统、租户数量多

2. 最终选择：共享数据库+租户ID隔离

• 实现方式：所有租户共享数据库表，每张表增加tenant_id字段
• 优势：部署成本低，支持租户数量大，维护简单
• 劣势：需要额外的安全措施和性能优化

三、核心表结构设计

1. 租户管理模块

-- 租户表
CREATE TABLE tenant (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '租户ID',
  tenant_name VARCHAR(100) NOT NULL COMMENT '租户名称',
  tenant_code VARCHAR(50) NOT NULL UNIQUE COMMENT '租户编码',
  contact_person VARCHAR(50) COMMENT '联系人',
  contact_phone VARCHAR(20) COMMENT '联系电话',
  contact_email VARCHAR(100) COMMENT '联系邮箱',
  status TINYINT DEFAULT 1 COMMENT '状态(0:禁用,1:启用)',
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  expired_time DATETIME COMMENT '到期时间',
  remark TEXT COMMENT '备注'
) COMMENT = '租户信息表';

-- 租户配置表
CREATE TABLE tenant_config (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '配置ID',
  tenant_id BIGINT NOT NULL COMMENT '租户ID',
  config_key VARCHAR(50) NOT NULL COMMENT '配置键',
  config_value TEXT COMMENT '配置值',
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE,
  UNIQUE KEY uk_tenant_key (tenant_id, config_key)
) COMMENT = '租户配置表';

2. 用户管理模块

-- 用户表
CREATE TABLE user (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
  tenant_id BIGINT NOT NULL COMMENT '租户ID',
  username VARCHAR(50) NOT NULL COMMENT '用户名',
  password VARCHAR(100) NOT NULL COMMENT '密码(加密存储)',
  real_name VARCHAR(50) COMMENT '真实姓名',
  email VARCHAR(100) COMMENT '邮箱',
  phone VARCHAR(20) COMMENT '手机号',
  department_id BIGINT COMMENT '部门ID',
  position VARCHAR(50) COMMENT '职位',
  status TINYINT DEFAULT 1 COMMENT '状态(0:禁用,1:启用)',
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  last_login_time DATETIME COMMENT '最后登录时间',
  FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE,
  UNIQUE KEY uk_tenant_username (tenant_id, username)
) COMMENT = '用户表';

-- 部门表
CREATE TABLE department (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '部门ID',
  tenant_id BIGINT NOT NULL COMMENT '租户ID',
  dept_name VARCHAR(50) NOT NULL COMMENT '部门名称',
  parent_id BIGINT DEFAULT 0 COMMENT '父部门ID',
  sort_order INT DEFAULT 0 COMMENT '排序',
  status TINYINT DEFAULT 1 COMMENT '状态(0:禁用,1:启用)',
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE
) COMMENT = '部门表';

-- 角色表
CREATE TABLE role (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '角色ID',
  tenant_id BIGINT NOT NULL COMMENT '租户ID',
  role_name VARCHAR(50) NOT NULL COMMENT '角色名称',
  role_code VARCHAR(50) NOT NULL COMMENT '角色编码',
  description TEXT COMMENT '描述',
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE,
  UNIQUE KEY uk_tenant_role (tenant_id, role_code)
) COMMENT = '角色表';

-- 用户角色关联表
CREATE TABLE user_role (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '关联ID',
  tenant_id BIGINT NOT NULL COMMENT '租户ID',
  user_id BIGINT NOT NULL COMMENT '用户ID',
  role_id BIGINT NOT NULL COMMENT '角色ID',
  FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,
  FOREIGN KEY (role_id) REFERENCES role(id) ON DELETE CASCADE,
  UNIQUE KEY uk_user_role (user_id, role_id)
) COMMENT = '用户角色关联表';

-- 权限表
CREATE TABLE permission (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '权限ID',
  permission_code VARCHAR(50) NOT NULL COMMENT '权限编码',
  permission_name VARCHAR(100) NOT NULL COMMENT '权限名称',
  permission_type TINYINT COMMENT '权限类型(1:菜单,2:按钮)',
  parent_id BIGINT DEFAULT 0 COMMENT '父权限ID',
  resource_url VARCHAR(200) COMMENT '资源URL',
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  UNIQUE KEY uk_permission_code (permission_code)
) COMMENT = '权限表';

-- 角色权限关联表
CREATE TABLE role_permission (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '关联ID',
  role_id BIGINT NOT NULL COMMENT '角色ID',
  permission_id BIGINT NOT NULL COMMENT '权限ID',
  FOREIGN KEY (role_id) REFERENCES role(id) ON DELETE CASCADE,
  FOREIGN KEY (permission_id) REFERENCES permission(id) ON DELETE CASCADE,
  UNIQUE KEY uk_role_permission (role_id, permission_id)
) COMMENT = '角色权限关联表';

3. 客户管理模块

-- 客户表
CREATE TABLE customer (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '客户ID',
  tenant_id BIGINT NOT NULL COMMENT '租户ID',
  customer_name VARCHAR(100) NOT NULL COMMENT '客户名称',
  customer_code VARCHAR(50) COMMENT '客户编码',
  level TINYINT DEFAULT 3 COMMENT '客户等级(1-5)',
  industry_id BIGINT COMMENT '所属行业ID',
  region_id BIGINT COMMENT '所属地区ID',
  address VARCHAR(200) COMMENT '地址',
  website VARCHAR(100) COMMENT '网站',
  source_id BIGINT COMMENT '客户来源ID',
  owner_id BIGINT COMMENT '负责人ID',
  status TINYINT DEFAULT 1 COMMENT '状态(1:潜在,2:成交,3:流失)',
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE,
  FOREIGN KEY (owner_id) REFERENCES user(id)
) COMMENT = '客户表';

-- 联系人表
CREATE TABLE contact (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '联系人ID',
  tenant_id BIGINT NOT NULL COMMENT '租户ID',
  customer_id BIGINT NOT NULL COMMENT '所属客户ID',
  name VARCHAR(50) NOT NULL COMMENT '联系人姓名',
  position VARCHAR(50) COMMENT '职位',
  phone VARCHAR(20) COMMENT '电话',
  mobile VARCHAR(20) COMMENT '手机',
  email VARCHAR(100) COMMENT '邮箱',
  is_primary TINYINT DEFAULT 0 COMMENT '是否主要联系人(0:否,1:是)',
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE,
  FOREIGN KEY (customer_id) REFERENCES customer(id) ON DELETE CASCADE
) COMMENT = '联系人表';

-- 客户标签表
CREATE TABLE customer_tag (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '标签ID',
  tenant_id BIGINT NOT NULL COMMENT '租户ID',
  tag_name VARCHAR(50) NOT NULL COMMENT '标签名称',
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE
) COMMENT = '客户标签表';

-- 客户标签关联表
CREATE TABLE customer_tag_relation (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '关联ID',
  tenant_id BIGINT NOT NULL COMMENT '租户ID',
  customer_id BIGINT NOT NULL COMMENT '客户ID',
  tag_id BIGINT NOT NULL COMMENT '标签ID',
  FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE,
  FOREIGN KEY (customer_id) REFERENCES customer(id) ON DELETE CASCADE,
  FOREIGN KEY (tag_id) REFERENCES customer_tag(id) ON DELETE CASCADE,
  UNIQUE KEY uk_customer_tag (customer_id, tag_id)
) COMMENT = '客户标签关联表';

4. 销售管理模块

-- 线索表
CREATE TABLE lead (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '线索ID',
  tenant_id BIGINT NOT NULL COMMENT '租户ID',
  lead_name VARCHAR(100) NOT NULL COMMENT '线索名称',
  contact_name VARCHAR(50) COMMENT '联系人',
  contact_phone VARCHAR(20) COMMENT '联系电话',
  contact_email VARCHAR(100) COMMENT '邮箱',
  source_id BIGINT COMMENT '线索来源ID',
  industry_id BIGINT COMMENT '所属行业ID',
  status TINYINT DEFAULT 1 COMMENT '状态(1:新建,2:跟进中,3:转化,4:废弃)',
  owner_id BIGINT COMMENT '负责人ID',
  estimated_value DECIMAL(15,2) COMMENT '预估价值',
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  converted_time DATETIME COMMENT '转化时间',
  FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE,
  FOREIGN KEY (owner_id) REFERENCES user(id)
) COMMENT = '线索表';

-- 商机表
CREATE TABLE opportunity (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '商机ID',
  tenant_id BIGINT NOT NULL COMMENT '租户ID',
  customer_id BIGINT NOT NULL COMMENT '客户ID',
  opportunity_name VARCHAR(100) NOT NULL COMMENT '商机名称',
  stage_id BIGINT COMMENT '销售阶段ID',
  amount DECIMAL(15,2) NOT NULL COMMENT '商机金额',
  probability INT DEFAULT 0 COMMENT '赢单概率(0-100)',
  expected_close_date DATE COMMENT '预计成交日期',
  owner_id BIGINT COMMENT '负责人ID',
  status TINYINT DEFAULT 1 COMMENT '状态(1:进行中,2:赢单,3:输单)',
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE,
  FOREIGN KEY (customer_id) REFERENCES customer(id) ON DELETE CASCADE,
  FOREIGN KEY (owner_id) REFERENCES user(id)
) COMMENT = '商机表';

-- 销售阶段表
CREATE TABLE sales_stage (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '阶段ID',
  tenant_id BIGINT NOT NULL COMMENT '租户ID',
  stage_name VARCHAR(50) NOT NULL COMMENT '阶段名称',
  sort_order INT DEFAULT 0 COMMENT '排序',
  probability INT DEFAULT 0 COMMENT '赢单概率(0-100)',
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE
) COMMENT = '销售阶段表';

-- 订单表
CREATE TABLE order (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '订单ID',
  tenant_id BIGINT NOT NULL COMMENT '租户ID',
  customer_id BIGINT NOT NULL COMMENT '客户ID',
  order_no VARCHAR(50) NOT NULL COMMENT '订单编号',
  total_amount DECIMAL(15,2) NOT NULL COMMENT '订单总金额',
  actual_amount DECIMAL(15,2) COMMENT '实际支付金额',
  order_date DATE COMMENT '订单日期',
  payment_date DATE COMMENT '支付日期',
  status TINYINT DEFAULT 1 COMMENT '状态(1:待付款,2:已付款,3:已发货,4:已完成,5:已取消)',
  created_by BIGINT COMMENT '创建人ID',
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE,
  FOREIGN KEY (customer_id) REFERENCES customer(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES user(id),
  UNIQUE KEY uk_order_no (tenant_id, order_no)
) COMMENT = '订单表';

-- 订单明细表
CREATE TABLE order_item (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '订单明细ID',
  tenant_id BIGINT NOT NULL COMMENT '租户ID',
  order_id BIGINT NOT NULL COMMENT '订单ID',
  product_id BIGINT NOT NULL COMMENT '产品ID',
  product_name VARCHAR(100) COMMENT '产品名称',
  quantity INT NOT NULL COMMENT '数量',
  unit_price DECIMAL(15,2) NOT NULL COMMENT '单价',
  total_price DECIMAL(15,2) NOT NULL COMMENT '总价',
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE,
  FOREIGN KEY (order_id) REFERENCES order(id) ON DELETE CASCADE
) COMMENT = '订单明细表';

-- 合同表
CREATE TABLE contract (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '合同ID',
  tenant_id BIGINT NOT NULL COMMENT '租户ID',
  customer_id BIGINT NOT NULL COMMENT '客户ID',
  contract_no VARCHAR(50) NOT NULL COMMENT '合同编号',
  contract_name VARCHAR(100) NOT NULL COMMENT '合同名称',
  amount DECIMAL(15,2) NOT NULL COMMENT '合同金额',
  signing_date DATE COMMENT '签订日期',
  start_date DATE COMMENT '开始日期',
  end_date DATE COMMENT '结束日期',
  status TINYINT DEFAULT 1 COMMENT '状态(1:草稿,2:已签订,3:已过期,4:已终止)',
  created_by BIGINT COMMENT '创建人ID',
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE,
  FOREIGN KEY (customer_id) REFERENCES customer(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES user(id),
  UNIQUE KEY uk_contract_no (tenant_id, contract_no)
) COMMENT = '合同表';

5. 跟进管理模块

-- 跟进记录表
CREATE TABLE follow_up (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '跟进记录ID',
  tenant_id BIGINT NOT NULL COMMENT '租户ID',
  business_type TINYINT NOT NULL COMMENT '业务类型(1:客户,2:商机,3:线索)',
  business_id BIGINT NOT NULL COMMENT '业务ID',
  content TEXT NOT NULL COMMENT '跟进内容',
  follow_type TINYINT COMMENT '跟进方式(1:电话,2:邮件,3:拜访,4:会议)',
  created_by BIGINT NOT NULL COMMENT '跟进人ID',
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '跟进时间',
  next_follow_date DATE COMMENT '下次跟进时间',
  attachment_url VARCHAR(255) COMMENT '附件URL',
  FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES user(id)
) COMMENT = '跟进记录表';

-- 任务表
CREATE TABLE task (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '任务ID',
  tenant_id BIGINT NOT NULL COMMENT '租户ID',
  title VARCHAR(100) NOT NULL COMMENT '任务标题',
  description TEXT COMMENT '任务描述',
  assign_to BIGINT COMMENT '执行人ID',
  created_by BIGINT COMMENT '创建人ID',
  start_time DATETIME COMMENT '开始时间',
  end_time DATETIME COMMENT '结束时间',
  priority TINYINT DEFAULT 3 COMMENT '优先级(1-5)',
  status TINYINT DEFAULT 1 COMMENT '状态(1:未开始,2:进行中,3:已完成,4:已取消)',
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE,
  FOREIGN KEY (assign_to) REFERENCES user(id),
  FOREIGN KEY (created_by) REFERENCES user(id)
) COMMENT = '任务表';

6. 产品管理模块

-- 产品表
CREATE TABLE product (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '产品ID',
  tenant_id BIGINT NOT NULL COMMENT '租户ID',
  product_name VARCHAR(100) NOT NULL COMMENT '产品名称',
  product_code VARCHAR(50) COMMENT '产品编码',
  category_id BIGINT COMMENT '分类ID',
  price DECIMAL(15,2) COMMENT '价格',
  cost DECIMAL(15,2) COMMENT '成本',
  description TEXT COMMENT '产品描述',
  status TINYINT DEFAULT 1 COMMENT '状态(0:下架,1:上架)',
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE
) COMMENT = '产品表';

-- 产品分类表
CREATE TABLE product_category (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '分类ID',
  tenant_id BIGINT NOT NULL COMMENT '租户ID',
  category_name VARCHAR(50) NOT NULL COMMENT '分类名称',
  parent_id BIGINT DEFAULT 0 COMMENT '父分类ID',
  sort_order INT DEFAULT 0 COMMENT '排序',
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE
) COMMENT = '产品分类表';

7. 系统设置模块

-- 数据字典表
CREATE TABLE dictionary (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '字典ID',
  dict_type VARCHAR(50) NOT NULL COMMENT '字典类型',
  dict_code VARCHAR(50) NOT NULL COMMENT '字典编码',
  dict_name VARCHAR(100) NOT NULL COMMENT '字典名称',
  sort_order INT DEFAULT 0 COMMENT '排序',
  is_system TINYINT DEFAULT 0 COMMENT '是否系统预置(0:否,1:是)',
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  UNIQUE KEY uk_dict_type_code (dict_type, dict_code)
) COMMENT = '数据字典表';

-- 操作日志表
CREATE TABLE operation_log (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '日志ID',
  tenant_id BIGINT COMMENT '租户ID',
  user_id BIGINT COMMENT '操作用户ID',
  operation_type TINYINT COMMENT '操作类型(1:新增,2:修改,3:删除,4:查询)',
  module_name VARCHAR(50) COMMENT '模块名称',
  business_id BIGINT COMMENT '业务ID',
  content TEXT COMMENT '操作内容',
  ip_address VARCHAR(50) COMMENT 'IP地址',
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
  FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES user(id)
) COMMENT = '操作日志表';

四、多租户核心实现策略

1. 租户识别与上下文管理

// 租户上下文管理器
public class TenantContextHolder {
    private static final ThreadLocal<Long> CONTEXT = new ThreadLocal<>();
    
    public static void setTenantId(Long tenantId) {
        CONTEXT.set(tenantId);
    }
    
    public static Long getTenantId() {
        return CONTEXT.get();
    }
    
    public static void clear() {
        CONTEXT.remove();
    }
}

// 租户拦截器
public class TenantInterceptor implements HandlerInterceptor {
    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) {
        // 从请求头或其他方式获取租户ID
        String tenantIdStr = request.getHeader("X-Tenant-ID");
        if (tenantIdStr != null) {
            TenantContextHolder.setTenantId(Long.parseLong(tenantIdStr));
        }
        return true;
    }
    
    @Override
    public void afterCompletion(HttpServletRequest request, HttpServletResponse response, 
                              Object handler, Exception ex) {
        TenantContextHolder.clear();
    }
}

2. 数据访问增强

// 使用MyBatis-Plus的租户插件
@Configuration
public class MyBatisConfig {
    @Bean
    public TenantLineInnerInterceptor tenantLineInnerInterceptor() {
        return new TenantLineInnerInterceptor(new TenantLineHandler() {
            @Override
            public Expression getTenantId() {
                Long tenantId = TenantContextHolder.getTenantId();
                if (tenantId == null) {
                    // 可根据需要抛异常或返回默认值
                    return new LongValue(0);
                }
                return new LongValue(tenantId);
            }
            
            @Override
            public String getTenantIdColumn() {
                return "tenant_id";
            }
            
            @Override
            public boolean ignoreTable(String tableName) {
                // 忽略不需要租户隔离的表
                return "dictionary".equals(tableName);
            }
        });
    }
}

五、性能优化建议

1. 索引优化：为所有含tenant_id的查询条件创建组合索引
2. 分页查询：所有列表查询必须使用分页，避免全表扫描
3. 数据归档：对历史数据进行归档，减轻主表压力
4. 读写分离：对大型租户考虑读写分离策略
5. 缓存策略：使用Redis缓存热点数据，减轻数据库压力
6. 租户数据分片：当单库数据量过大时，考虑按租户进行数据分片

六、安全措施

1. 强制租户过滤：所有查询必须包含tenant_id条件，防止跨租户访问
2. 权限控制：实现细粒度的权限控制，确保用户只能访问授权数据
3. 数据脱敏：对敏感数据进行脱敏处理
4. 审计日志：记录所有关键操作，便于追溯
5. 参数校验：严格校验用户输入，防止SQL注入等攻击

七、扩展建议

1. 数据备份与恢复：实现按租户的数据备份与恢复功能
2. 租户数据统计：提供租户数据量、活跃度等统计功能
3. 多语言支持：为不同租户提供多语言界面
4. 自定义字段：允许租户自定义表字段，增强灵活性
5. API接口：提供标准API接口，便于与其他系统集成

通过以上设计，我们实现了一个功能完整、安全可靠的多租户CRM系统数据库表结构。该设计在保持低成本和易维护性的同时，也考虑了性能优化和数据安全问题，适合大多数中小型SaaS CRM系统的需求。