I have come across a requirement where we need to maintain a table where all the edit information needs to be shown. The requirement is as follows:
When someone edits anything in a Request form (has 9 inputs fields which can be edited), a new “Edit Log” read-only table should appear.
Columns in the Table should include:
Input Field Name that was changed
Initial Input Data (Data provided when creating a request first time)
Input Data that was provided during the edit (Current)
Date and Time of Edit
Name of the Editor
Status of the Request at the time of the Edit (Can be Pending, Approved, etc.)
It should look something similar to this:
Your open inputs are invited for handling this in database (how can we maintain this log in DB) and showing in Sail UI.
Thanks in advance
Discussion posts and replies are publicly visible
Here's another potential way of looking at it:
Are there a finite list of actions that a user can take on a given record? Are there, say 5 or 6 or 7, main discrete things a user can do to a given record? You could simply create a History table that stores which action was taken, on which record, by whom, and when. You could even number those actions 1 - 7 somewhere on a reference table, and simply store the primary key of the reference as a FK on the HISTORY table.
The whole point is that while there may be more than actually 7 things the user CAN do to a record, you are only concerned about the perhaps 5 to 9 to maybe even 20 things that are really important to know about. Just record when and by whom THOSE things happen.
When you have more, you've got far more intimate knowledge of what your users are doing. Your auditors will be much happier, your client will be much more secure against fraud, hackers, bugs, data corruption, etc. You'll be able to piece broken data back together much easier, and you'll be able to track down bugs much more easily. Your life will be better in so many ways by tracking more things. This is your value.
When you track more things, you run out of space more quickly. It also slows down your application to have to write to the DB more, though not by much per write. You will eventually have to do some shenanigans to keep your DB from crashing if you rack up so many rows too quickly. This is your cost.
Start with the most important thing to track, then keep adding rows to your auditing while adding the row gives you more value than cost. Soon more and more rows will add less value per row, and more cost per row. Eventually the cost will outpace the value, and that's when you stop.
Admittedly just more in-depth exactly what Stewart is saying.
Thanks David. That's what I was looking for, we've created a decision table with finite no. of actions that user can take while editing form and have provided a specific to each action, then on that particular action storing that number value to DB in log table.
This way we are able to achieve the requirement but with limited actions. This work great for now.