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

问题描述

如何在我的数据库中保存数据的日志？

我必须保存每行更改的日志。这意味着我不能允许执行 DELETE 和 UPDATE 。

如何保存此类日志？

解决方案

使用仅插入数据库

$

每个表格都有两个日期时间栏来自

它们以每个值的开始时间（时间开始到时间结束）开始。

当您需要更改行时，您需要添加新行，同时将上一行中的更改为，然后将从

您通过一个视图（其中to = null）读取表中的数据。

此方法还可以显示任何时间点数据库的状态。

EDIT

只是为了说明回答注释：序列将由表的主键给出，这将是一个自动递增号。 / p>

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

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?

解决方案

Use "Insert Only Databases"

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

Each table has 2 datetime columns from and to.

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

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.

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.

EDIT

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屋！