|
|
Posted by dmarkle on 01/15/07 23:32
I apologize if I sound celkoesque (you can say you were there when I
coined this term) but...
I've seen a lot of tables like this. They suffer from a couple of
problems:
1) They're not typed. Who's to say that everything in your database
will be smaller than VARCHAR(255)? What happens if someone changes the
date format of the machine?
2) They usually indicate that the requirements of the application being
designed haven't been really scrutinized. What auditors generally want
to see is something along the lines of: "Tammy Jones put a SELL order
on AAPL at 86.49", not a bunch of individual column changes.
2.1) They don't tie together bits of a more complex transaction into
one cohesive whole. It's hard to see what LOGICALLY happened to make
the data change in the database. That's a lot more important when
you're doing things like trying to make sure people aren't doing what
they aren't supposed to do (which is the whole purpose of auditing,
right?)
3) They tend to become massive bit-buckets of write-only data, which
may just be the bane of my existence. Before going to this level, the
application designer really needs to look at the system requirements.
Who's going to be reading this data? How are you going to reconstruct
it for the reader in a meaningful way?
That being said, you *can* do this -- it will (most of the time)
"work". I've been forced to make tables like this every now and then,
and the result is just about always the same. What should be a 100MB
database becomes 10+GB of stale data that the auditiors frankly don't
care about at all. I'm not saying that you should never implement
designs like this. I'm just saying that a design like this must be
done only under *very* careful consideration -- not because the
designer didn't want to study the system's requirements and felt that
they'd be 'safe' by just 'auditing everything'.
-Dave
Ed Murphy wrote:
> dmarkle wrote:
>
> > I'm not so sure about this design, though. In the long run, you may
> > find it to be quite limiting. I usually recommend against doing
> > auditing on the database level whenever I can -- I prefer to audit the
> > actions that users do in the application layer.
>
> Auditing on the database level may be necessary if you can't alter the
> application, or don't know which part is responsible.
>
> > Not to mention the
> > fact that I think your design will be slow and difficult to query when
> > the time comes to use it.
>
> I don't see anything particularly wrong with the audit table, assuming
> proper indexes.
Navigation:
[Reply to this message]
|