How to make tasks double-checked (the way how to store it in the DB)?

Question

Hello all!

I have a DB that stores different types of tasks and more items in different tables. In many of these tables (that their structure is different) I need a way to do it that the item has to be double-checked, meaning that the item can't be 'saved' (I mean of course it will be saved) before someone else goes in the program and confirms it.

What should be the right way to say which item is confirmed:

1. Each of these tables should have a column "IsConfirmed", then when that guy wants to confirm all the stuff, the program walks thru all the tables and creates a list of the items that are not checked.
2. There should be a third table that holds the table name and Id of that row that has to be confirmed.
3. I hope you have a better idea than the two uglies above.

Answer 1

Is the double-confirmed status something that happens exactly once for an entity? Or can it be rejected and need to go through confirmation again? In the latter case, do you need to keep all of this history? Do you need to keep track of who confirmed each time (e.g. so you don't have the same person performing both confirmations)?

The simple case:

ALTER TABLE dbo.Table ADD ConfirmCount TINYINT NOT NULL DEFAULT 0;
ALTER TABLE dbo.Table ADD Processed BIT NOT NULL DEFAULT 0;

When the first confirmation:

UPDATE dbo.Table SET ConfirmCount = 1 WHERE PK = <PK> AND ConfirmCount = 0;

On second confirmation:

UPDATE dbo.Table SET ConfirmCount = 2 WHERE PK = <PK> AND ConfirmCount = 1;

When rejected:

UPDATE dbo.Table SET ConfirmCount = 0 WHERE PK = <PK>;

Now obviously your background job can only treat rows where Processed = 0 and ConfirmCount = 2. Then when it has processed that row:

UPDATE dbo.Table SET Processed = 1 WHERE PK = <PK>;

If you have a more complex scenario than this, please provide more details, including the goals of the double-confirm process.

Answer 2

Consider adding a new table to hold the records to be confirmed (e.g. TasksToBeConfirmed). Once the records are confirmed, move those records to the permanent table (Tasks).

The disadvantage of adding an "IsConfirmed" column is that virtually every SQL statement that uses the table will have to filter on "IsConfirmed" to prevent getting unconfirmed records. Every time this is missed, a defect is introduced.

In cases where you need confirmed and unconfirmed records, use UNION.

This pattern is a little more work to code and implement, but in my experience, significantly improves performance and reduces defects.