在数据库中维护历史 [英] maintain history in a database

问题描述

我正在设计这个数据库，它必须维护员工工资和组织内变动的历史记录.基本上，我的设计有 3 个表(我的意思是，还有更多的表，但对于这个问题，我会提到 3 个，所以请耐心等待).Employee 表(包含最新的工资、职位数据等)、SalaryHistory 表(薪水、日期、原因等)和 MovementHistory(Title、Dept.、评论).我将使用 Linq to Sql，所以我的想法是每次更新员工数据时，旧值都会被复制到各自的历史表中.这是一个好方法吗?我应该使用 Linq to SQL 还是触发器来完成它?感谢您提供任何帮助、建议或想法.

I am designing this database that must maintain a history of employee salary and the movements within the organization. Basically, my design has 3 tables (I mean, there more tables but for this question I'll mention 3, so bear with me). Employee table (containing the most current salary, position data, etc), SalaryHistory table (salary, date, reason, etc.) and MovementHistory(Title, Dept., comments). I'll be using Linq to Sql, so what I was thinking is that every time employee data is updated, the old values will be copied to their respective history tables. Is this a good approach? Should I just do it using Linq to SQL or triggers? Thanks for any help, suggestion or idea.

推荐答案

看看 http://www.simple-talk.com/sql/database-administration/database-design-a-point-in-time-architecture.

基本上，文章建议您在需要跟踪历史记录的表格中包含以下列 -

Basically, the article suggests that you have the following columns in the tables you need to track history for -

* DateCreated – the actual date on which the given row was inserted.
* DateEffective – the date on which the given row became effective.
* DateEnd – the date on which the given row ceased to be effective.
* DateReplaced – the date on which the given row was replaced by another row.
* OperatorCode – the unique identifier of the person (or system) that created the row. 

DateEffective 和 DateEnd 一起告诉您该行的有效时间(或员工在某个部门的时间，或他获得特定薪水的时间).

DateEffective and DateEnd together tell you the time for which the row was valid (or the time for which an employee was in a department, or the time for which he earned a particular salary).

这篇关于在数据库中维护历史的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！