Implement 4 eye approval before changes made to a record

C

Craig.oneill

New member
Local time
Today, 16:07
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
 
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".
 
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
 
Colin,
two step verification but each step needs to be done by different people
Click to expand...

10-4------that's what I envisaged also.
 
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 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.
 
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".
 
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)
 
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.
 
I'm not sure the whole procedure is fleshed out enough to firmly fix the schema.
Click to expand...
Agree. Too often we see physical data base in place before analysis is complete; and then the work arounds.
 
You must log in or register to reply here.

Similar threads

C
Cant Update Record
Replies
19
Views
639
Pat Hartman
P
N
Solved How to access the server resources from the internet on a different network
Replies
6
Views
584
Minty
Minty
D
Solved Normalization - Struggles In Access
2
Replies
25
Views
1,248
MajP
MajP
N
Solved How to update a currentstock control in the subform after a requery from the parent form
Replies
16
Views
777
Mark_
M
A
Solved Referring to a control on a subform from the parent form.
Replies
3
Views
475
AlliCarr
A

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom