Professional Web Applications Themes

Database design/approach question - transaction journal - ASP Database

I have a small ASP app on our intranet that handles the internal contact list. Nothing special, some tables for the department and employee details. I now have a request for providing a history of changes. Currently I capture the user ID making changes and the time, and store them in fields in the record. This came about because of the (I guess inevitable) question "Who changed that?" Now I need to keep a history/transaction log of changes so I can answer the question "Who changed that before the guy who changed it before the current change was made...?" I've ...

  1. #1

    Default Database design/approach question - transaction journal

    I have a small ASP app on our intranet that handles the internal
    contact list. Nothing special, some tables for the department and
    employee details. I now have a request for providing a history of
    changes.

    Currently I capture the user ID making changes and the time, and store
    them in fields in the record. This came about because of the (I guess
    inevitable) question "Who changed that?" Now I need to keep a
    history/transaction log of changes so I can answer the question "Who
    changed that before the guy who changed it before the current change
    was made...?"

    I've dealt with transaction records for a retail operation years ago
    (DBase IV), and I don't want to get that involved in this. I'm not
    dumb enough to add fields for the last three changes to the records
    though. So I'm looking for suggestions on how others might handle
    this.

    So far I only need to do this on the employee details, so I'm thinking
    something like:

    Current tblEmployeeDetails structure (SQL 2000):

    EmployeeID, IDENTITY, PK
    EmployeeFirstName
    EmployeeLastName
    EmployeeDepartment, FK
    EmployeePhone
    EmployeeCell
    EmployeeLocation
    LastUpdateTime
    LastUpdateUser

    I'm thinking of dropping the LastUpdate fields and creating a
    Transaction History table, as:

    EmployeeID, IDENTITY, PK
    EmployeeFirstName
    EmployeeLastName
    EmployeeDepartment, FK
    EmployeePhone
    EmployeeCell
    EmployeeLocation
    UpdateTime
    UpdateUser

    Then I just need to insert a record each time a change is made to the
    employee details table. My current skillset would be to just use a
    second Query for this insert, but I assume a trigger or making the
    entire insert/update part of a stored procedure would be a better
    option. I've been going through the code for the Northwind database
    for examples and should be able to muddle through (with occasional
    help from the SQL group...).

    Does this make sense to approach it this way? Is there a reason to
    use ASP for the entire process and not stored procedures? Other than
    it's a learning curve for me to get a handle on the stored procedures
    and possibly triggers?

    Thanks,

    Jeff
    Jeff Guest

  2. #2

    Default Re: Database design/approach question - transaction journal

    "Jeff Cochran" <com> wrote in message
    news:microsoft.com... 

    How about just creating a "tblEmployeeDetailsHistory" table identical to
    "tblEmployeeDetails" but with a new AutoNumber field simply called "ID".
    Before a change is made to the original table copy the row to the History
    table. Subsequently, you can query the original and History tables with
    "ORDER BY EmployeeID, LastUpdateTime DESC" to see what changed and when.


    McKirahan Guest

  3. #3

    Default Re: Database design/approach question - transaction journal

    Jeff Cochran wrote: 
    Take a look at
    Time After Time (SQL Audits and Historic Views)
    http://www.standardreporting.net/survival/view.aspx?_id=53431
    which outlines techniques for creating audit trails.

    Good Luck,
    Michael D. Kersey
    Michael Guest

  4. #4

    Default Re: Database design/approach question - transaction journal

    On Tue, 31 Aug 2004 01:28:07 -0500, "Michael D. Kersey"
    <org> wrote:
     [/ref]
     

    Excellent, thanks. Gives me just what I need to work through this.

    Jeff
    Jeff Guest

Similar Threads

  1. database design and asp question
    By Michael in forum Dreamweaver AppDev
    Replies: 2
    Last Post: June 27th, 09:23 AM
  2. Advice on design approach (please!)
    By Erik J Sawyer in forum ASP.NET Building Controls
    Replies: 2
    Last Post: September 7th, 05:01 AM
  3. Database Design Question !!
    By Adam Knight in forum ASP Database
    Replies: 3
    Last Post: August 14th, 08:45 AM
  4. Advice on design approach and principles
    By Mr Gordonz in forum ASP.NET General
    Replies: 1
    Last Post: August 4th, 10:08 PM
  5. Database design/optimization question
    By AMIT in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 1st, 07:02 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139