I have a scenario where any update/change in the data by a cms user through application/CMS needs the approval of the admin/authorizer user. There may be multiple changes in one update in a single document/record. This approval will not be done in real-time and may take few hours or may be days. Authorizer may also reject the change. So in this case what would be the best way to keep this data alive without comitting it to the database till approval or rejection. Should I create temporary or duplicate tables to keep this data temporarily in the db? But this will result in large number of temporary tables(one for each table). Or is there any other option at developer/application/java end? I am using here Oracle with Java.

CodePudding user response：

You can always have draft and final copy of the data. Draft copy will save your work in draft mode, committed and operation like save / confirm from app can copy this into final version.

This requires one more record to identify draft / final version and you should be using draft data to show on UI.

CodePudding user response：

You need to better understand the problem. You do not require one datastore, you require two datastores.

Datastore one (possible table one) will contain unapproved changes. This is the "proposed" state. You will write and commit all data into this datastore as soon as the user requests the change.

Datastore two (possible table two) will contain the approved changes; this is the "real" state. Once a change that is in datastore one has been reviewed and approved, you must apply the change here.

A possible other solution is to use a Kafka topic:

1. Use a Kafka topic to store the unapproved changes.
2. Feed the topic to reviewers.
3. When approved, note the decision (in the same topic) and write the change to the database.

Note:
datastore 1 and datastore 2 can be the same table, just have a column to indicate "approved change", "declined change", and "pending change".