At 03:37 PM 3/03/2003 -0500, you wrote:

>I'm setting up a new database, and wondering what the consensus is on this
>situation:
>
>I have a couple of audit columns that are part of every table in the
>database. Those columns are simply Ins_User_ID, Ins_TS, Upd_User_ID and
>Upd_TS, representing the user and timestamp of the insert, and the most
>recent user and timestamp of an update.
>
>I could create a foreign key from each table to the Users table, linking
>the Ins_User_ID and Upd_User_ID to the primary key of the Users
>table. Alternatively, I could add a check constraint (or manually create
>BeforeUpdate/Insert triggers) to do the validation.
>
>Is there a consensus on which way is better? I know that I should not
>create foreign keys to tables that serve as solely as lookup values due to
>index non-selectivity, such as a table holding a few status codes. It
>would seem to be the same case here, but just checking.

Bob,
A reason not to place dependencies on these columns is that, if a
particular user were to be removed from the Users table, relationships
would be broken everywhere. This type of "user-stamping" usually has to be
designed to survive changes to the parent table. You *can* do this with
referential integrity; but I guess you have to decide whether you want the
extra cost of two additional referential integrity checks on every row in
your database.

I consider "stamp" fields (along with other audit trail mechanisms) to be
snapshots of a condition that exists at the moment the data are
created. As a rule, it's important that changes in dependencies don't
affect them.

If users are allowed to enter their own IDs, then the preferred solution
would be to use the triggers for your checks. Even better would be to have
some means to do the stamping automatically in the triggers and disallow
users from subsequently tampering with those columns where they are visible
in applications.

heLen