关于捕获审计跟踪的数据库设计的想法 [英] Ideas on database design for capturing audit trails

问题描述

如何维护数据库中数据的日志?

How can I maintain a log of the data in my DB?

我必须维护对每一行所做的每次更改的日志.这意味着我不能允许执行 DELETE 和 UPDATE.

I have to maintain a log of every change made to each row. That means that I can't allow DELETE and UPDATE to be performed.

我怎样才能保留这样的日志?

How can I keep such a log?

推荐答案

使用仅插入数据库"

基本思想是永远不要更新或删除数据.

The basic idea is that you never update or delete data.

每个表有 2 个日期时间列from 和 to.

Each table has 2 datetime columns from and to.

它们以每个值 null 开始(时间开始到时间结束)

They start with the value null in each (beginning of time to end of time)

当您需要更改"行时，您可以添加新行，同时将前一行中的 to 更新为 Now 并将 from 更新为您要添加到 Now 的行.

When you need to "change" the row you add a new row, at the same time you update the to in the previous row to Now and the from in the row you are adding to Now.

您通过一个包含 where to = null 的视图从表中读取数据.

You read data out of the table via a view that has a where to = null in it.

此方法还可以让您随时了解数据库的状态.

This method also gives you a picture of the state of your database at any point in time.

编辑

只是为了回应评论澄清:序列将由表的主键给出，这将是一个自动增量编号.

Just to clarify in response to the comment: The sequence would be given by the primary key of the table, which would be an autoincrement number.

这篇关于关于捕获审计跟踪的数据库设计的想法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！