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

Database Design Help Expand / Collapse
Author
Message
Posted Thursday, November 19, 2009 2:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 20, 2009 2:15 AM
Points: 3, Visits: 7
Hello,
I want to design a database in which editing an information requires an approval. and only when the updated information is used when the approver approves it. This means that i have to maintain two copies of data one is the original information( that will be used until the new information is approved) and other is the updated information.
Could anyone please help me on this that how i will be doing this. as i have many tables and i dont want to keep a pending information table for each original table.

Any help in this regard is appreciated.
Post #821404
Posted Thursday, November 19, 2009 3:21 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 12:52 AM
Points: 1,454, Visits: 2,959
You shouldn't necessarily need 2 tables why not one table with something like

Field Type
Item - Int
Version - Int (So one item can be changed and you record) changes
Content - varchar
EnteredDate - DateTime
ValidTo - DateTime (Used to expire changed content)
Approved - Bit (Yes No, 1 or 0)
ApprovedBy - varchar (who approves)


Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists
Post #821451
Posted Thursday, November 19, 2009 3:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 20, 2009 2:15 AM
Points: 3, Visits: 7
Thankyou Carolyn for your abrupt response i really appreciate it, Actually my problem is little complex( may be simpler for you) lets take an example of a table organization. I have almost 40 columns in it. I broke it into two tables Organization 1(containing the 10 columns which are commonly used.) and Organization 2(which contains the other 30 columns which are not commonly used but surely are organization related info.) now when showing to user to edit the information i take the join and display the data. Now applying the versioning concept of yours make it difficult to implement in such scenario for me. Now i have all the tables having the same summary detail concept. Could you please help me in this scenario or suggest a better alternative.
Post #821460
Posted Thursday, November 19, 2009 7:23 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 12:52 AM
Points: 1,454, Visits: 2,959
Its a bit difficult from your example, looks like you have an un-normalised database. If you continue in this fashion you will be forced to duplicate information all over the place, if this is a database that cannot be redesigned then I can be of no help.

In my senario there would be a one to many relationship with the new table.


Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists
Post #821596
Posted Thursday, November 19, 2009 8:29 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 8:42 AM
Points: 33,049, Visits: 15,154
I think you might need to provide more information on your design.

You could still use Carolyn's version concept, add a version to both tables, copy the data on edit to a new row. Or just update the related rows.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #821661
Posted Thursday, November 19, 2009 11:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 20, 2009 2:15 AM
Points: 3, Visits: 7
Thanx Carolyn and Steve for your help.
I will be using a modified versioning concept to enter new rows on edit and updating it to the original row when approved. Also i am now longer breaking my table organization into two different tables.
Post #822109
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse