谈数据删除设计-以记账凭证为例

原文地址 https://my.oschina.net/yangyishe/blog/3138204

1 常见删除策略

凡是做业务逻辑系统, 总是离不开对删除逻辑的处理.
本文论述重点是伪删除, 即字段标示状态, 这是在一些中小型系统开发中的单据等较重要数据的主流做法.
但在此之前, 不妨先将常见删除策略列举一下:

1. 数据库设置级联
   这个我没太懂是怎么回事, 不过网上也说缺点较多, 很少用到, 在此就不考虑了

2. 触发器控制

-- 本文所写sql默认数据库均为mysql
CREATE TRIGGER `tg_bf_insert_t_product_only` BEFORE INSERT ON `t_product` FOR EACH ROW begin
insert 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 begin
insert 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,
缺点是对于一些级联数据的恢复不好控制, 如主表单和明细表单, 另在表结构变更的时候, 对于触发器的维护也是一件需要注意的事情.

1. 字段标示状态 / 伪删除 (下文中将统称为伪删除)
   即用状态表示已删除, 如 status=-1 或者 is_del=1,
   之所以说是中小型系统开发的主流做法, 是因为对于重要数据, 为了控制风险, 不会直接将数据删除, 而使用触发器前面的缺点也说了, 维护十分不易.
   中小型系统本来就有逻辑变更频繁, 以及数据量不会太高 (单据数量几百万上千万, 但很少上亿) 的特点, 权衡之下, 伪删除往往成为首选.

2 伪删除不同设计的优劣点

即使是伪删除, 也有几种不同的设计方式, 以下以财务系统中常常使用到的记账凭证为例, 介绍下几种伪删除的设计方案, 及实际中应当如何选择.

以下是现实生活当中的一张记账凭证图片.

由图片可以看出, 传统的记账凭证, 在数据库设计中, 至少需要两种表: 凭证主表和凭证明细表.
凭证主表记录凭证日期, 凭证字 (如记), 凭证数(如图片右上角的 1 号) 等信息,
凭证明细需要记录摘要, 会计科目, 借贷方向 / 借方金额 / 贷方金额 (这三个内容在数据库表中至少需要保存 2 个).
实际凭证设计中, 可能还需要考虑辅助核算等信息, 本文简化处理不考虑这些.

由于凭证比较重要, 多数情况都是原始信息源 (即不是可从其他数据中推导出的冗余), 故选择伪删除是较常见的.

2.1 状态设计字段的选择

如前文所述, 对于伪删除字段的选择, 一般有两种:

1. 将凭证的常见状态全都放在一个字段中, 包含已保存 status=0, 已审核 status=1, 已删除 status=-1;

2. 将已删除单独设为一个字段, 如 is_del, 删除状态时为 0, 伪删除状态时为 1.

第一种选择, 好处是状态字段只有一个, 且恰好凭证在已审核状态下是不允许被删除的, where 约束起来比较简单.

-- voucher_month表示凭证月份,记账凭证均是以月为基本周期的
-- voucher_mark表示凭证字+凭证数的一种冗余,和凭证月联合起来用以表示某一公司的一张唯一记账
update fnc_voucher
set status=-1
where company_id=1001
and voucher_month='2019-01'
and voucher_mark='记-1'
and status=0

第二种选择, 好处是将删除状态与正常的审批流程状态区分开, 使得两种逻辑得以解耦, 还有一种好处, 下文中会有提及.

-- is_audited是是否已审核的意思
update fnc_voucher
set is_del=1
where company_id=1001
and voucher_month='2019-01'
and voucher_mark='记-1'
and is_audited=0
and is_del=0

虽然第一种选择的 sql 看起来更简短, 但个人还是建议第二种选择.

2.2 唯一索引的设计协同

设计数据库表格时, 一般建议是每一张数据库表格至少需设置一个唯一索引, 个别情况还需要设计多个唯一索引. 伪删除带来的状态标识字段增加, 可能会给唯一索引的设计带来一些影响.

当凭证不考虑伪删除的时候, 其唯一索引的设计方式如下:

alter table fnc_voucher
add unique key uk_voucher_cmm (company_id,voucher_month,voucher_mark);

当用户允许凭证断号时, 如在'记 - 7'和'记 - 9'之间允许存在一个空的凭证号时, 以上的唯一索引仍能正常发挥作用.
但当用户不允许凭证断号 (至少不允许自动断号, 可以增加手动凭证弥补断号) 时, 上面的唯一索引就不再符合逻辑. 当'记 - 8'凭证已伪删除时, 如果再增加一张同月的'记 - 8'凭证, 无疑会报 duplicate key 错误.

为了消除这种问题, 就需要将删除信息体现在唯一索引中.
这就是我建议将删除状态单独设置为一个字段的另一个原因: 当凭证被删除时, 不将 is_del 设为 1, 而改为 id 值:

update fnc_voucher
set is_del=id
where company_id=1001
and voucher_month='2019-01'
and voucher_mark='记-8'
and is_audited=0
and is_del=0

这样, 唯一索引就可设计为:

alter table fnc_voucher
add unique key uk_voucher_cmmd (company_id,voucher_month,voucher_mark,is_del);

对于均放在一个字段中的设计, 当然也可考虑将删除后的状态值设置为 id 的负值:

update fnc_voucher
set status=-id
where company_id=1001
and voucher_month='2019-01'
and voucher_mark='记-8'
and status=0

只是这样做, 又会更进一步增加删除与正常流程的耦合性, 给以后的设计带来较大的困扰, 是不很建议这样做的. 只有当已经将删除设计为单字段混合状态时, 才考虑使用这种方法.

2.3 对凭证明细的影响

前文所述的重点, 都是在对凭证主表上, 而一旦考虑到凭证明细, 就又回出现新的问题.
查询凭证明细表的时候, 有两种选择:

1. 根据主表的 id 查询;

select summary,subject_code,debit_amount,credit_amount
from fnc_voucher_detail
where company_id=1001
and voucher_id=12345
-- 此sql理论上可以不在约束条件中加company_id限制,加只是为了格式统一

1. 根据主表的凭证标识来查询;

select summary,subject_code,debit_amount,credit_amount
from fnc_voucher_detail
where company_id=1001
and voucher_month='2019-01'
and voucher_mark='记-8'

每种都有各自的优势, 就笔者个人而言, 习惯使用第二种: 根据主表的凭证表示查询凭证明细, 但这就产生衍生了一个新的问题:
当一个凭证伪删除时, 且又生成了一个与已删除凭证标识相同的新凭证, 则新凭证就会共享已删除凭证的明细, 导致明细逻辑的错误!

解决这个问题, 就必须要在凭证明细上也增加删除状态标识, 当伪删除凭证时, 同时也对凭证明细进行伪删除处理.

update fnc_voucher_detail
set is_del=id
where company_id=1001
and voucher_month='2019-01'
and voucher_mark='记-8'
and is_del=0;

select summary,subject_code,debit_amount,credit_amount
from fnc_voucher_detail
where company_id=1001
and voucher_month='2019-01'
and voucher_mark='记-8'
and is_del=0;

如果使用根据 id 关联查询, 当然可以规避这种情况, 只是用 id 需要关联查询, 一般会带来一些效率上的差异.

具体使用哪种明细关联方式, 见仁见智吧.

推荐一个公众号

MarkerHub

主分析开源项目，以及导图梳理Java知识点！