多租户下的系统基础表设计

多租户下的系统基础表设计在设计多租户进销存系统SaaS时核心是 租户隔离 权限控制 组织结构。一般推荐的设计是 “租户 → 机构 → 角色 → 用户” 的层级结构同时所有业务数据都带 tenant_id。租户表Tenantsys_tenant ------ id bigint PK tenant_code varchar(50) unique -- 租户编码 tenant_name varchar(200) -- 租户名称 contact_name varchar(100) -- 联系人姓名 contact_phone varchar(50) -- 联系人电话 contact_email varchar(100) -- 联系人邮箱 expire_time datetime -- 过期时间 status int -- 状态, 1启用, 0禁用 remark varchar(255) -- 备注 created_at datetime -- 创建时间 updated_at datetime -- 更新时间说明一个租户 一个企业所有业务表都要带 tenant_id 字段用来标识当前数据所属的租户业务表统一规范idtenant_idcreated_bycreated_atupdated_byupdated_atis_deletedorg_idstatus 字段用来标识当前数据是否有效。系统表通常只需要表达 是否可用状态很少变化。这些对象只有两件事是否可用是否禁用。用户有时需要 锁定状态如密码输错次数过多。因此状态设计0禁用1正常2: 锁定业务表的 status 设计业务单据通常有 生命周期。 例如订单草稿 → 提交 → 审核 → 完成 → 作废如果用一个简单 status0123别人几个月后根本看不懂。 所以业务表推荐用 业务状态枚举。 例如订单DRAFT SUBMITTED APPROVED FINISHED CANCELLED示例status含义DRAFT草稿SUBMITTED已提交APPROVED已审核FINISHED完成CANCELLED作废优点可读性强调试方便API清晰成熟系统一般这样设计status 业务状态 is_deleted 逻辑删除因此 系统表 vs 业务表总结类型status设计系统表0禁用 1启用用户表0禁用 1正常 2锁定业务表业务枚举字符串机构表Organizationsys_organization ------------- id bigint PK tenant_id bigint org_code varchar(50) unique -- 机构编码 org_name varchar(200) -- 机构名称 pid bigint -- 父节点 path varchar(500) -- 层级路径 org_type varchar(50) -- 机构类型如 company/department/store sort varchar(50) -- 排序 status int -- 状态, 1启用, 0禁用 remark varchar(200) -- 备注 is_deleted int -- 逻辑删除, 1删除, 0未删除 created_at datetime updated_at datetime说明tenant └── 总公司 ├── 财务部 ├── 销售部 └── 门店A机构表中org_type 字段用来标识当前机构的类型如company/department/store机构表中pid 字段用来标识当前机构的父节点path 字段用来标识当前机构的层级路径path 字段用来标识当前机构的层级路径。idpath1121/231/341/2/451/2/561/3/6如果用户机构org_id 2, 查询SELECT id FROM sys_organization WHERE path LIKE 1/2/% OR id 2;优点查询非常快, SQL简单缺点移动机构需要更新 pathERP 中 机构移动很少所以这是一个很好的方案。用户表Usersys_user ----- id bigint PK tenant_id bigint username varchar(100) -- 用户名 password varchar(255) -- 密码哈希 salt varchar(50) -- 密码盐 real_name varchar(100) -- 真实姓名 nickname varchar(100) -- 昵称 gender varchar(10) -- 性别 avatar varchar(200) -- 头像 mobile varchar(50) -- 手机号 email varchar(100) -- 邮箱 org_id bigint -- 机构ID position_id bigint -- 岗位ID login_count int -- 登录次数 last_login_time datetime -- 最后登录时间 last_login_ip varchar(50) -- 最后登录IP is_super int -- 是否超级管理员, 1超级管理员, 0普通用户 is_deleted int -- 逻辑删除, 1删除, 0未删除 status int -- 状态, 1启用, 0禁用 remark varchar(200) -- 备注 created_at datetime -- 创建时间 updated_at datetime -- 更新时间说明用户表中tenant_id 字段用来标识当前用户所属的租户org_id 字段用来标识当前用户所属的机构is_super 表示系统超级管理员不受任何权限控制if user.is_super: 允许所有操作 else: 按 RBAC 权限判断避免误操作:如果超级管理员只是角色, 管理员可能在 UI 中误删结果系统没有管理员这个字段通常不允许 UI 修改,只能数据库修改,安全性更高。is_super 的作用1️⃣ 绕过权限系统2️⃣ 防止系统锁死3️⃣ 提高权限判断性能4️⃣ 防止误删管理员角色5️⃣ 系统逃生通道用户有时需要 锁定状态如密码输错次数过多。因此状态设计0禁用1正常2: 锁定角色表Role角色是租户级的。sys_role ----- id bigint PK tenant_id bigint role_code varchar(50) unique -- 角色编码 role_name varchar(200) -- 角色名称 role_type varchar(50) -- 角色类型 data_scope varchar(50) -- 数据权限 sort varchar(50) -- 排序 status int -- 状态, 1启用, 0禁用 is_deleted int -- 逻辑删除, 1删除, 0未删除 created_at datetime -- 创建时间 updated_at datetime -- 更新时间常见角色管理员采购销售仓库财务角色通常需要data_scope例如ALL 全部数据ORG 本机构ORG_CHILD 本机构及下级SELF 仅自己CUSTOM 指定机构SQL示例1、data_scope ALL 时WHERE tenant_id ?2、data_scope ORG 时WHERE tenant_id ? AND org_id current_org3、data_scope ORG_CHILD 时WHERE tenant_id ? AND org_id IN (子机构列表)4、data_scope SELF 时WHERE tenant_id ? AND created_by current_user5、data_scope CUSTOM 时WHERE tenant_id ? AND org_id IN (role_org)ERP 实际 SQL 拼接SELECT * FROM sales_order WHERE tenant_id ? AND ( created_by :user_id OR org_id IN (:org_ids) )岗位表Positionsys_position ---------- id bigint PK tenant_id bigint position_code varchar(50) -- 岗位编码 position_name varchar(200) -- 岗位名称 org_id bigint -- 所属机构 status int -- 状态, 1启用, 0禁用 created_at datetime -- 创建时间 updated_at datetime -- 更新时间角色主要解决 权限问题, 岗位主要解决 组织职责问题。岗位通常是 组织结构的一部分。岗位通常是“一人一岗”主岗位:在很多 ERP / OA / HR 系统里岗位通常设计为“一人一个主岗位”因此直接在用户表中放 position_id而不是做多对多。优点表结构简单查询快UI简单符合大多数企业组织结构有些企业确实存在 兼职岗位兼职职责用 角色 解决。权限表Permission权限通常是菜单 按钮。sys_permission ----------- id bigint PK system_code varchar(50) -- 系统类型 perm_code varchar(50) PK -- 权限编码 perm_name varchar(200) -- 权限名称 perm_type varchar(50) -- 权限类型如 menu/button/api pid bigint -- 父节点 path varchar(500) -- 层级路径 api_path varchar(200) -- API路径 scope varchar(50) -- 权限范围如SYSTEM/TENANT module_code varchar(50) -- 模块编码 resource_code varchar(50) -- 资源编码 action_code varchar(50) -- 操作编码 sort varchar(50) -- 排序 status int -- 状态, 1启用, 0禁用 created_at datetime -- 创建时间 updated_at datetime -- 更新时间说明 perm_type 枚举menu菜单button按钮apiAPI例如perm_codeperm_typeuser:add按钮user:delete按钮/api/user/listAPI这样可以控制前端菜单控制按钮控制接口权限scope 权限作用范围:SYSTEM系统级TENANT租户级例如perm_codescopetenant:createSYSTEMuser:addTENANT权限表通常 不带 tenant_idsys_permission 全局 sys_menu 全局权限只负责“动作”, 如下面示例查询可选新增编辑删除审核反审核导出打印作废关闭红冲过账module_code resource_code action_code这是 工业级权限编码拆分设计后期非常好用。例如module_coderesource_codeaction_codeperm_codesysuserviewsys:user:viewsysuseraddsys:user:addordersales_orderapproveorder:sales_order:approve比单纯 perm_code 更利于代码生成权限树归类批量授权模块迁移权限编码必须 统一规范 推荐模块:资源:操作例如user:list user:add user:update user:delete order:create order:approve order:cancel最终模型Menu (导航) Permission (功能) User └─ Role └─ Permission ├─ Menu ├─ Button └─ API核心思想菜单控制导航权限控制行为角色负责授权前端菜单生成逻辑:流程用户登录 ↓ 获取角色 ↓ 获取权限和菜单集合 ↓ 根据权限和菜单集合生成菜单树 ↓ 前端展示菜单树菜单表Menusys_menu ------ id bigint PK pid bigint -- 父节点 system_code varchar(50) -- 系统类型 perm_code varchar(50) -- 访问权限编码(可选) menu_code varchar(50) PK -- 菜单编码 menu_name varchar(200) -- 菜单名称 tag varchar(50) -- 标签 path varchar(200) -- 路由路径 redirect varchar(200) -- 重定向路径 is_iframe int -- 是否内嵌窗口1内嵌窗口, 0不内嵌窗口 out_link varchar(200) -- 外链地址 is_keep_alive int -- 是否缓存1缓存, 0不缓存 is_affix int -- 是否固定1固定, 0不固定 is_expand int -- 是否展开 url varchar(200) -- 界面Url地址 is_eav_menu int -- 是否EAV菜单 entity_type_id bigint -- 实体类型ID component varchar(200) -- 组件路径 icon varchar(50) -- 图标 sort varchar(50) -- 排序 status int -- 状态, 1启用, 0禁用 is_visible int -- 是否可见,1可见,0不可见 created_at datetime -- 创建时间 updated_at datetime -- 更新时间菜单和权限分离但菜单支持可选绑定访问权限。 菜单有一个可选的“访问权限编码”有则校验无则只要菜单分配了就能访问不是所有菜单页面都必须有 VIEW 权限。只给重要页面加例如财务报表价格策略供应商结算采购成本分析薪资核算系统类型表SystemTypesys_system_type ------------ id bigint PK system_code varchar(50) -- 系统类型编码 system_name varchar(200) -- 系统类型名称 remark varchar(200) -- 备注 status int -- 状态, 1启用, 0禁用 created_at datetime -- 创建时间系统类型表用于区分不同系统之间的资源如系统菜单、权限功能点等。用户角色表UserRolesys_user_role ---------- id bigint PK tenant_id bigint user_id bigint role_id bigint唯一约束UNIQUE (tenant_id, user_id, role_id)企业级系统中间表建议带 tenant_id避免跨租户脏数据查询更高效索引优化更直接tenant_id 可以冗余但利大于弊。角色机构表RoleOrg当使用 CUSTOM 时需要指定机构sys_role_org --------- id bigint PK tenant_id bigint role_id bigint org_id bigint唯一性约束:UNIQUE (tenant_id, role_id, org_id)角色权限表RolePermissionsys_role_permission ---------------- id bigint PK tenant_id bigint role_id bigint perm_id bigint唯一性约束UNIQUE (tenant_id, role_id, perm_id)租户菜单关系表TenantMenu表示某个租户可使用哪些菜单资源一级分配。然后该租户下的角色可以根据当前租户拥有的菜单权限来控制可见菜单二级分配。sys_tenant_menu - id bigint PK tenant_id bigint -- 租户ID menu_id bigint -- 菜单ID status int -- 状态, 1启用, 0禁用 created_at datetime -- 创建时间 updated_at datetime -- 更新时间作用, 比如A租户可用【采购、销售、库存】B租户可用【销售、财务】C租户可用【全部模块】这样你就实现了“不同租户看到不同菜单体系”。 唯一性约束UNIQUE (tenant_id, menu_id)角色菜单关系表RoleMenu在实际项目里尤其是 ERP / 后台管理系统如果把“菜单”和“权限点”完全绑定死往往会出现配置步骤变多维护成本变高很多纯展示菜单也要配权限显得很累赘前端菜单树和后端权限模型耦合过重因此菜单和权限分离建模但允许菜单可选绑定权限。菜单Menu解决“看不看得见、能不能导航到页面”权限Permission / Resource解决“能不能操作按钮/接口/业务动作”角色分配时可以直接分配 菜单也可以分配 权限菜单可选关联一个“访问权限”不是必须菜单直接给角色分配简单直观。特点角色拥有哪些菜单直接可见前端菜单树加载简单配置很直观优点实现简单运维/实施人员容易理解适合大多数后台系统缺点只能控制“能不能看到页面”按钮、接口、审核、反审核、导出等细粒度动作不好管最后还是要补权限表为什么这是最适合 ERP 的ERP 里通常有三层目录菜单比如“采购管理”页面菜单比如“采购订单”页面内动作新增、编辑、删除、审核、反审核、导出、打印、关闭、红冲……如果你把它们全都统一成权限会出现目录菜单也要定义权限菜单页也要定义 VIEW 权限配置会很重而 ERP 实际上最关键的是菜单页是否可见 → 用 role_menu页面里的动作是否可做 → 用 role_permission这样非常清晰。sys_role_menu ----------- id bigint PK tenant_id bigint role_id bigint menu_id bigint唯一性约束UNIQUE (tenant_id, role_id, menu_id)操作日志表OperationLogsys_operation_log -------------- id bigint PK tenant_id bigint user_id bigint module varchar(250) -- 模块 action varchar(50) -- 操作 content varchar(2000) -- 内容 ip varchar(50) -- IP地址 created_at datetime -- 创建时间登录日志表LoginLogsys_login_log ---------- id bigint PK tenant_id bigint user_id bigint content varchar(2000) -- 内容 ip varchar(50) -- IP地址 created_at字典类型表在多租户系统里字典类型Dictionary Type 和 字典项目Dictionary Item 是否租户隔离通常不能一刀切。字典类型大概率是“全局定义为主”而字典项目既可能全局共享也可能租户自定义。建议把字典分成 3类1 系统级字典全局共享所有租户都一样例如性别、星期、单据状态、启用状态2 租户级字典租户私有每个租户可以维护自己的字典项目、例如客户等级、供应商分类、仓库分区、付款方式、业务标签3 混合型字典系统默认 租户可扩展系统给默认项租户可以追加或覆盖例如结算方式、订单来源、业务分类如果你做的是 ERP 场景下面这些字典项几乎一定会不同客户供应商等级业务员分组仓库区域付款条件税率组有时甚至不同组织不同单据业务类型自定义标签物料属性分类费用类别结算方式运输方式哪些字典通常不会不同全局例如性别是否启用星期月份国家/省市如果你自己维护单据状态草稿、已审核、已关闭审核状态布尔型选项系统固定枚举这些更适合做 全局字典。sys_dict_type --------------- id bigint PK pid bigint type_code varchar(50) -- 类型编码 type_name varchar(200) -- 类型名称 dict_category varchar(20) -- SYSTEM / BUSINESS / CUSTOM -- 字典类别 scope_mode varchar(20) -- GLOBAL / TENANT / MIXED -- 作用范围 sort varchar(50) -- 排序 remark varchar(200) -- 备注 is_deleted int -- 逻辑删除, 1删除, 0未删除 is_system int -- 是否系统字典 status int -- 状态, 1启用, 0禁用 created_at datetime -- 创建时间scope_mode 这个字段非常重要可取值GLOBAL只允许全局字典项TENANT只允许租户私有字典项MIXED系统默认 租户可扩展字典项目表sys_dict_data --------------- id bigint PK type_id bigint -- 类型ID tenant_id bigint null -- null 全局项有值 租户项 item_code varchar(100) -- 项目编码 item_name varchar(200) -- 项目名称 item_value varchar(200) -- 项目值 remark varchar(200) -- 备注 is_default smallint default 0 -- 是否默认项 is_builtin smallint default 0 -- 系统内置项 ext_json text -- 扩展属性颜色、标签、额外配置 sort varchar(50) -- 排序 status int -- 状态, 1启用, 0禁用 is_deleted int -- 逻辑删除, 1删除, 0未删除 created_at datetime -- 创建时间tenant_id null 表示全局字典项例如 GENDER男女未知这些对所有租户都一样。tenant_id 某租户ID 表示租户私有字典项例如租户 A 的 CUSTOMER_LEVELVIP客户普通客户战略客户租户 B 的 CUSTOMER_LEVELA类客户B类客户C类客户1 GLOBAL 字典只查全局项select * from sys_dict_item where dict_type_id :dict_type_id and tenant_id is null and status 1 order by sort_no, id;2 TENANT 字典只查当前租户项select * from sys_dict_item where dict_type_id :dict_type_id and tenant_id :tenant_id and status 1 order by sort_no, id;3 MIXED 字典查全局和租户项 查询规则先查全局默认项再查当前租户扩展项如果允许“覆盖”则租户项优先例如PAYMENT_METHOD付款方式, 系统默认现金转账支票租户 A 追加月结30天月结60天租户 B 追加银承商承查询逻辑追加模式select * from sys_dict_item where dict_type_id :dict_type_id and status 1 and (tenant_id is null or tenant_id :tenant_id) order by sort_no, id;查询逻辑覆盖模式按 item_code 覆盖如果你希望租户能覆盖系统默认项比如系统有 BANK_TRANSFER租户也定义一个 BANK_TRANSFER名字改成“对公转账”那么可以按 item_code 做唯一语义。规则先加载全局项再加载租户项相同 item_code 的租户项覆盖全局项参数表Parameter参数表必须天然支持多租户。 参数表在多租户下强烈建议按“作用域Scope”设计。 也就是同一套参数定义参数值可以有不同层级系统级参数GLOBAL租户级参数TENANT组织级参数ORG可选用户级参数USER可选通常用于偏好设置参数“定义”和“值”分离,拆成两张表参数定义表 sys_param_def参数值表 sys_param_value参数定义表 sys_param_def, 定义“这个参数是什么、类型是什么、支持什么作用域、默认值是什么”sys_parameter_def ---------- id bigint PK param_code varchar(50) -- 参数编码 param_name varchar(50) -- 参数名称 group_name varchar(50) -- 分组名称, SYSTEM / INVENTORY / SALES / FINANCE ... param_type varchar(20) -- 参数类型: STRING / INT / DECIMAL / BOOL / JSON / DATE scope_mode varchar(20) -- GLOBAL / TENANT / MIXED -- 作用范围 is_required int -- 是否必填 1必填 0非必填 is_encrypted int -- 是否加密 1加密 0不加密 is_builtin int -- 是否系统内置 1系统内置 0非系统内置 remark varchar(200) -- 备注 is_deleted int -- 逻辑删除, 1删除, 0未删除 sort int -- 排序 status int -- 状态, 1启用, 0禁用 created_at datetime -- 创建时间 updated_at datetime -- 更新时间scope_mode 这个字段非常重要可取值GLOBAL只允许系统级TENANT每个租户独立ORG按组织/部门/账套维度ERP 常见USER用户偏好设置MIXED允许多级覆盖推荐重点参数值表是指“在某个作用域下这个参数的实际值是什么”参数值表设计sys_parameter ---------- id tenant_id bigint -- 租户ID param_def_id bigint -- 参数定义ID scope_level varchar(20) -- GLOBAL / TENANT / ORG / USER-- 作用范围 org_id bigint -- 组织ID user_id bigint -- 用户ID param_value varchar(4000) -- 参数值 value_source varchar(200) -- 参数值来源:MANUAL / DEFAULT / IMPORT / SYSTEM remark varchar(200) -- 备注 status int -- 状态, 1启用, 0禁用 created_at datetime -- 创建时间 updated_at datetime -- 更新时间优先级建议 USER ORG TENANT GLOBAL sys_param_def.default_value也就是说如果有用户级值用用户级没有则看组织级没有则看租户级没有则看全局级再没有则用参数定义里的默认值读取规则V1GLOBAL只查 tenant_id is nullTENANT只查 tenant_id current_tenant_id没有则用默认值MIXED优先租户值没有则全局值没有则默认值进销存业务表建议核心业务表product category warehouse inventory supplier customer purchase_order purchase_order_item sales_order sales_order_item stock_in stock_out所有表都带 tenant_id 字段用来标识当前数据所属的租户。 对于中间关联表如UserRole设计需要增加tenant_id字段。user_role ---------- id tenant_id user_id role_id查询需要根据租户过滤如SELECT * FROM user_role WHERE tenant_id ?避免跨租户脏数据,可以加唯一索引,逻辑更安全CREATE UNIQUE INDEX idx_user_role_tenant_id_user_id_role_id ON user_role (tenant_id, user_id, role_id);SaaS ORM自动过滤更容易:query.filter(Model.tenant_id current_tenant)删除租户数据更容易:DELETE FROM user_role WHERE tenant_id ?大多数企业系统 全部中间表都会带 tenant_id。例如user_rolerole_permissionuser_orgrole_orguser_position多租户系统设计原则 只要是业务表一律带 tenant_id 字段并且查询时需要根据租户过滤。 什么时候可以不加 tenant_id? 只有一种情况全局表,这种是 平台共享数据不属于某个租户吗如租户表字典表、参数表、系统配置表、菜单表等。。tenantdictionaryparameterpermissionmenucountrycurrency除了租户基础表外租户还需要包括租户套餐 租户套餐关联。租户套餐:tenant_package -------------- id package_name user_limit storage_limit price租户套餐关联:tenant_package_rel ------------------- tenant_id package_id start_time end_timeERP系统推荐ID方案大多数 ERP 系统推荐主键IDBIGINT 业务编码VARCHAR主键ID使用分布式IDSnowflake Leaf Sonyflake生成 64bit BIGINT178923741239123特点全局唯一有时间顺序仍然是 BIGINT因此使用BIGINT Snowflake 方案。结构id BIGINT PRIMARY KEY生成Snowflake ID优点分布式高性能顺序索引ERP数据库标准结构:典型表id BIGINT PRIMARY KEY tenant_id BIGINT created_at DATETIME updated_at DATETIME不要在id中把 GUID 存 VARCHAR。 例如550e8400-e29b-41d4-a716-446655440000什么时候用 GUID 1 微服务跨系统ID例如订单服务 支付服务 物流服务2 离线客户端例如移动端 离线同步3 数据合并例如多数据库合并