Database Design Help

  • 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.

  • 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

  • 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.

  • 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

  • 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.

  • 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