DB design document

David Merrill dmerrill@lupercalia.net
Wed, 13 Dec 2000 11:13:54 -0500


On Wed, Dec 13, 2000 at 09:54:37AM -0600, Patrick Spinler wrote:
> 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.

I'm also using a field for this purpose, although I call it
transaction_status, and I have different values "D"eleted and "M"odified
(where you use "U"pdate).

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

I am planning on locking down all db access to stored procedures. The
front end shouldn't ever have to worry about the audit trail. Any
reasons why this won't work?

> 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.

Very interesting. I have to learn more about pgs before I go too far.
I could be counting on capabilities that aren't there, and missing
capabilities that are. Postgres *does* have stored procedures, doesn't
it?

I'm going to make a note that we may want to use a separate table. I'm
not entirely sure yet, but it sounds better than my scheme.

-- 
Dr. David C. Merrill                     http://www.lupercalia.net
Linux Documentation Project                dmerrill@lupercalia.net
Collection Editor & Coordinator            http://www.linuxdoc.org
                                       Finger me for my public key

Knucklehead:	"Knock, knock"
Pee Wee:	"Who's there?"
Knucklehead:	"Little ol' lady."
Pee Wee:	"Liddle ol' lady who?"
Knucklehead:	"I didn't know you could yodel"