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
1. I would urge you to express to your client the egregious cost that you are going to incur by doing this, and strongly recommend that they consider whether they really want to do this.
2. You can use a!writeToDataStoreEntity() as part of the saveInto of *this makes me cringe to type it* all 9 input fields. This will write to the database every time the saveInto function is called. (Warning: This may be even grossly more infeasible if it actually saves once every key press, which is likely. You could be writing to the database hundreds of times rather than 9 times.)
3. To display this info on the screen, you then need to query the latest row from the database for each input once every time the variable that stores the contents of each of the nine inputs is changed. Whichever query that runs again will pull the data, and then you need to display it in a separate read-only component beneath each input.
4. DON'T DO THIS! Explain to your client what a terrible idea this is. You don't need to log edits at the input, or more probable if you took this approach keypress, level. You're going to wind up sending so much data to and getting so much data from that you will kill your connection thread pool. If it somehow passed test, doing so with only a few users on the test system total, I promise you that you would crash your database in production with only a few users filling out this form at the same time.
Under normal circumstances, under most reasonable design patterns, no data would go to the database, hence nothing would really be edited, and nothing need to be logged, until the form was submitted. Then you can display another form with all the logging information you want. One trip to the DB, one trip back, with everything. One maybe two connection threads. You don't need your logging to be more granular than that, because your updates to the data itself shouldn't be that granular.
I would echo 's comments. I guess my reaction is to ask you to wind back to what the objective is here? Usually this sort of requirements reflects a need to audit what a record looked like before a transaction was applied and what it looks like after a transaction was applied. In which case you're only really interested in actual changes applied to the database row in question, not all of the changes I started to make then undid then re-did at the form level - what value would there be in that?
For the audit-style requirement one common pattern is as follows:
So: the key question to ask is - what is the objective you're aiming for?
Thanks and Stewart Burchell for the elaborate responses. The edit log table is actually going to be used for audit purposes as Stewart mentioned to check every possible update made in the database row.
Stewart Burchell
History table seems to be a good idea, but that might cost high as data grows as you already mentioned.
So, it's all - ALL - about 'value' not cost. Cost by itself is meaningless. You can't buy a house for 5$, and you wouldn't spend $5000 on a loaf of bread. So: "is it worth it?" is the question you should ask yourself. What do you get by doing it, what do you NOT get by NOT doing it? (regarding the potential size - audit tends not to be forever, so you could always implement data purging on your table, to get rid of rows after, say, 12 months so the table doesn't grow indefinitely)
Have you looked at this https://community.appian.com/b/appmarket/posts/audit-history-application as a possible solution? How often will these fields be edited? If the customer is only looking for an audit type capability, the process instances provide a nice way of seeing who changed what and when. We've used Process Models as a nice way to tracking who changed what and when. Depending on how quickly you are archiving your process models, you could build a process report to display that data before it is archived. If there are ever any questions after an instance is archived, process instances could be un-archived.
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.
bryant.st39 Thanks Bryant. Will definitely look into that.