文档章节

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

社哥
 社哥
发布于 2019/12/06 11:22
字数 2015
阅读 4.8K
收藏 57

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,
缺点是对于一些级联数据的恢复不好控制, 如主表单和明细表单, 另在表结构变更的时候, 对于触发器的维护也是一件需要注意的事情.
3. 字段标示状态/伪删除(下文中将统称为伪删除)
即用状态表示已删除,如status=-1或者is_del=1,
之所以说是中小型系统开发的主流做法, 是因为对于重要数据, 为了控制风险, 不会直接将数据删除, 而使用触发器前面的缺点也说了, 维护十分不易.
中小型系统本来就有逻辑变更频繁, 以及数据量不会太高(单据数量几百万上千万, 但很少上亿)的特点, 权衡之下, 伪删除往往成为首选.

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

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

以下是现实生活当中的一张记账凭证图片.
image
由图片可以看出, 传统的记账凭证, 在数据库设计中, 至少需要两种表: 凭证主表和凭证明细表.
凭证主表记录凭证日期, 凭证字(如记), 凭证数(如图片右上角的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需要关联查询, 一般会带来一些效率上的差异.

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

end

© 著作权归作者所有

社哥
粉丝 17
博文 24
码字总数 48366
作品 0
石家庄
程序员
私信 提问
加载中

评论(11)

超级奶爸老谭
超级奶爸老谭
这波操作骚
落舞者
落舞者
is_del 存删除的时间戳, 即 is_del = 0 未删除, is_del>0 已删除,且知道删除时间是什么时候,删除操作又是小概率非并发事件,所以时间戳足以保证唯一性。
社哥
社哥 博主
时间戳和id我认为都是可以的. 之所以我选择id, 主要是一些没有经验的程序猿, 在处理批量保存单据的时候, 选择一些不太合理保存方案. 某些情况下, 导致时间戳重复的可能性会提升. 举个比较极端的例子: 批量插入的一些单据中, 有3条相同的单据, 如果不进行筛选操作, 可能造成在较相近的一个时间段内, 先插入一条数据, 再删除这条数据, 再插入相同的数据, 再删除这条数据(这种做法的时间复杂度是O(n), 不合情理, 但新手很容易出这个问题), 如果采用的时间戳是秒级而非毫秒级, 就会duplicate key错误.
whaon
whaon
1. 这种较为可行,不能用0,1表示未删除和删除,对索引不友好,加时间戳可以,时间戳搞毫秒够了 2. 或者用-id 2. 使用历史表,这样可以减少单表的数据量,但是如果删除数据不多的话就没啥意义,不如直接用上面的方案,然后分库分表
错觉
错觉
一般放 _del 后缀的表中, 所有纠结就都没了, is_del 字段也不用, 缺点也有就是多了表
社哥
社哥 博主
历史表的思想类似于触发器控制, 做法上没有问题. 但实际开发中, 领导(或者是领导的领导)会经常性的改变想法, 今天加个字段, 明天加个状态, 后天加个冗余, 有时一些逻辑甚至要求加新表才能实现. 这样历史表也得对应增加对应的内容, 对应的开发成本就太高了. (而且众所周知的, 领导们一向会高估你的开发效率) 伪删除相对来说, 开发成本就不那么高了.
calvinwilliams
calvinwilliams
可以移到历史表里,历史表去掉主键唯一索引。
社哥
社哥 博主
您的想法, 和"错觉"的回复相似, 可以参考下我在他下面的回复. 简言之, 主要是为了开发效率考虑, 所以选择了伪删除方案.
名字呢
名字呢
明细的查询修改删除还是通过主表id来进行处理吧。一般明细表数据量都会比较多,对明细的主表id列建立索引后,都能更快的找到该主表id对应的明细记录。
阿债
阿债
前面都同意,最后查询明细时,我建议用第一种“根据主表的id查询”。所谓关联查询,实际上时两次查询,第一次查主表得到ID,第二次查询查找int类型外键。如果数据量小,这种方式花费时间略长,因为多了一次查询时间、数据库解析SQL、制定查询计划都多一次;而明细表数据行多于百万时,说不定第二次查询的时间有明显优势,能够弥补回来。
社哥
社哥 博主
我在实际开发的时候, 其实两种字段都是保存了的. id在修改删除, 以及查询单个单据上有优势. 而在一些统计查询上, 如查询某个科目在某个时间段内的金额(借贷值之差), 如果采用唯一索引方式, 且在明细上增加了is_del字段,则可直接根据明细表查询, 而不需要联查主表, 这时的效率就会更高一些. 采用id联查主表以筛选is_del效率就会差一些.
如何给金蝶kis记账王新增凭证字

众所周知,凭证字是财务领域常用专业名词之一,当使用金蝶KIS记账王录入记账凭证时一定要输入凭证字,本文以新增凭证字-记为例讲解金蝶KIS记账王新增凭证字的步骤。 在正式学习教程之前,小编...

石沉大海
2016/12/05
79
0
金蝶kis记账王如何高效的记账

与传统的手工记账相比,金蝶KIS记账王可以大大提高财务管理工作效率,同等时间和账簿要求下,用户可以处理更多家公司的账务。金蝶KIS记账王有很多可以提高效率的使用技巧。 成批审核凭证 金蝶...

石沉大海
2016/12/02
111
0
怎么高效的运用金蝶软件

与传统的手工记账相比,金蝶财务软件大大提高财务管理工作效率,同等时间和账簿要求下,用户通过金蝶等财务软件可以处理更多公司的账。本文小编将总结出金蝶KIS记账王高效率使用的技巧,赶快...

石沉大海
2016/11/03
49
1
使用金蝶kis财务的具体步骤

账务处理是金蝶KIS记账王核心功能,换句话说,掌握账务处理技巧足以充分发挥记账王功能,账务处理涉及凭证-账簿的基本会计核算环节。会计核算环节规范性强、通用性高,能适应不同行业、不同企...

石沉大海
2016/10/21
45
0
金蝶kis记账王打开会计分录序时簿的方法

金蝶KIS记账王的分录序时簿专门用于查询或修改会计凭证,可谓序时簿中汇集了各种凭证编辑命令,但是有的用户不知道如何打开分录序时簿,本文小编将详解金蝶KIS记账王根据不同凭证过滤需求打开...

石沉大海
2016/12/19
72
0

没有更多内容

加载失败,请刷新页面

加载更多

检查字符串是否不为空且不为空

如何检查字符串是否不为null也不为空? public void doStuff(String str){ if (str != null && str != "**here I want to check the 'str' is empty or not**") { /* handl......

javail
3分钟前
7
0
No module named 'apscheduler.schedulers'

关于 ModuleNotFoundError: No module named 'apscheduler.schedulers' 和 ModuleNotFoundError: No module named 'apscheduler' 的问题,网上有不少解决。大多数都是因为,文件起名与模块名......

開援带碼
24分钟前
27
0
如何查看Android的系统版本?

有谁知道我怎么能检查系统版本(例如1.0 , 2.2 ,等)编程? #1楼 我无法对答案发表评论,但是Kaushik的答案存在一个严重错误:SDK_INT与系统版本不同,但实际上是指API级别。 if(Build.VER...

技术盛宴
50分钟前
35
0
引入AI变量,精准农业正在加速豹变?

  不久前,“江苏省脱贫率达 99.99%,尚未脱贫人数仅剩 6 户、17 人”这样一则新闻刷屏。“把这 17 个人迁出,江苏就全省脱贫”、“最后这 6 户人家拖了后腿”,在网民们的调侃和质疑声背后...

水果黄瓜
53分钟前
41
0
Elasticsearch系列---结构化搜索

概要 结构化搜索针对日期、时间、数字等结构化数据的搜索,它们有自己的格式,我们可以对它们进行范围,比较大小等逻辑操作,这些逻辑操作得到的结果非黑即白,要么符合条件在结果集里,要么...

清茶豆奶
今天
69
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部