Created Date + Last Update (Good design?)

[upstream]

Hey everyone,

im currently designing a database. i usually design my tables with "LastUpdate" field with a smalldatetime data type for tracking purpose. which is whenever user insert or update a row. there's a trigger that updates that specific field.

now, i was thinking to split the information of "created date" and the "last update" of that row. is this advisable or necessary in general? i don't see anything wrong with it except extra data space. i'd like to check some third opinion before proceeding.

Thanks!

P.S im using SQL Server 2008 R2 ;)

[is4u]

Hi,

I think it is fine to keep a minimal amount of audit data in the record itself as you propose.

Often I will have four fields like CreateDate, CreateUser, UpdateDate and UpdateUser with the two user fields being keys into my Users table. Then these values are readily and inexpensively available when needed.

Sometimes more change data is required, and an Audit table of some sort can be used to track the actual field changes made. As this table grows larger, queries can consume a lot of cpu, in addition to the extra disk space.

Of course, there are also the actual database logs for your particular RDBMS.

Good Luck!

[buddylee17]

This should be fine. You will require a little more disk space. Ability to audit is more important than disk space though.

As long as you don't rewrite your application to query this column regularly, you probably won't notice a difference in performance. If you do, modify your indexes to cover the new column.