Database Design Help

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Strategies » Database Design Help

Database Design Help

Rate Topic

Display Mode

Topic Options

Author

Message

ahsanzafars

Posted Thursday, November 19, 2009 2:13 AM

Forum 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

Carolyn Richardson

Posted Thursday, November 19, 2009 3:21 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:00 AM
Points: 1,397, Visits: 2,748

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

ahsanzafars

Posted Thursday, November 19, 2009 3:37 AM

Forum 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

Carolyn Richardson

Posted Thursday, November 19, 2009 7:23 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:00 AM
Points: 1,397, Visits: 2,748

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

Steve Jones - SSC Editor

Posted Thursday, November 19, 2009 8:29 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 5:09 AM
Points: 31,526, Visits: 13,864

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

ahsanzafars

Posted Thursday, November 19, 2009 11:03 PM

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

Permissions