150实战案例：业务系统表结构与字段设计全解析

这次我们不看算法也不聊模型而是把一份编号为150的实战案例单独拆开专门讲它的表结构和业务说明。很多开发者在拿到一套开源项目或者内部交接代码时第一件事不是跑通接口而是先打开数据库脚本看表建得是否合理、字段含义是否清楚、业务状态怎么流转。表结构设计好了后面的权限、订单、统计、报表都会很顺设计不好后面每一步都在还债。这篇文章会把“150 实战案例”的表结构设计思路、业务字段说明、典型建表 SQL、常用工具操作和排查方法完整过一遍。文章里出现的表结构属于通用业务建模示例你可以直接套用到自己的工作项目里也可以当作一次真实系统表结构设计的评审练习。如果你正在做数据库设计、后端开发或者准备接手一个业务系统这篇内容建议收藏。1. 实战案例核心能力速览先说清楚这个案例能带给你什么。它不是某个开源框架的源码分析而是一整套以业务为中心的数据库设计实践适合用来对照自己的系统做优化。能力项说明案例主题业务系统表结构设计与业务说明案例编号150实战案例模块适用位置用户权限、商品管理、订单流转、报表统计技术栈参考Spring Boot MyBatis/JPA MySQL核心交付物表结构 DDL、字段业务说明、状态流转设计常用工具Datagrip、Navicat、SAP GUI、JimuReport是否支持批量可通过 SQL 脚本批量初始化支持批量数据导入是否提供 API表结构本身不涉及 API需由后端服务封装推荐环境MySQL 5.7 / 8.08G 内存左右即可需要准备数据库客户端、JDK、对应持久层框架从这张表可以看出这个案例的核心价值在“表结构”本身而不是代码。读完之后你应该能回答三个问题这张表为什么这么建每个字段在业务里是什么意思状态字段怎么流转才不会乱。2. 适用场景与使用边界2.1 适合谁后端开发需要在项目中设计用户、订单、商品等核心表结构可以参考这里的字段拆分和索引设计。运维/DBA需要快速理解业务库表含义做数据字典整理、慢查询优化。测试开发需要构造测试数据理解订单状态机验证业务流程。数据分析师需要了解业务表结构写统计 SQL做报表。2.2 解决什么问题一份清晰的表结构至少能解决四类问题交接问题项目换手时新人能通过表结构说明快速了解业务。数据一致性问题明确的字段枚举和状态流转避免乱写状态值。查询效率问题合理的索引设计让订单查询、用户查询不拖慢接口。统计口径问题业务说明中定义好“有效订单”“成交金额”等口径报表才不会被质疑。2.3 不适合什么场景表结构设计适合中低频业务更新的系统。如果业务规则每天都在变字段频繁增删那还不如采用宽表加 JSON 扩展字段的方式。另外如果系统需要处理千亿级数据这套单库单表的设计就不够用了需要引入分库分表或分布式数据库方案。2.4 数据与合规边界涉及用户手机号、地址、身份证等敏感字段必须做加密存储不能把明文直接落库。涉及订单金额、折扣、税费字段要统一使用十进制类型避免浮点误差。在任何开发、测试、演示场景中都要使用脱敏数据或虚拟数据不要在个人博客或教程中出现真实用户资料。如果你把表结构复制到自己的项目中也要先评估业务授权和数据合规要求。3. 环境准备与前置条件这个案例不依赖复杂的 AI 环境只需要一套可以运行 MySQL 的环境和数据库客户端。下面是一份通用准备清单。3.1 基础软件软件版本建议用途操作系统Windows 10/11、macOS、Linux 均可运行数据库和客户端MySQL5.7 或 8.0业务数据存储JDK如果后续接 Spring Boot建议 JDK 8 或 17业务服务运行数据库客户端Datagrip / Navicat / MySQL Workbench执行 SQL、查看表结构可选JimuReport 集成包报表展示3.2 检查清单MySQL 服务是否已启动。是否已创建独立数据库例如business_case_150。数据库账号是否有建表、导数据、查表结构的权限。客户端是否能连接 3306 端口如果修改过端口记得替换。如果使用 Datagrip建议安装对应数据库驱动。这些前置条件很简单主要目的是避免在后续执行建表语句时出现权限或者连接问题。4. 表结构设计实战业务说明与 DDL 示例这一部分是全文重点。下面以一套常见的电商业务系统为例逐步拆解核心表结构。这套结构包括了用户、角色、商品、订单等基础表适合作为实战案例的参考。4.1 核心表清单表名业务说明sys_user系统用户表存储账号、密码、状态sys_role角色表定义权限角色sys_user_role用户角色关联表多对多biz_product商品表存储商品基础信息biz_order订单主表一笔订单一条记录biz_order_item订单明细表一个订单多个商品设计时把“系统管理”和“业务数据”分成不同前缀这样一眼就能区分表的功能域。4.2 用户表sys_user用户表是几乎所有系统的起点。实际设计时需要明确一个用户属于哪种类型是平台管理员还是普通用户。字段设计可以参考下面这个 DDL。CREATE TABLE sys_user ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 主键ID, username VARCHAR(64) NOT NULL COMMENT 登录用户名, password VARCHAR(128) NOT NULL COMMENT 登录密码密文存储, nickname VARCHAR(64) DEFAULT NULL COMMENT 用户昵称, phone VARCHAR(20) DEFAULT NULL COMMENT 手机号需加密, email VARCHAR(128) DEFAULT NULL COMMENT 邮箱, status TINYINT NOT NULL DEFAULT 1 COMMENT 用户状态1启用 0禁用, user_type TINYINT NOT NULL DEFAULT 2 COMMENT 用户类型1管理员 2普通用户, last_login_time DATETIME DEFAULT NULL COMMENT 最后登录时间, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_username (username), KEY idx_phone (phone) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT系统用户表;业务说明password字段必须存加密后的密文不能存明文。status字段用数字枚举1启用0禁用。user_type用来区分管理员和普通用户权限判断时先查这个字段再查角色表。create_time和update_time由数据库自动维护避免代码里手动赋值产生不一致。4.3 角色与权限关联角色表本身不复杂关键是和用户表、菜单/权限表的关联方式。CREATE TABLE sys_role ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 角色ID, role_code VARCHAR(64) NOT NULL COMMENT 角色编码, role_name VARCHAR(64) NOT NULL COMMENT 角色名称, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1启用 0禁用, remark VARCHAR(255) DEFAULT NULL COMMENT 备注, PRIMARY KEY (id), UNIQUE KEY uk_role_code (role_code) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT角色表; CREATE TABLE sys_user_role ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 主键ID, user_id BIGINT NOT NULL COMMENT 用户ID, role_id BIGINT NOT NULL COMMENT 角色ID, PRIMARY KEY (id), UNIQUE KEY uk_user_role (user_id, role_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户角色关联表;业务说明role_code是角色编码比如ADMIN、USER代码里判断建议用编码不要用主键 ID因为主键在迁移环境时会变化。sys_user_role使用联合唯一键防止同一条用户角色关系重复插入。如果系统还有菜单权限还需要增加sys_menu和sys_role_menu两张表这里先不展开。4.4 商品表biz_product商品表需要保存价格、库存、上下架状态等信息。价格字段必须使用DECIMAL不能使用FLOAT或DOUBLE避免金额精度丢失。CREATE TABLE biz_product ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 商品ID, product_no VARCHAR(64) NOT NULL COMMENT 商品编码, product_name VARCHAR(128) NOT NULL COMMENT 商品名称, category_id BIGINT DEFAULT NULL COMMENT 分类ID, price DECIMAL(10,2) NOT NULL COMMENT 销售价格, cost_price DECIMAL(10,2) DEFAULT NULL COMMENT 成本价格, stock INT NOT NULL DEFAULT 0 COMMENT 库存数量, sales INT NOT NULL DEFAULT 0 COMMENT 销量, status TINYINT NOT NULL DEFAULT 1 COMMENT 商品状态1上架 2下架 3删除, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_product_no (product_no), KEY idx_category (category_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT商品表;业务说明product_no是业务编号通常会生成如P20250101001这样的编号数据库里给唯一索引防止重复。sales字段可以理解为冗余字段避免每次统计都去订单表聚合。status3表示逻辑删除。实际删除会影响订单历史关联所以用状态标记。4.5 订单主表biz_order订单表是业务中状态变化最复杂的表。设计订单表时要把订单基础信息、金额信息、收货信息、状态信息分清楚。CREATE TABLE biz_order ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 订单ID, order_no VARCHAR(64) NOT NULL COMMENT 订单编号, user_id BIGINT NOT NULL COMMENT 下单用户ID, total_amount DECIMAL(12,2) NOT NULL COMMENT 订单总金额, discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 优惠金额, pay_amount DECIMAL(12,2) NOT NULL COMMENT 实付金额, pay_status TINYINT NOT NULL DEFAULT 0 COMMENT 支付状态0待支付 1已支付 2已退款 3支付失败, order_status TINYINT NOT NULL DEFAULT 0 COMMENT 订单状态0待付款 1待发货 2待收货 3已完成 4已取消, receiver_name VARCHAR(64) NOT NULL COMMENT 收货人姓名, receiver_phone VARCHAR(20) NOT NULL COMMENT 收货人电话, receiver_address VARCHAR(255) NOT NULL COMMENT 收货地址, remark VARCHAR(255) DEFAULT NULL COMMENT 订单备注, pay_time DATETIME DEFAULT NULL COMMENT 支付时间, delivery_time DATETIME DEFAULT NULL COMMENT 发货时间, complete_time DATETIME DEFAULT NULL COMMENT 完成时间, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 下单时间, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id), KEY idx_order_status (order_status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单主表;业务说明order_no必须唯一通常是雪花 ID 或者日期加随机数生成。order_status和pay_status分开因为支付状态不完全等于订单状态。比如货到付款场景下订单可能是待发货但支付状态是待支付。pay_amount是用户真正支付的金额计算公式是total_amount - discount_amount。多个金额字段的存在是为了后续对账不要在代码里临时计算。4.6 订单明细表biz_order_item一个订单对应多个商品明细表记录每一件商品的快照信息包括下单时的商品名称、价格、数量。CREATE TABLE biz_order_item ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 明细ID, order_id BIGINT NOT NULL COMMENT 订单主表ID, product_id BIGINT NOT NULL COMMENT 商品ID, product_name VARCHAR(128) NOT NULL COMMENT 商品名称快照, product_price DECIMAL(10,2) NOT NULL COMMENT 商品单价快照, quantity INT NOT NULL COMMENT 购买数量, sub_total DECIMAL(12,2) NOT NULL COMMENT 小计金额, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), KEY idx_order_id (order_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单明细表;业务说明product_name和product_price叫“快照字段”。商品信息后续可能会改但订单里的历史商品名称和价格不能跟着变所以必须冗余存储。小计金额sub_total product_price * quantity建议在插入数据时计算好避免后续统计时重新计算。4.7 订单状态流转设计业务说明中最重要的部分就是状态机。建议画一张状态流转图并用注释写清楚0 待付款用户下单成功创建订单此时订单处于待付款状态。1 待发货用户支付成功支付状态变更为1 已支付订单状态变更为1 待发货。2 待收货商家发货写入delivery_time订单状态变更为2 待收货。3 已完成用户确认收货写入complete_time订单状态变更为3 已完成。4 已取消用户支付前取消订单或超时自动取消订单状态变更为4 已取消。状态变更时必须校验前置状态例如从0 待付款跳转到2 待收货是不合法操作。代码里可以用状态机框架也可以写简单的 if 判断但一定要在更新 SQL 里带上原状态作为条件UPDATE biz_order SET order_status 2, delivery_time NOW() WHERE id #{orderId} AND order_status 1;这条 SQL 的关键是AND order_status 1。如果影响行数为 0说明当前订单状态不是待发货不能执行发货操作这样可以防止并发状态下重复发货。5. 使用 Datagrip 同步数据库表结构拿到一份表结构 DDL 后通常需要把表同步到本地数据库或者生成实体类。Datagrip 是 JetBrains 出品的数据库客户端在表结构同步方面很顺手。下面给出常用操作思路。5.1 连接数据库打开 Datagrip新建数据源选择 MySQL填写主机、端口、用户名、密码和数据库名称。测试连接通过后在左侧数据库树中就能看到表。5.2 同步表结构到本地 SQL如果想把远程表结构同步成一份 SQL 文件可以右键点击目标表选择 “SQL Scripts” 下的 “Generate DDL to Clipboard” 或类似选项。这样得到的是当前表的标准 DDL可以用来版本管理或者导入到另一套环境。5.3 对比两个数据库的表结构Datagrip 支持数据库结构对比。打开两个数据源右键选择 “Compare With” 或使用 Tools 菜单中的 “Compare Database”选择要对比的表可以快速看出哪些表新增了字段、哪些字段类型变了。这个功能在版本升级和环境迁移时非常有用。5.4 根据表结构生成实体类Datagrip 本身不直接生成 Java 实体类但可以通过代码生成插件或者 MyBatis Generator 反向生成。通用流程是先用 Datagrip 导出表结构再通过 MyBatis Generator 配置数据库连接和生成目录生成 Entity、Mapper、XML。如果项目使用的是 JPA也可以使用 IDEA 自带的 Persistence 工具反向生成实体类。6. 报表场景JimuReport 表结构集成说明实战案例落地后报表是一个绕不开的环节。JimuReport 是一个开源的报表工具提供基于 Spring Boot 的 starter 集成方式例如jimureport-spring-boot-starter。如果项目里要接入报表模块需要了解它依赖的表结构和业务表如何关联。6.1 JimuReport 内置表结构JimuReport 启动后会创建一批自己的内置表比如报表定义表、数据源配置表、报表授权表等。正常情况下业务方不需要关心这些表的内部实现只需要在启动时让系统自动完成初始化。以 v2.3.4 为例官方文档会说明依赖的数据库版本和初始化方式实际配置时要先确认当前项目使用的 MySQL 版本是否兼容。6.2 业务表接入报表配置在 JimuReport 中做报表时通常要新增一个数据源然后写 SQL 查询业务表。例如统计每天的订单量SELECT DATE(create_time) AS order_date, COUNT(*) AS order_count, SUM(pay_amount) AS pay_amount FROM biz_order WHERE pay_status 1 GROUP BY DATE(create_time) ORDER BY order_date;这段话在报表工具中可以作为数据集 SQL最终生成柱状图或折线图。关键点在于业务表和报表表通过 SQL 关联不需要额外修改业务表结构。7. 企业场景扩展SAP 结构表数据查看方法如果实战案例对接的是 SAP 等企业 ERP可能会遇到“怎么查看结构表数据”的问题。SAP 中结构Structure和透明表Transparent Table是两个不同概念。结构通常用于程序内数据组装不持久化数据透明表才对应数据库中的实际表。查看结构表数据时可以有两种思路查看数据结构定义在 SAP 数据字典事务代码通常可参考 SE11中输入结构名称可以查看字段名、字段类型、长度和组件说明。查看透明表内容如果数据实际存放在透明表中可以通过数据浏览器事务代码通常可参考 SE16输入表名查看表内数据。需要注意SAP 系统中表名一般是大写很多是Z开头表示自定义表。开发人员通过Z前缀可以快速区分标准表和自定义表。对于业务顾问查看结构表数据时要特别关注数据的业务含义不要直接修改表数据避免造成生产数据异常。8. 表结构设计常见问题与排查下面是开发中经常会遇到的问题建议对照排查。问题现象可能原因排查方式解决方案表建好后字段没注释建表时漏写 COMMENT执行SHOW FULL COLUMNS FROM 表名查看补全 DDL或新增注释金额查询出现小数点误差使用 FLOAT/DOUBLE 存储金额检查字段类型改为 DECIMAL(10,2)用户名唯一约束失效字段未加唯一索引查看索引列表添加 UNIQUE KEY订单状态并发更新混乱更新 SQL 未带原状态条件检查 Mapper 更新语句更新时加AND order_status ?连接数据库超时端口未开放或驱动版本不匹配测试连接、查看错误日志修改端口或更新驱动Datagrip 同步表结构失败当前账号权限不足检查用户权限授予 SELECT、CREATE、ALTER 权限JimuReport 初始化找不到表版本不兼容或未执行初始化脚本查看启动日志按照官方文档执行初始化 SQLSAP 中查询表无数据输入的是结构名而不是透明表名使用数据字典确认对象类型换成透明表名称再查询这些排查思路不依赖具体业务遇到同类问题可以直接套用。9. 最佳实践与使用建议9.1 建表规范先行团队内部需要统一建表规范。字段命名建议全部使用小写加下划线表名使用业务前缀。主键统一叫id创建时间和更新时间统一叫create_time、update_time。这样接手的开发人员不需要猜字段含义。9.2 数据字典要持续维护表结构不是建完就结束了。每次新增字段都要同步更新字段说明。建议在项目仓库中维护一份database/README.md记录每张表的业务说明、字段解释、枚举值和状态流转规则。150 这类的实战案例如果只给 DDL 不给业务说明价值会大打折扣。9.3 用版本管理数据库脚本不要直接在正式库手工执行 DDL。每次表结构变更都要写增量脚本比如V1.0.1__add_order_pay_type.sql放到项目数据库脚本目录下由 Flyway 或 Liquibase 统一管理。这样从开发到测试再到生产表结构变更可控、可回滚、可追溯。9.4 敏感字段加密与访问控制用户手机号、邮箱、身份证件号等属于敏感数据数据库表中建议只保存密文或脱敏后的值。如果必须保存原文需要评估合规风险并限制数据库账号的权限范围。接口输出时也要做脱敏处理。9.5 批量任务要留审计字段如果系统需要批量导入、批量更新数据建议在相关表上增加batch_no、create_by、source_type等字段。当数据出现异常时可以通过批次号快速定位问题来源。9.6 索引不是越多越好单表索引过多会降低写入性能。平时查询条件里经常出现的字段才需要加索引。像订单表的order_status如果只有几个枚举值选择性不高单独加索引收益有限需要结合业务实际查询场景决定是否保留。10. 总结与下一步这次把 150 实战案例的表结构和业务说明拆了一遍核心要抓住三件事第一每张表都要有清晰的业务定位用户、商品、订单、明细各司其职第二状态字段必须配合状态流转规则一起设计更新时用条件防并发第三表结构不是一次性工作需要结合 Datagrip、报表工具和版本管理持续维护。最容易踩的坑有两个一个是金额字段用了浮点类型另一个是订单状态更新没有前置状态判断。这两个问题在业务上线后都很难修建议在建表阶段就避掉。如果你准备在自己项目里落地这套表结构先从sys_user和biz_order两张表开始跑通用户下单、支付、发货、收货的完整链路。等到表结构稳定后再接入 JimuReport 做订单报表或者把 Datagrip 的表结构同步和版本管理规范整理成团队的数据库开发规范。后续还可以继续扩展权限表、菜单表、库存流水表把业务体系补完整。