November 19, 2009 at 2:13 am
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.
November 19, 2009 at 3:21 am
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)
November 19, 2009 at 3:37 am
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.
November 19, 2009 at 7:23 am
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.
November 19, 2009 at 8:29 am
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.
November 19, 2009 at 11:03 pm
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply