Approving Changes

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Strategies » Approving Changes

12 posts, Page 1 of 2

1 2 »»

Approving Changes

Rate Topic

Display Mode

Topic Options

Author

Message

dlongnecker-802303

Posted Wednesday, January 14, 2009 10:01 AM

SSC-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

nitinpatel31

Posted Wednesday, January 14, 2009 10:21 AM

SSC Eights!

Group: General Forum Members
Last Login: Sunday, January 18, 2015 4:53 AM
Points: 879, Visits: 285

You can use different DB comparision tool

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

Regards,
Nitin

Post #636435

dlongnecker-802303

Posted Wednesday, January 14, 2009 11:02 AM

SSC-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

GSquared

Posted Wednesday, January 14, 2009 11:05 AM

SSChampion

Group: General Forum Members
Last Login: Tuesday, February 17, 2015 2:14 PM
Points: 13,872, Visits: 9,603

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

dlongnecker-802303

Posted Wednesday, January 14, 2009 11:10 AM

SSC-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

GSquared

Posted Wednesday, January 14, 2009 11:33 AM

SSChampion

Group: General Forum Members
Last Login: Tuesday, February 17, 2015 2:14 PM
Points: 13,872, Visits: 9,603

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

Matt Miller (#4)

Posted Wednesday, January 14, 2009 11:46 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 3:06 PM
Points: 7,227, Visits: 15,975

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

dlongnecker-802303

Posted Wednesday, January 14, 2009 12:00 PM

SSC-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

GSquared

Posted Wednesday, January 14, 2009 12:43 PM

SSChampion

Group: General Forum Members
Last Login: Tuesday, February 17, 2015 2:14 PM
Points: 13,872, Visits: 9,603

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

dlongnecker-802303

Posted Wednesday, January 14, 2009 12:45 PM

SSC-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 »

12 posts, Page 1 of 2

1 2 »»

Permissions