Implement 4 eye approval before changes made to a record (1 Viewer)

Craig.oneill

New member
Local time
Today, 21:04
Joined
Aug 14, 2018
Messages
1
Hi folks

I have been asked to implement 4 eye approval on certain data within a database. Just want to know the best way to go about it. I am a competent programmer so just looking for some advice around the process...

I am going to give users front end access to the records via a form. The first user will make a change to a any of the fields and that will have to be either approved or declined by another user (not the same user).

Should I create a holding table to capture the new values and upon approval move that record from the holding table to the original table? Should I keep it all in the same table but have fields that capture old and new data and manipulate depending on the approve/decline?

I haven’t been able to find much material online so if somebody could point me in the right direction!

Thanks
Craig
 

jdraw

Super Moderator
Staff member
Local time
Today, 17:04
Joined
Jan 23, 2006
Messages
15,364
Have you mocked up anything that you could test and/or show those who would be using the set up? Better to get feedback early on and get users involved.

I hadn't see 4 eye expression but found this link.
I think this was input, then verify in the "olden days".
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 21:04
Joined
Jul 9, 2003
Messages
16,245
I don't know enough about it to advise you. However it struck me that there is already a system called "audit trail" which records changes to fields in a separate table.

I thought it worth mentioning as it might be a place to start.

Sent from my SM-G925F using Tapatalk
 

jdraw

Super Moderator
Staff member
Local time
Today, 17:04
Joined
Jan 23, 2006
Messages
15,364
Colin,
two step verification but each step needs to be done by different people

10-4------that's what I envisaged also.
 

Mark_

Longboard on the internet
Local time
Today, 14:04
Joined
Sep 12, 2017
Messages
2,111
One way to implement what you are asking for is to use an audit trail and have a flag in the changed record. If the flag is set to TRUE, you use a review form that does a lookup into the audit file to show both the current values and the previous values (labels under the controls holding current values) AND a label that shows who put in the edits. If the "2nd pair of eyes", the reviewer, is fine with the changes you have them press a "Commit" button that commits the changes and sets your flag to FALSE.

You will always be able to go back and see what had been in the fields, plus you can easily identify records being changed. Normally this type of validation is ONLY done for a few sensitive fields in a record.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:04
Joined
Feb 28, 2001
Messages
27,001
The Navy had a bunch of "chop chains" that they used for multiple verification. The only system I knew any details about had a "changes pending" table that had all the fields of the main table plus its own PK. Then a child table depending on the "pending entry" PK listed the dates and approvals. When all had been entered, the pending record was overlaid onto the original record and the approvals became part of a change history.

I didn't implement that and didn't have to maintain that so can't tell you the exact structure. But I know they didn't commit the changes until the list of approvals was completed and I know it was a child-table system. Oh, and the "pending change" table's PK was an autonumber, purely synthetic.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:04
Joined
Sep 12, 2006
Messages
15,614
It makes you wonder who asked for this.

Easily the best way is not to do this in the way it's been requested. Limit the users who can change the sensitive data. Add a "requires approval" flag to any edited record.

Produce a form/report for an approving manager, who can inspect the changes, and clear the "approval flag".
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:04
Joined
May 7, 2009
Messages
19,169
some thought:
add another table, for the Approval:

FKID (PKID of record being reviewed)
Approved By (short string)
Approved (Yes/No)
Date (date approved/declined)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:04
Joined
Feb 19, 2002
Messages
42,976
I would use a second table for the pending change. And a third table for the approvals to cover the possibility of the rules changing on how many approvals are required.

I might add a flag to the current table to indicate that a change is in progress.

Also, what happens if someone disapproves? Do all the approvals get deleted and the process goes back to the original author of the changes? I'm not sure the whole procedure is fleshed out enough to firmly fix the schema.
 

jdraw

Super Moderator
Staff member
Local time
Today, 17:04
Joined
Jan 23, 2006
Messages
15,364
I'm not sure the whole procedure is fleshed out enough to firmly fix the schema.
Agree. Too often we see physical data base in place before analysis is complete; and then the work arounds.
 

Users who are viewing this thread

Top Bottom