Table Structure for Multiple Histrory

Question

i want to create table to keep histroy of the ammendments & history of the object. for That i have created two column Primary Key ( Id & update date)

I have 3 more date columns to maintain history & Status Column for Actual object history. Status , StatusFrom , Statusto, UpdateDate & NextUpdateDate

UpdateDate & NextUpdateDate is for maintain histroy of ammendment.

Is there any better way to maintain actual history of the Record & Ammend histroy of the record?

Answer 1

Seemingly relevant question and answer: http://stackoverflow.com/questions/793643/auditing-sql-server-data-changes.

Answer 2

You're creating what is known as an "audit table". There are many ways to do this; a couple of them are:

1. Create a table with appropriate key fields and before/after fields for all columns that you're interested in on the source table, along with a timestamp so you know when the change was made.

2. Create a table with a appropriate key fields, a modification timestamp, a field name, and before/after columns.

Method (1) has the problem that you end up with a lot of fields in the audit table - basically two for every field in your source table. In addition, if only one or two fields on the source table change then most of the fields on the audit table will be NULL which may waste space (depending on your database). It also requires a lot of special-purpose code to figure out which field changed when you go back to process the audit table.

Method (2) has the problem that you end up creating a separate row in the table for each field that is changed on your source table, which can result in a lot of rows in the audit table (one row for each field changed). Because each field change results in a new row being written to the audit table you also have the same key values in multiple rows which can use up a bunch of space just for the keys.

Regardless of how the audit table is structured it's usual to use a trigger to maintain them.

I hope this helps.