谈数据删除设计-以记账凭证为例
- 2020 年 1 月 2 日
- 筆記
原文地址 https://my.oschina.net/yangyishe/blog/3138204
1 常见删除策略
凡是做业务逻辑系统, 总是离不开对删除逻辑的处理. 本文论述重点是伪删除, 即字段标示状态, 这是在一些中小型系统开发中的单据等较重要数据的主流做法. 但在此之前, 不妨先将常见删除策略列举一下:
- 数据库设置级联 这个我没太懂是怎么回事, 不过网上也说缺点较多, 很少用到, 在此就不考虑了
- 触发器控制
-- 本文所写sql默认数据库均为mysqlCREATE TRIGGER `tg_bf_insert_t_product_only` BEFORE INSERT ON `t_product` FOR EACH ROW begininsert into t_product_only (p_id,only_code) values (new.p_id,concat(new.group_code,',',new.p_code)) ;end;CREATE TRIGGER `tg_af_delete_product_only` AFTER DELETE ON `t_product` FOR EACH ROW begininsert into t_product_deleted (p_id,group_code,p_code) values (old.p_id,old.group_code,old.p_code);delete from t_product_only where p_id=old.p_id;end;
优点是代码业务逻辑简单化, 且可以使用 unique index, 缺点是对于一些级联数据的恢复不好控制, 如主表单和明细表单, 另在表结构变更的时候, 对于触发器的维护也是一件需要注意的事情.
- 字段标示状态 / 伪删除 (下文中将统称为伪删除) 即用状态表示已删除, 如 status=-1 或者 is_del=1, 之所以说是中小型系统开发的主流做法, 是因为对于重要数据, 为了控制风险, 不会直接将数据删除, 而使用触发器前面的缺点也说了, 维护十分不易. 中小型系统本来就有逻辑变更频繁, 以及数据量不会太高 (单据数量几百万上千万, 但很少上亿) 的特点, 权衡之下, 伪删除往往成为首选.
2 伪删除不同设计的优劣点
即使是伪删除, 也有几种不同的设计方式, 以下以财务系统中常常使用到的记账凭证为例, 介绍下几种伪删除的设计方案, 及实际中应当如何选择.
以下是现实生活当中的一张记账凭证图片.
由图片可以看出, 传统的记账凭证, 在数据库设计中, 至少需要两种表: 凭证主表和凭证明细表. 凭证主表记录凭证日期, 凭证字 (如记), 凭证数(如图片右上角的 1 号) 等信息, 凭证明细需要记录摘要, 会计科目, 借贷方向 / 借方金额 / 贷方金额 (这三个内容在数据库表中至少需要保存 2 个). 实际凭证设计中, 可能还需要考虑辅助核算等信息, 本文简化处理不考虑这些.
由于凭证比较重要, 多数情况都是原始信息源 (即不是可从其他数据中推导出的冗余), 故选择伪删除是较常见的.
2.1 状态设计字段的选择
如前文所述, 对于伪删除字段的选择, 一般有两种:
- 将凭证的常见状态全都放在一个字段中, 包含已保存 status=0, 已审核 status=1, 已删除 status=-1;
- 将已删除单独设为一个字段, 如 is_del, 删除状态时为 0, 伪删除状态时为 1.
第一种选择, 好处是状态字段只有一个, 且恰好凭证在已审核状态下是不允许被删除的, where 约束起来比较简单.
-- voucher_month表示凭证月份,记账凭证均是以月为基本周期的-- voucher_mark表示凭证字+凭证数的一种冗余,和凭证月联合起来用以表示某一公司的一张唯一记账update fnc_voucherset status=-1where company_id=1001and voucher_month='2019-01'and voucher_mark='记-1'and status=0
第二种选择, 好处是将删除状态与正常的审批流程状态区分开, 使得两种逻辑得以解耦, 还有一种好处, 下文中会有提及.
-- is_audited是是否已审核的意思update fnc_voucherset is_del=1where company_id=1001and voucher_month='2019-01'and voucher_mark='记-1'and is_audited=0and is_del=0
虽然第一种选择的 sql 看起来更简短, 但个人还是建议第二种选择.
2.2 唯一索引的设计协同
设计数据库表格时, 一般建议是每一张数据库表格至少需设置一个唯一索引, 个别情况还需要设计多个唯一索引. 伪删除带来的状态标识字段增加, 可能会给唯一索引的设计带来一些影响.
当凭证不考虑伪删除的时候, 其唯一索引的设计方式如下:
alter table fnc_voucheradd unique key uk_voucher_cmm (company_id,voucher_month,voucher_mark);
当用户允许凭证断号时, 如在'记 – 7'和'记 – 9'之间允许存在一个空的凭证号时, 以上的唯一索引仍能正常发挥作用. 但当用户不允许凭证断号 (至少不允许自动断号, 可以增加手动凭证弥补断号) 时, 上面的唯一索引就不再符合逻辑. 当'记 – 8'凭证已伪删除时, 如果再增加一张同月的'记 – 8'凭证, 无疑会报 duplicate key 错误.
为了消除这种问题, 就需要将删除信息体现在唯一索引中. 这就是我建议将删除状态单独设置为一个字段的另一个原因: 当凭证被删除时, 不将 is_del 设为 1, 而改为 id 值:
update fnc_voucherset is_del=idwhere company_id=1001and voucher_month='2019-01'and voucher_mark='记-8'and is_audited=0and is_del=0
这样, 唯一索引就可设计为:
alter table fnc_voucheradd unique key uk_voucher_cmmd (company_id,voucher_month,voucher_mark,is_del);
对于均放在一个字段中的设计, 当然也可考虑将删除后的状态值设置为 id 的负值:
update fnc_voucherset status=-idwhere company_id=1001and voucher_month='2019-01'and voucher_mark='记-8'and status=0
只是这样做, 又会更进一步增加删除与正常流程的耦合性, 给以后的设计带来较大的困扰, 是不很建议这样做的. 只有当已经将删除设计为单字段混合状态时, 才考虑使用这种方法.
2.3 对凭证明细的影响
前文所述的重点, 都是在对凭证主表上, 而一旦考虑到凭证明细, 就又回出现新的问题. 查询凭证明细表的时候, 有两种选择:
- 根据主表的 id 查询;
select summary,subject_code,debit_amount,credit_amountfrom fnc_voucher_detailwhere company_id=1001and voucher_id=12345-- 此sql理论上可以不在约束条件中加company_id限制,加只是为了格式统一
- 根据主表的凭证标识来查询;
select summary,subject_code,debit_amount,credit_amountfrom fnc_voucher_detailwhere company_id=1001and voucher_month='2019-01'and voucher_mark='记-8'
每种都有各自的优势, 就笔者个人而言, 习惯使用第二种: 根据主表的凭证表示查询凭证明细, 但这就产生衍生了一个新的问题: 当一个凭证伪删除时, 且又生成了一个与已删除凭证标识相同的新凭证, 则新凭证就会共享已删除凭证的明细, 导致明细逻辑的错误!
解决这个问题, 就必须要在凭证明细上也增加删除状态标识, 当伪删除凭证时, 同时也对凭证明细进行伪删除处理.
update fnc_voucher_detailset is_del=idwhere company_id=1001and voucher_month='2019-01'and voucher_mark='记-8'and is_del=0;
select summary,subject_code,debit_amount,credit_amountfrom fnc_voucher_detailwhere company_id=1001and voucher_month='2019-01'and voucher_mark='记-8'and is_del=0;
如果使用根据 id 关联查询, 当然可以规避这种情况, 只是用 id 需要关联查询, 一般会带来一些效率上的差异.
具体使用哪种明细关联方式, 见仁见智吧.
推荐一个公众号
MarkerHub
主分析开源项目，以及导图梳理Java知识点！
