关于数据库表字段逻辑删除设计的思考

一句话描述：逻辑删除建议使用2张表。

通常我们在做配置类的表设计时，

1. 为了审计谁最后废弃删除了配置数据。
2. 为了在误删除数据时，很方便的恢复数据。

实现方案

第一种，添加字段用于标志被删除的数据

我们使用了逻辑删除相关字段，deleteId和deleteTime等。

这样被逻辑删除的数据仍然遗留在表中。
这个逻辑删除字段，对业务代码的影响较大；对更新和查询数据库的性能影响也较大。表现如下:

1. 查询数据时，所有的查询条件都需要增加deleteTime==null。
2. 不可以使用业务唯一性Id作为数据库表Id，需要增加1个专门的没有逻辑意义的自增长Id作为唯一性Id。
3. 业务Id不能建唯一性约束，也不能建唯一索引。
4. 增加数据时，判断业务Id是否为重复，需要先做查询，不能利用表的唯一约束。
5. 增加数据时，判断唯一性字段是否重复，需要先做查询，也不能利用表的唯一约束。

第二种，增加1张表，专门保存被逻辑删除的数据。

操作方式：

1. 在原表表空间（数据库）下，新增加1张表，表名为原表名+backup。
2. 复制原表的所有字段定义。
3. 新增自增长Id字段作为新表的Id。
4. 新增deleteId和deleteTime字段。（假设原表中没有deleteId和deleteTime字段。）
5. 删除复制于原表Id及其他字段的唯一性约束。

使用方式：

当删除数据时，在1个事务里，同时将数据从原表中删除，并插入到新表中。其他操作与物理删除时的设计，保持不变。
或者利用触发器，储存过程来实现，但可能存在安全隐患和额外的性能消耗。

使用好处：

1. 除删除操作外，其他操作与物理删除时的设计完全一致，客户端代码简单。（因为对于原表确实是物理删除。）
2. 业务Id可以作为主键，不用建立普通索引，查询更快。
3. 查询时不用过滤被逻辑删除的数据，查询更快。
4. 如果觉得逻辑删除没有意义，想恢复物理删除。只需要修改删除逻辑即可，其他地方没有任何影响。

另外：使用1张表比使用2张表在数据恢复时的一个假想优势是不存在的。

该假想优势表现为：使用1张表时，只需要将逻辑删除字段设置为空(deleteId=null, deleteTime=null)，就可以恢复数据。
分析：这种恢复数据的方式，可能破坏逻辑Id及其他唯一性约束字段的约束。
因此，直接清空逻辑删除字段恢复数据的方式不可取。只能参考原始数据，从新新增1条新数据，并校验相关参数。
使用这种方式恢复数据，2种方式都一样。
所以：我建议使用这种新的方式设计数据库表结构。

附：如果已经使用了第一种方法，可以每天运行定时任务，把 deleted 为真的转移到 backup表，backup表每天也运行定时任务，超过很久的就删除。