数据历史跟踪最佳实践 [英] Data history tracking best practice

问题描述

我们需要跟踪在某些表上的时间内的数据修改。我们需要一些关于如何实现这个任务的建议。我们有两条街道跟在我们的脑海。
1）创建具有以下记录的表：userid，日期修改，表名，字段名，字段类型，字段值。这样，我们将跟踪一个触发器。

2）在所有表上添加一个状态字段，我们需要跟踪称为状态的历史记录。此字段将具有以下值：I = inserted - D = deleted - M =使用相对日期od修改修改。这样，我们总能知道最新的有效行和所有以前的数据修改
3）在你的心中
你建议什么？

历史模式与真实表相同，但添加了 history_id PK和事件时间戳。 历史模式中的表没有任何约束。

在postgreSQL中，你可以使用 CREATE 语句：

  CREATE TABLE history.tbl AS 
（history_id BIGSERIAL PRIMARY KEY，
 event_time TIMESTAMP DEFAULT NOW（），
 action CHAR（1），
 LIKE public.tpl）; 
  

之后，您应该创建一个触发器，插入，更新，删除后插入历史记录表。 / p>

we need to keep track of data modification during the time on some table. We need some advice about how to achieve this task. We have two streets to follow in our mind. 1) Create a table with the following records: userid, date modification, table name, fieldname, fieldtype, fieldvalue. In this way, we will track with a trigger.

2) Add a state field on all the table we need to track history called Status. This field will have the following values: I = inserted - D = deleted - M = Modified with relative date od modification. In this way we can always know the latest valid row and all the previous data modification 3) It’s in your minds  What do you suggest?

解决方案

I have done this multiple times on PostgreSQL using separate "history" schema and triggers.

The tables in the "history" schema are identical to the real tables but with history_id PK and event timestamp added. Tables from "history" schema don't have any constraints. Also you need to create field for action if you need to track deletion as well.

In postgreSQL you can easily create such table using such CREATE statement:

CREATE TABLE history.tbl AS
  (history_id BIGSERIAL PRIMARY KEY, 
  event_time TIMESTAMP DEFAULT NOW(), 
  action CHAR(1), 
  LIKE public.tpl);

After this you should create trigger which will insert into the history table on insert, update, delete.

这篇关于数据历史跟踪最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！