Db design for approving data updates

Question

I am working on a project in which we need data to be entered or updated by some users undergoing a wait status before adding them to the "live data".

During data preparation, the user can save incomplete records. While the data is pending, we do not want the data to affect the rules imposed on users editing current data, for example. the user working with real-time data should not encounter a unique obstacle when entering the same data that is already in a waiting state.

I assume that the data update set will be grouped into a “data presentation” and the data will be re-checked and corrected / rejected / approved when someone controls the quality of the presentation.

I thought of two data storage scenarios:

1) Saving pending status data in the same table as the current data, but adding a flag to indicate its status. I could see here the problems associated with the need to remove prohibitions or create the necessary fields to support "incomplete" state data. Then there is a problem with the processing of updated data, you will need to add a new row for updating and associate it with an existing "live" row. It seems a little dirty to me.

2) Add new tables that reflect the current tables and store the data there until they are approved. This would allow me to fully control existing live tables, while pending tables can be abused by the way the user feels that he wants to insert them. The disadvantage of this is that I will have many additional tables / SP in db. Another issue that I was thinking about is how the user can link between two records, according to which the record associated with the record can be a record in a living table or one in a pending table, but I suppose in this situations you can always take a copy of the related record and consider it as an update?

None of the solutions seem perfect, but the second seems to be the best option for me - is there a third solution?

Answer 1

Your option 2 is very similar to a better idea. If you want to use referential integrity and all the nice things that you get with the help of a DBMS, you cannot have pending data in one table. But there is no need for unstructured data to be there. The expected data is still structured and apparently you want db to play a role in enforcing the rules even for that data. Even if you haven’t done this, the pending data fits well with the standard table structure.

A separate set of tables is the correct answer. You can transfer the primary key of the row that will be changed to the waiting table so that you know which item is being edited or which item is associated with.

I don’t know your situation exactly, so this may not be acceptable, but the idea would be to have a separate table for storing a lot of corrections that are performed, because then you can control the quality of the lot or send the package for life. Each pending table can have a batch key so you know which batch it belongs to. You will need to find a way to manage multiple pending changes on the same lines (if you want), but this does not seem to be too difficult a problem to solve.

I'm not sure if this works, but it might be worth exploring Master Data Management tools, such as SQL Server Master Data Services.

Answer 2

"Unit of work" is a good name for "data presentation."

You can serialize it to another place, for example, a (non-relational) document-oriented database, and only save it to the relational database upon approval.

Depending on how many restrictions on live data you still need to apply to unauthorized data.

Answer 3

I think the second option is better. To manage this, you can use the View, which will contain both tables, and you can work with this structure through the view.

---

Another good approach is to use an XML column in a separate table to store the required data (due to the unknown number / column names). You can only create one table with an XML column declaration column "Type" to determine which table this document is associated with.

Answer 4

The first scenerio seems good. Add a status column to the table. There is no need to remove the Nullable restriction, just add one function to check the necessary fields based on the flag, for example, If, if the flag is 1 (incomplete). Zero is allowed otherwise. Is not allowed. for the second doubt, you want to add data or update all data.