How to save the old and new version of the record until the supervisor approves the changes?

Question

I am currently working on an ASP.NET MVC 3 project in which I have to record field changes with specific attributes. Example:

public class MyModel { public String PropertyOne { get; set; } // Need to keep track of these properties [RequiresSupervisorKey] public String PropertyTwo { get; set; } } 

As soon as one of the fields is changed, it is required that the supervisor approves these field changes. Until the changes are approved, the record will be pending, and somehow I need to save the old record and the new record until such a time!

What are the best methods for storing these records? Should I have 2 records in a table in the database or should I have an audit table that can store this data until it is approved.

Thanks.

Answer 1

I would save them in one table. Use a combination key to identify a unique string. Line identifier with autoincretment id. and datetime as the second part of the combined key. When the 3rd row can save state. It also allows you to control versions. If you select the field to display, you select in order id by the time date desc, if the status is approved. 1. Hope this helps; -)

Answer 2

This is my .02 from other projects, but I would add a version or status column to the table and save n number of records in the table. I do not know if it is possible to change it in your system for recording by two different users with different changes, but in situations like this usually happens. An audit table is an acceptable solution, but overall I prefer to store things in one table.

Answer 3

I would save both records in a table (old and new) with an additional status field (for example, active, pending, deleted, rejected) (or whatever you think is necessary).

Then I would create a view that shows only active records (used for most purposes), and one that shows only pending records (using a supervisor approval page for a page).

I would create a trigger in the table to ensure that only one record is active at a time. Therefore, if the supervisor changes the record from the pending to the active, it will take the old record and change it to the deletion status. If the supervisor rejects the change, it will transition to the reject state.

To keep the table nimble (you don’t need to keep old statuses permanently), I would have a task that is performed at night to delete all records in the delete or reject status.