I have to design a CRUD application for my job. Unfortunately, if a user changes some data in a database row, the changes cannot be instantly committed to the database. A super user should be notified when changes are made to a table row. The super user then logs in and verifies that the changes made to said row are OK. The super user then approves the changes and they are committed to the database.
My question lies in how to store those changes until the super user can log in to approve the changes? I’ve thought of temp tables, writing the changes to files, etc, but none of that really seems like a great idea.
Why not to add another pair of columns to the table: PENDING_STATUS (boolean) , PENDING_TIME (datetime) and PENDING_ID ( your OK type ) - this way, when user changes any data - a new row is created with the activated PENDING_STATUS flag. PENDING_ID is also set with the ID of an actual row being editied, PENDING_TIME - stores the edit attempt time.
When superuser logs-in, his “todo” list could be a query results for rows with PENDING_STATUS=true. When data is validated, it is copied to actual row (ID is taken from PENDING_ID) column and the pending temporary row is deleted. This will even allow multiple edits of the same row - they will be present in supervisor’s todo list in chronologically order.
You can also add more PENDING_* columns, such for storing user id (who performed an edit attempt) and so on… You can also move this information to a separate table, so your original table will contain only a “PENDING_STATUS” column to mark rows as temporal and exclude them from other business queries and all other editing data - actual row id, editing user, editing time, etc… will be in a separate table, linked with FK.
Yeah, not really a vaadin issue. Personally, I’d keep this in a pending table like Dmitri suggested towards the end so that your main “real” table doesn’t have to carry baggage about such pending matters that really are not related to the table itself. Then you can keep all sorts of extra (perhaps optional) info about the pending change, including a reason for making the change. You can even then keep an approval/deny status should you want to keep those change requests over time so you can go back and see what was done over time.