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

问题描述

我设计的这个数据库必须保持员工工资的历史和组织内的变动。基本上，我的设计有3个表（我的意思是，有更多的表，但对于这个问题，我会提到3，所以忍受我）。雇员表（包含最新的工资，位置数据等），SalaryHistory表（工资，日期，原因等）和MovementHistory（标题，部门，注释）。我将使用Linq到Sql，所以我想的是每次员工数据更新，旧的值将被复制到各自的历史表。 这是一个好方法吗？我应该使用Linq到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屋！