Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Approving Changes Expand / Collapse
Author
Message
Posted Wednesday, January 14, 2009 10:01 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 22, 2010 3:35 PM
Points: 198, Visits: 199
Heyo,

Anyone who's seen my posts on here know's I'm a DBA intern for a big company who's been learning alot about MSSQL Server the past year or so. This is the first time I'm posting a question beyond how to write a particular complicated query, and this is a real stretch for me.

Right now I'm working on an in-house application directory which stores information about applications that can't be queried or otherwise generated. The data includes information such as who wrote the apps, who uses them, what the app does, what other apps it relies on, where it's hosted, etc. When application developers make changes, they have to submit changes to this directory to be reviewed and approved by managers and various other beaurucratic processes.

The problem I have is the higher ups want to view the changes exactly as they will appear in the database before they approve them, and commit the changes to the DB. The other DBA's and I have been discussing exactly how to do this including duplicating all the tables, duplicating the entire database, etc. The changes can be complicated including inserts, updates, and deletes on several tables at once. Does anyone have recommednations or know of any papers online or something discussing strategies on issues like this?

Thanks!


---
Dlongnecker
Post #636414
Posted Wednesday, January 14, 2009 10:21 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 10:20 AM
Points: 879, Visits: 283
You can use different DB comparision tool

see this link http://www.sqlservercentral.com/articles/Tools/64908/


Regards,
Nitin

Post #636435
Posted Wednesday, January 14, 2009 11:02 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 22, 2010 3:35 PM
Points: 198, Visits: 199
Not quite what I was looking for.

What I'm looking for is help designing a database in which changes to any of the tables (inserts, updates, deletes) are not applied until a supervisor/manager of some kind approves them. For example, we could duplicate the entire database, and any rows in the "pending" database can be approved by a manager which would then be moved to the production database. This solution is bulky, and makes changes to the database hard to manage.

All interaction with the data has to be done by end users using a custom application. A DB Comparison tool for DBA's would be inappropriate.

Small use case scenario: Joe wants to list that his application is used by payroll, and logs into an application tied to our database. He finds the record of his application and adds payroll to the list of users. His manager later logs into the same application and finds the change awaiting approval. The manager can then approve the change (and the actual record is updated), or disapprove the change (and the record is not updated).


---
Dlongnecker
Post #636482
Posted Wednesday, January 14, 2009 11:05 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
I would add a flag to the data for "IsApproved" or something like that. Defaults to 0, and can only be changed to 1 by a manager. Queries and such used to report data out of the database would either ignore rows with a 0 in the flag, or would report it as Pending, depending on whether it was a manager viewing the data or not.

I think that's what you're looking for.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #636486
Posted Wednesday, January 14, 2009 11:10 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 22, 2010 3:35 PM
Points: 198, Visits: 199
GSquared, you're on the right track.

A simple pending flag like that wouldn't solve the issue of records that were, updated.

Maybe a multi-option flag, such as
0 = Live or production record
1 = Pending Insertion
2 = Pending Delete
3 = This record is a pending update, and there should be a corresponding record with a 0 flag.

This then runs into issues such as what if someone flags a record to delete, but then someone else flags it for an update and such.


---
Dlongnecker
Post #636489
Posted Wednesday, January 14, 2009 11:33 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Something like that would require a lookup table and some more complex rules, but it could certainly be done.

If you go that way, you might also want to add an audit log to that table, to keep track of when it was originally added, when approved, when marked for deletion, etc.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #636510
Posted Wednesday, January 14, 2009 11:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:27 PM
Points: 7,120, Visits: 15,016
I'd look at implementing the good ole' crab trap technique. In short - operate with 2 structurally identical DB's. One is the one the users can enter into, edit, etc....the other is the "approved stuff". On a specified interval, run a compare between the 2, and allow the higher-ups to approve/deny each of the changes.

Not all that different from GSquared's idea, really. It's just sometimes a little easier to do it that way than to try to shoehorn it all into one single set of objects.....


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #636525
Posted Wednesday, January 14, 2009 12:00 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 22, 2010 3:35 PM
Points: 198, Visits: 199
That solution makes a lot of sense. Here's something I've been thinking about:

I will place a trigger on the database (actually on every table) and anytime a user executes an INSERT, UPDATE, or DELETE I will copy the data from the temporary INSERT and DELETE tables into two tables in the database made to hold that information, and assign a GUID to all the records in these two tables that are related to the users actions. The trigger will never allow the users original INSERT, UPDATE, or DELETE to take place.

whenever a manager logs in, they can then review all the the insert and update tables and approve or deny the changes.

This leads to a question - can I have one trigger at the database level that will fire on INSERT, UPDATE, and DELTE's or do I need a trigger on each and every table.


---
Dlongnecker
Post #636533
Posted Wednesday, January 14, 2009 12:43 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Every table.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #636573
Posted Wednesday, January 14, 2009 12:45 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 22, 2010 3:35 PM
Points: 198, Visits: 199
The pain to wordcount ratio on that post was obscene.

---
Dlongnecker
Post #636576
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse