在做关于主要是记账功能（例如，库存系统、电商系统等）业务的设计时，常常困惑于数据库应该怎么设计，下面是我平常开发时总结的一些思考。

整个业务分为三种核心表：流程表、主数据表和业务流水表。

一、数据库表设计说明

（一）流程表（驱动源头）

一般一种业务事件可以看成是一个流程或一种单据，例如ERP系统中库存的出库、入库、转库单据，支付系统中的充值、消费、退款等流程。

这样的流程或者说单据的数据库表一般是一种主子表结构。

同时，单据一般是有状态流转的，可以加入工作流引擎，作为审批来流转，所以我称之为流程/单据。

各参与人都可以根据条件对状态进行更改，这样的更改同样可以衍生为另一种业务事件，或流程单据。

1.业务说明

例如，当业务流程状态变更时，会触发核心数据的更新和流水的记录。

（1）核心数据变更 (状态更新):

ERP入库： 入库单据审核/完成 -> 更新物料的当前库存数量。
电商充值： 充值订单支付成功 -> 更新用户的当前账户余额。

（2）业务流水记录 (明细/凭证):

ERP入库： 记录一条库存流水账（或出入库明细），包含物料、数量、时间、单据号、仓库等信息，用于追溯历史。

电商充值： 记录一条充值流水（或资金流水/账户明细），包含用户、金额、时间、订单号、操作类型（充值）、余额变化等信息，用于对账和查询。

（3）核心单据/订单 (业务驱动):

ERP： 入库单是驱动库存和流水变更的源头。
电商： 充值订单是驱动余额和流水变更的源头。

2.涉及的字段设计

（1）状态字段 (status_dict)

例如，订单有各种状态码，待支付、已支付、已取消等。随着订单整个生命周期的流转，这些状态会随之发生改变。

除了当前状态 (status_dict)，可以单独设计一个 status_change_log 表（或作为单据主表/子表的一部分），记录状态变更的完整历史（原状态、新状态、变更时间、操作人、变更原因）。这对审计、排查问题、理解业务流转至关重要，比 operation_log 更聚焦于状态本身。

（2）流程类型(enum_type)

订单的类型有充值、消费、退款等类型，每一种流程的类型可以单独设计一个数据库表或有相应的子表。

根据每种类型插入到记录表中，每种类型都会不同程度的影响用户的账户余额，也影响公司的收入。

（3）单据编号生成

明确单据号的生成策略（如：前缀+日期+序列号）。这通常由数据库序列、Redis、雪花算法等实现，确保唯一性、有序性和可读性。

（4）版本控制/乐观锁

对于核心单据（尤其是状态流转的），强烈建议添加 version 字段（或 update_time 作为乐观锁依据），防止并发修改导致状态覆盖或数据不一致。例如，更新状态时检查 version 是否匹配。

（5）关联流程溯源

如果流程之间有关联（如退款单关联原充值单，调拨单关联源单和目标单），设计显式的关联字段（如 ref_order_no， source_voucher_id， target_voucher_id）。

（6）其它关联字段

其它相关联的商品表、物料表、支付方式表，称之为基础信息表，例如支付方式表可以记录支持的支付渠道（微信、支付宝、银行卡等），这些信息可以插入到记录表中存储起来方便后续对账。

3.举例说明

（1）建表示例

-- 流程表（充值订单示例）
CREATE TABLE recharge_order (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    order_no VARCHAR(32) NOT NULL UNIQUE COMMENT '订单号(前缀+日期+序列)',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    amount DECIMAL(15,2) NOT NULL COMMENT '充值金额',
    status TINYINT NOT NULL DEFAULT 0 COMMENT '状态:0-待支付,1-成功,2-失败,3-关闭',
    account_type TINYINT NOT NULL COMMENT '账户类型:1-现金,2-积分',
    payment_channel VARCHAR(20) COMMENT '支付渠道',
    version INT NOT NULL DEFAULT 0 COMMENT '版本号(乐观锁)',
    ref_order_no VARCHAR(32) COMMENT '关联原订单号',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT '软删除标记'
) COMMENT '充值订单表';

-- 状态变更日志表
CREATE TABLE order_status_log (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(32) NOT NULL COMMENT '关联订单号',
    old_status TINYINT NOT NULL COMMENT '原状态',
    new_status TINYINT NOT NULL COMMENT '新状态',
    operator_id BIGINT NOT NULL COMMENT '操作人',
    change_reason VARCHAR(200) COMMENT '变更原因',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_order_no(order_no)
) COMMENT '订单状态变更日志';

(2)代码示例

订单状态变更（含乐观锁和状态日志）:

public void updateOrderStatus(String orderNo, int newStatus, Long operatorId, String reason) {
    // 获取订单当前状态
    RechargeOrder order = orderDao.selectByOrderNo(orderNo);
    
    // 状态校验
    if (order.getStatus() == newStatus) return;
    
    // 记录状态变更日志
    OrderStatusLog log = new OrderStatusLog();
    log.setOrderNo(orderNo);
    log.setOldStatus(order.getStatus());
    log.setNewStatus(newStatus);
    log.setOperatorId(operatorId);
    log.setChangeReason(reason);
    statusLogDao.insert(log);
    
    // 更新订单状态（带乐观锁）
    int rows = orderDao.updateStatus(orderNo, newStatus, order.getVersion());
    if (rows == 0) {
        throw new OptimisticLockException("订单状态更新冲突");
    }
}

状态机管理（使用枚举）:

public enum OrderStatus {
    PENDING(0, "待支付"),
    SUCCESS(1, "支付成功"),
    FAILED(2, "支付失败"),
    CLOSED(3, "订单关闭");
    
    private final int code;
    private final String desc;
    
    // 构造函数、getters
    
    public static boolean isValidTransition(int oldStatus, int newStatus) {
        // 实现状态流转规则
        if (oldStatus == PENDING.code) {
            return newStatus == SUCCESS.code || newStatus == FAILED.code;
        }
        return false;
    }
}

（二）主数据表（基础/状态）

主数据表是业务运行的基础，为单据、流水提供上下文信息。

对于支付系统来说，用户/会员表是存储用户的基本信息（ID、姓名、手机号等），是余额和流水归属的主体。

对于ERP系统来说，仓库与物料的组合是存储各物料系统现存量归属的主体。

1.涉及的字段设计

（1）多账户类型

当然，用户的账户也有可能是多个，例如账户类型表里定义了不同的账户（如：现金余额、积分、优惠券账户等），余额表可能与之关联。一次充值流程，可能会更新多个账户。

（2）余额计算与冻结

对于涉及资金或高价值物品的账户，考虑 可用余额 (available_balance) 和 冻结余额 (frozen_balance) 的概念。

例如，用户发起提现时，先将提现金额从可用余额转入冻结余额，提现成功后再扣减冻结余额；提现失败则解冻。

 total_balance = available_balance + frozen_balance

（3）主数据的“状态”与“有效性”

主数据也可能有状态（如 用户 status：激活/禁用/注销；商品 status：上架/下架/删除）。

设计 is_active 或 status 字段，并在相关业务逻辑（流程、流水）中检查关联主数据的有效性。

（4）历史快照

对于重要的主数据（如商品价格、规格），如果其变化会影响历史业务的追溯（如查看历史订单时需显示当时的商品信息），需要考虑在 流程/单据 或 流水 表中冗余存储关键信息，或设计主数据历史版本表。

2.举例说明

(1)建表示例

-- 主数据表（用户账户）
CREATE TABLE user_account (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL COMMENT '用户ID',
    account_type TINYINT NOT NULL COMMENT '账户类型:1-现金,2-积分',
    available_balance DECIMAL(15,2) NOT NULL DEFAULT 0 COMMENT '可用余额',
    frozen_balance DECIMAL(15,2) NOT NULL DEFAULT 0 COMMENT '冻结余额',
    version INT NOT NULL DEFAULT 0 COMMENT '版本号(乐观锁)',
    is_active TINYINT NOT NULL DEFAULT 1 COMMENT '账户状态:0-禁用,1-启用',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE uniq_user_account(user_id, account_type)
) COMMENT '用户账户表';

（2）代码示例

账户余额更新（含事务和流水记录）:

@Transactional
public void processRechargeSuccess(String orderNo) {
    // 1. 查询订单
    RechargeOrder order = orderDao.selectByOrderNo(orderNo);
    
    // 2. 更新账户余额
    int rows = accountDao.updateBalance(
        order.getUserId(),
        order.getAccountType(),
        order.getAmount(),  // 正数
        order.getVersion()
    );
    
    if (rows == 0) {
        throw new OptimisticLockException("账户更新冲突");
    }
    
    // 3. 查询更新后余额
    UserAccount account = accountDao.selectByUserAndType(
        order.getUserId(), 
        order.getAccountType()
    );
    
    // 4. 创建资金流水
    AccountTransaction transaction = new AccountTransaction();
    transaction.setTraceNo(IdGenerator.nextId()); // 雪花算法生成
    transaction.setOrderNo(orderNo);
    transaction.setUserId(order.getUserId());
    transaction.setAccountType(order.getAccountType());
    transaction.setAmount(order.getAmount());
    transaction.setOperationType(OperationType.RECHARGE);
    transaction.setEndingBalance(account.getAvailableBalance());
    transaction.setPaymentChannel(order.getPaymentChannel());
    transactionDao.insert(transaction);
    
    // 5. 更新订单状态
    updateOrderStatus(orderNo, OrderStatus.SUCCESS, null, "充值成功");
}

（三）业务流水表（明细/凭证）

这些表记录了与核心单据相关的其他操作或流程。

支付记录表 (payment_record): 记录实际的支付行为，与充值订单关联。包含支付渠道订单号、支付金额、支付时间、支付结果、回调信息等。这是连接业务订单和外部支付系统的关键桥梁。

退款订单表 (refund_order): 如果支持充值后退款，需要独立的退款单来驱动余额扣减和新的资金流水（负向）。

优惠/促销活动表 (promotion / coupon): 如果充值有赠送（如充100送10），需要记录活动规则和用户领取/使用的优惠券，可能产生额外的流水（如“赠送”类型）。

用于系统与支付渠道进行对账，导入对账文件，标记已核对的交易。

1.涉及的字段设计

（1）流水号全局唯一性

业务流水表的记录需要一个全局唯一流水号 (trace_id, serial_no)，这个号通常独立于流程单据号。

它是追溯单笔业务操作的最小单元，对账、排查问题的黄金标识。生成方式需高并发安全（如雪花算法）。

（2）流水方向与符号

明确流水的“方向”表示。通常有两种方式：

• 单字段 + 符号： 一个 amount 字段，正数表示增加（入库、充值），负数表示减少（出库、消费）。

• 双字段 + 类型： in_amount (入账金额)， out_amount (出账金额)，结合 operation_type (操作类型：充值、消费、转账入、转账出等)。这种方式在统计和查询特定方向的流水时更方便。

（3）余额快照

在记录流水时，强烈建议同时记录该笔流水发生后的 ending_balance (期末余额)。

这是对账和快速定位问题的核心依据。例如：“用户A充值100元，流水ID=TX123, 充值后余额=500”。

这样，即使余额表被篡改，也能通过流水逐笔计算核对。

（4）更丰富的上下文信息

除了关联单据号 (order_no)，尽可能记录触发该流水的核心上下文。例如：

• 库存流水：仓库ID、库位ID、批次号。
• 资金流水：支付渠道、支付订单号、手续费、关联的退款单号（如果是退款流水）、关联的优惠券ID（如果涉及）。

（5）流水不可变性

业务流水记录一旦生成，原则上不允许修改或删除。任何错误都需要通过后续的冲正流水（负负得正）或调整流水来修正。这是保证审计追踪完整性的基石。设计权限时要严格控制。

2.举例说明

（1）建表示例

-- 业务流水表
CREATE TABLE account_transaction (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    trace_no VARCHAR(32) NOT NULL UNIQUE COMMENT '全局流水号(雪花算法)',
    order_no VARCHAR(32) NOT NULL COMMENT '关联订单号',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    account_type TINYINT NOT NULL COMMENT '账户类型',
    amount DECIMAL(15,2) NOT NULL COMMENT '变动金额(正负值)',
    operation_type TINYINT NOT NULL COMMENT '操作类型:1-充值,2-消费,3-退款',
    ending_balance DECIMAL(15,2) NOT NULL COMMENT '变动后余额',
    payment_channel VARCHAR(20) COMMENT '支付渠道',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_account(user_id, account_type),
    INDEX idx_create_time(create_time)
) COMMENT '账户流水表';

-- 按月分区
ALTER TABLE account_transaction PARTITION BY RANGE (TO_DAYS(create_time)) (
    PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
    PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01'))
);

(2)代码示例

唯一ID生成（雪花算法）:

public class IdGenerator {
    private static final long START_TIMESTAMP = 1700000000000L;
    private static final long WORKER_ID_BITS = 5L;
    private static final long SEQUENCE_BITS = 12L;
    
    private static long lastTimestamp = -1L;
    private static long sequence = 0L;
    
    public static synchronized String nextId() {
        long timestamp = System.currentTimeMillis();
        
        if (timestamp < lastTimestamp) {
            throw new RuntimeException("时钟回拨");
        }
        
        if (timestamp == lastTimestamp) {
            sequence = (sequence + 1) & ((1 << SEQUENCE_BITS) - 1);
            if (sequence == 0) {
                timestamp = tilNextMillis(lastTimestamp);
            }
        } else {
            sequence = 0L;
        }
        
        lastTimestamp = timestamp;
        
        long id = ((timestamp - START_TIMESTAMP) << (WORKER_ID_BITS + SEQUENCE_BITS))
                | (getWorkerId() << SEQUENCE_BITS)
                | sequence;
                
        return String.valueOf(id);
    }
}

（四）其它扩展性的数据库表

1. 审计与日志表

这些表用于追踪操作历史、排查问题和安全审计。

（1）操作日志表 (operation_log)

记录关键操作（如：手动调整余额、修改订单状态）的操作人、操作时间、操作内容、IP地址等。

区别于业务流水，它记录的是“谁在什么时候改了什么”。

当然要注意操作日志的颗粒度，operation_log 需要定义清晰的记录范围（哪些操作要记？字段级变更还是整条记录？）和内容格式（如记录变更前后的值）。

避免过度记录影响性能，也要避免记录不足难以排查问题。

（2）变更历史表 (entity_history)

对于极其重要的核心单据（如订单），有时会设计专门的表来记录其关键字段（如状态、金额）的每一次变更，形成完整的历史轨迹。

entity_history 的实现可以通过：

• 数据库触发器 (Trigger)： 自动捕获变更，但可能增加数据库负担，逻辑复杂时调试困难。

• 应用层逻辑： 在更新核心实体时，显式插入历史记录。更灵活可控，但需要开发人员注意。

• CDC (Change Data Capture) + 消息队列： 适合大型、解耦系统，将变更事件异步写入历史表或数据仓库。

实体历史变更（使用AOP）:

@Aspect
@Component
public class EntityHistoryAspect {
    
    @Autowired
    private EntityHistoryService historyService;
    
    @AfterReturning(pointcut = "execution(* com..dao.*.update*(..))", returning = "result")
    public void logUpdate(JoinPoint joinPoint, Object result) {
        if (result instanceof Integer && (Integer)result > 0) {
            Object entity = joinPoint.getArgs()[0];
            if (entity instanceof BaseEntity) {
                historyService.recordChange((BaseEntity) entity);
            }
        }
    }
}

2. 统计与汇总表 (可选，用于提升性能)

为了提升查询效率，避免实时计算海量流水，可能会有预计算的汇总表。

（1）余额快照表 (balance_snapshot)

在特定时间点（如每天凌晨）记录用户的余额，用于快速生成报表或作为对账基准。

balance_snapshot 在业务高峰期间（如双11最后一秒）抓取快照可能非常困难且影响性能。常见做法：

• 业务低峰期执行： 如凌晨。

• 基于流水计算： 从上次快照点开始重放所有流水计算当前余额。需要确保流水完整且有序。

• 最终一致性： 接受快照点附近短暂的数据延迟。

（2）统计报表表 (statistics_report)

按日/月汇总充值总额、用户数等，供运营查看。

这类表是为了性能而牺牲实时性。需要明确：

• 更新频率： T+1（日终）？ 小时级？ 实时增量？像库存系统一般按月生成月末报表

• 更新方式： 全量重建？ 增量更新？ (依赖流水表的 create_time 或 batch_id)。

• 数据源： 基于流水表计算？ 基于快照表计算？

二、相互关联关系

（一）溯源与恢复机制

假如有人把流程不小心删掉了，那么根据流水表的明细，可以将流程的信息再恢复回来。

假如有人把账户余额给修改了，可以根据流水表的明细，重新计算账户余额，进行对账。

假如有人将流水表的明细删除了，可以根据流程重新恢复记录的明细。

这样流程、主数据、流水三个表单可以互相印证和恢复数据，这是保证数据完整性和可审计性的关键设计目标。

1.流程删除恢复

仅靠流水恢复流程信息可能不完整（流程可能包含流水未记录的审批意见、备注等元数据）。强调流程表的软删除 (is_deleted 标记) 而非物理删除！ 物理删除风险极高。

2.余额恢复

通过流水重新计算余额 (initial_balance + SUM(amount)) 是标准做法，但需要确定初始余额 (initial_balance) 的可靠来源（如某个快照点）。再次强调流水记录 ending_balance 的重要性！

3.流水删除恢复

这是最危险的！流水被删意味着审计线索断裂。流水必须物理禁止删除！ 如果“删除”是业务需求（如 GDPR 要求），通常只能标记为“匿名化”或移动到归档库，而不是真删除。从流程恢复流水依赖于流程表存储了足够的信息，这通常不如原始流水详细。

（二）对账机制

应该设计 每日/定期对账任务，自动核对：

1.核对余额和流水

核心单据的最终状态/金额 与 支付渠道记录/银行流水。

账户余额 (account_balance) 与 该账户所有流水的汇总 (SUM(amount) from transaction where account_id=xxx)。

2.核对状态流转

流水表的 ending_balance 与下一笔流水的 beginning_balance (或上一笔的 ending_balance) 是否连续。

核心单据触发的流水记录是否完整且一致。

对账结果记录在专门的 reconciliation_result 表中，标记差异以便人工介入。

3.代码示例

(1)余额对账逻辑

public void reconcileBalances(LocalDate date) {
    // 1. 获取账户列表
    List<UserAccount> accounts = accountDao.selectAllActive();
    
    for (UserAccount account : accounts) {
        // 2. 根据流水计算余额
        BigDecimal calculatedBalance = transactionDao.calculateBalance(
            account.getUserId(),
            account.getAccountType(),
            date
        );
        
        // 3. 与实际余额比对
        if (account.getAvailableBalance().compareTo(calculatedBalance) != 0) {
            // 记录对账差异
            ReconciliationResult result = new ReconciliationResult();
            result.setUserId(account.getUserId());
            result.setAccountType(account.getAccountType());
            result.setExpectedBalance(calculatedBalance);
            result.setActualBalance(account.getAvailableBalance());
            result.setReconcileDate(date);
            reconciliationDao.insert(result);
            
            // 告警通知
            alertService.sendBalanceMismatchAlert(account, calculatedBalance);
        }
    }
}

（2）流水记录余额快照验证

public BigDecimal verifyBalance(Long userId, Integer accountType) {
    // 1. 获取最后一笔流水
    AccountTransaction lastTx = transactionDao.selectLastTransaction(
        userId, 
        accountType
    );
    
    // 2. 计算流水后发生的变动
    BigDecimal subsequentChange = transactionDao.sumAmountAfter(
        userId,
        accountType,
        lastTx.getCreateTime()
    );
    
    // 3. 验证当前余额
    BigDecimal expectedBalance = lastTx.getEndingBalance().add(subsequentChange);
    UserAccount account = accountDao.selectByUserAndType(userId, accountType);
    
    if (account.getAvailableBalance().compareTo(expectedBalance) != 0) {
        throw new BalanceVerificationException("余额验证失败");
    }
    
    return expectedBalance;
}

（三）其他重要考虑点

1.事务边界

一个业务流程（如创建充值单并支付成功）通常涉及更新多个表（流程表状态、账户余额、插入流水）。必须使用数据库事务保证这些操作的原子性（ACID），避免部分成功导致数据不一致。

分布式事务考虑：

// 使用Seata的全局事务
@GlobalTransactional
public void crossServiceOperation() {
    serviceA.update();
    serviceB.update();
}

2.幂等性设计

对于可能重试的操作（如支付回调），接口和业务逻辑需要支持幂等性（相同请求处理一次和多次结果一致）。通常利用单据的唯一标识（订单号+状态）或支付渠道的唯一流水号来实现。

3.索引优化

根据查询模式（按用户查流水、按时间查、按单据号查、按状态查等）为流程表、流水表、主数据表合理设计索引。流水表尤其需要关注时间范围查询的性能。

4.归档策略

流水表和流程表会随时间急剧增长。设计合理的归档策略（如按时间分区，将历史数据迁移到冷存储），保证在线业务表的高效查询。

-- 将6个月前数据迁移到历史表
INSERT INTO account_transaction_archive
SELECT * FROM account_transaction 
WHERE create_time < DATE_SUB(NOW(), INTERVAL 6 MONTH);

DELETE FROM account_transaction 
WHERE create_time < DATE_SUB(NOW(), INTERVAL 6 MONTH);

5.枚举值管理

status_dict, enum_type 等字段的枚举值，建议在数据库中使用 字典表 (sys_dict) 管理，或在应用层用常量/枚举类管理。避免在业务代码中硬编码魔法数字/字符串。

--枚举字典表设计：
CREATE TABLE sys_dict (
  id INT PRIMARY KEY AUTO_INCREMENT,
  dict_type VARCHAR(50) NOT NULL COMMENT '字典类型',
  dict_code INT NOT NULL COMMENT '字典编码',
  dict_value VARCHAR(100) NOT NULL COMMENT '字典值',
  INDEX idx_dict_type(dict_type)
);

INSERT INTO sys_dict (dict_type, dict_code, dict_value) VALUES
('order_status', 0, '待支付'),
('order_status', 1, '支付成功'),
('account_type', 1, '现金账户'),
('account_type', 2, '积分账户');

6.软删除

对几乎所有核心业务表（流程、主数据）实施软删除 (is_deleted)，而非物理删除。流水表通常禁止删除。