0

I'm looking for the best practice/ design pattern to permit changes on data in a database.

The requirement is that a person A want to update some data. For example email, address or company name. All the changes from person A are not visible on the webpage until Person B check these changes and confirm that. The data are stored in a database. My question is now what is the best practice/ design pattern for the database? Duplicate the database and copy the data by commit to the other? Only one database and copy the whole dataset with the changed value and tag in that extra column(has to check).

I tried to find something with Google, but I think I don't use the right buzzwords.

My only buzzword was four eyes principal and a solution was workflow engine like camunda or to use dms or ecm.

There has to be simple solution for that problem or is that problem so uncommon?

Thanks for help.

PS.: the user change the data on a website, not directly in the database.

snears
  • 13
  • 1
  • 6
  • I would add a column `verified` into the table (boolean). then you only show rows where this flag is set to `true` . But it also depends on what you really need. For. ex. do you also need to know who verified the content? etc. – name not found Apr 10 '20 at 09:12
  • Does this answer your question? [Optimistic vs. Pessimistic locking](https://stackoverflow.com/questions/129329/optimistic-vs-pessimistic-locking) – Marcinek Apr 10 '20 at 09:13
  • @Marcinek, sry that is not what I'm looking for – snears Apr 10 '20 at 12:09
  • @name not found - I have an extra column for a user that created the dataset and a column for the user who last updated the dataset. I don't need the information for who verified it – snears Apr 10 '20 at 12:11
  • Your situation is uncommon. If person A can change anything, but everything has to be checked, you need duplicate tables for holding the changes until they are approved. – Gilbert Le Blanc Apr 10 '20 at 12:24

1 Answers1

0

You have to design a data structure such as "generic order" or "generic job". Here you will have a master table that describes a job or an order. The fields would be:

job_id
created_at
created_by
job_ident
job_name
permit_at
permit_by

Any job/order has a key/value set of data. Which would be modeled like this:

job_id (fk_job_id)
key
value

Every job must have an implementation. So the interpretation of the key value pairs is up to this implementation. The implementation would be triggered by a user as he permits the changes associated with the current job or order.

The generic model allows you to have one data structure regardless of any table your application has.

You can also draw some inspiration from transaction logs of a database. Please note, that changes have an order to it. So you can detect conflicts.

However you can enhance the data structure above to lock an entity until approval.

You can also have a look on spring-batch where there is a similar data structure and processing.

Please see the apache camel project. Maybe you can use apache-camel as well.

Marcinek
  • 2,144
  • 1
  • 19
  • 25