The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Auditing approaches

We have designed our database,with tables having createdtime and lastmodifiedtime as 2 columns.My question is,what purpose they serve? Is it a standard approach? The other option is to have an audit table,which records every change.Or,the business requirements should drive what changes need to be tracked.
Saturday, October 06, 2007
I think the business rules should drive the auditing of the tables. Especially if the tables contain personal or confidentail information and more than one perseon has update access to the records of the table. What needs to be captured again depends on the business rules. For example, the action can be recorded e.g. add or edit. For edit actions, additional information can be provided on which of the fields got changed or added.

I too have create timestamps and update timestamps in my application. I suppose this is more applicable in multiuser environments where more than one person can touch the data. I also have field in the record the userid of the person who last updated or created the record. Hope this helps.
Phillip Flores Send private email
Saturday, October 06, 2007
I'd never do this in the software.
That's what SQL Triggers are for in my book.

Then have the software access the separate audit tables when you want to show that data.
Saturday, October 06, 2007
That is interesting, but doesn't really work if you have a web-application and all the users are accessing the database through a common account.

@Phillip Flores:
That is the right approach to take, imho. LEt the business decide these requirements. It is usually good to be able to show created/by and last updated/by fields when displaying a record, but if the business wants more than that, they should let you know.

Of course, "they should let you know" in response to you asking them directly at the early stage of the project what they want.
Entries of Confusion Send private email
Sunday, October 07, 2007
@ Entries of Confusion

Well i'd still do the:

record.EditedBy = currentUser.Id;
record.Edited = DateTime.Now;

in the software.
There would just be triggers listening to edits and deletes and archiving that data.
Jax Send private email
Monday, October 08, 2007
These columns have a way of coming in handy after the fact for stuff like debugging. Also they can sometimes make their way into the application code when you realize you want the latest of something or you want to sort on it for the GUI.
cbmc64 Send private email
Wednesday, October 10, 2007
A little off-topic

This is an earlier thread on the different ways to implement audit trail
Friday, October 12, 2007
"That is interesting, but doesn't really work if you have a web-application and all the users are accessing the database through a common account."

By the way, does anyone know of a technique to access the identity of the current (web) user inside a trigger in TSQL (SQL Server)? As the above poster points out, this can be tricky since database access is through a common account, so built-ins such as USERID() are useless.

In Oracle, there is Proxy Authentication which maps the web-user to an actual database user. It is also possible to use a sys_context call to set the user id from the client/mid-tier, which is then accessible from trigger code.

Anything similar in SQL Server? Or must we wait another 5 years before it catches up with Oracle in this regard? :-)
Thursday, October 18, 2007

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz