DB design document

[Patrick Spinler]

David Merrill wrote:
> 
> Some of the issues I'm dealing with:
> 
> 4. Auditing mechanism needs to be established. I wrote a proposal.

David:

I use an auditing method very similar to yours with two exceptions - I
save the auditing history in another table, therefore the auditing
flags, timestamp, and username only exist in this history table.  Makes
the design a little cleaner, IMHO.  Oh yeah, and I use the auditing flag
to indicate what kind of transaction caused this audit record, e.g. "U"
record updated, "D" record deleted, etc.

Then the application can do whatever operatiosn it wants on the base
table; updates, deletes, inserts, etc.

Postgres table inheritance works nicely for this, too.  You can have the
history table inherit all the elements of the base table +username,
timestamp, & audit function.  Then if desired you can do a single select
that includes both the base table and it's children.

-- Pat

-- 
      This message does not represent the policies or positions
	     of the Mayo Foundation or its subsidiaries.
  Patrick Spinler			email:	Spinler.Patrick@Mayo.EDU
  Mayo Foundation			phone:	507/284-9485