Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-14-2018, 12:41 PM   #1
Craig.oneill
Newly Registered User
 
Join Date: Aug 2018
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Craig.oneill is on a distinguished road
Implement 4 eye approval before changes made to a record

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

Craig.oneill is offline   Reply With Quote
Old 08-14-2018, 12:56 PM   #2
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,574
Thanks: 57
Thanked 1,875 Times in 1,825 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Implement 4 eye approval before changes made to a record

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".
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 08-14-2018, 12:57 PM   #3
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 9,519
Thanks: 363
Thanked 782 Times in 747 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Implement 4 eye approval before changes made to a record

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

__________________
Code:
                 |||||
               @(~‘^‘~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 08-14-2018, 01:13 PM   #4
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,996
Thanks: 92
Thanked 1,715 Times in 1,592 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Implement 4 eye approval before changes made to a record

This phrase came up in a recent thread which might be useful to you: https://www.access-programmers.co.uk...p+verification

I'd never heard the phrase before that but, as I understand it, its basically two step verification but each step needs to be done by different people

Anyway, hope that helps
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 08-14-2018, 02:10 PM   #5
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,574
Thanks: 57
Thanked 1,875 Times in 1,825 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Implement 4 eye approval before changes made to a record

Colin,
Quote:
two step verification but each step needs to be done by different people


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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 08-14-2018, 03:26 PM   #6
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,491
Thanks: 14
Thanked 272 Times in 270 Posts
Mark_ will become famous soon enough
Re: Implement 4 eye approval before changes made to a record

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.
Mark_ is offline   Reply With Quote
Old 08-14-2018, 06:59 PM   #7
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,473
Thanks: 62
Thanked 1,175 Times in 1,075 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Implement 4 eye approval before changes made to 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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 08-17-2018, 10:51 AM   #8
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,462
Thanks: 51
Thanked 949 Times in 918 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Implement 4 eye approval before changes made to a record

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".
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the top right of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 08-18-2018, 06:08 AM   #9
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,847
Thanks: 55
Thanked 2,183 Times in 2,094 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Implement 4 eye approval before changes made to a record

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)
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-22-2018, 11:14 PM   #10
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,433
Thanks: 13
Thanked 1,419 Times in 1,353 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Implement 4 eye approval before changes made to a record

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 08-23-2018, 03:17 AM   #11
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,574
Thanks: 57
Thanked 1,875 Times in 1,825 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Implement 4 eye approval before changes made to a record

Quote:
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.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
lock record after approval steve111 General 7 01-28-2015 10:38 AM
Check if any changes have been made to the record bignate Queries 3 09-23-2013 06:03 AM
Approval for record change 12588sam General 3 11-23-2010 01:26 AM
Can one record set be made into multiple? lhernandez General 1 01-28-2009 02:19 PM
Email approval request with link to approval form oihjk General 1 02-06-2004 05:39 AM




All times are GMT -8. The time now is 06:30 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post

Is Political Correctness Toxic?

Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World