How Do You Audit? I'm not necessarily talking about system auditing, which I understand to be pretty much like throwing a switch, I'm talking about table level auditing.

Given the following table:

CREATE TABLE t
(
  id NUMBER(15) PRIMARY KEY,
  ssn VARCHAR2(9) UNIQUE,
  last_name VARCHAR2(40),
  first_name VARCHAR2(30),
  dob DATE    
);

The requirement is to track any changes to everything but the SSN. ID is just a surrogate key.

I typically do something like this:

CREATE TABLE t
(
  id NUMBER(15) PRIMARY KEY,
  ssn VARCHAR2(9) UNIQUE,
  last_name VARCHAR2(40),
  first_name VARCHAR2(30),
  dob DATE,
  create_date DATE,
  create_user VARCHAR2(30),
  end_date DATE,
  end_user VARCHAR2(30)    
);

I added the create_ and end_ columns to see who did what when. If someone comes in and decides to change the record, the end_date and end_user are populated (thereby "terminating" the record) and a new record is created with the updated values. That then becomes the "current" record.

I know there are other ways, but I'd like to hear some of your ideas/methods.

Updated
I realized that I should not have put ID as the primary key, I should have used a unique key (SSN) instead but generated a surrogate key (ID). I've updated it to use SSN as the unique key, so the primary key can and will change (it's a surrogate), but the SSN cannot.

Updated II
Then it would fail when you create a new record because the SSN would no longer be unique. Oy, I usually read and re-read anything technically related.

So I'll remove the UNIQUE constraint from SSN and that should do the trick. SSN will be used to find the record of the appropriate person, along with END_DATE IS NOT NULL (a "current" row).

Labels: database, design, development

  ¶ 4/15/2008 07:44:00 PM