Thanks for the question, waseem.
Asked: October 21, 2009 - 11:21 pm UTC
Last updated: October 22, 2009 - 1:17 pm UTC
Version: 11.2
Viewed 1000+ times
You Asked
dear Sir,
a)In our web based OLTP application there will be aroud 1000 tables some of these are transcational and some are reference data. We need to keep detail logging of it. If a portion of record is changed by one user and other portion by other user then we should have change track by individual user. Please suggest which option we use for logging.
1. Put logging in text file.
2. Put loggin in proper history tables.
have a fear that storing this logging information in tables will reduce performance.
If you suggest loggin in history tables then should these be populated through triggers or stored procedures?
b)Where we store large objects including pictures/audio etc. In databae or file system.
c)We are planning to persist db tables through Hibernate in order to speed up development. What you suggest should we do it or use stored procedures?
d) In some table we have composite key what you suggest should we use composite key as primary key or single key as primary key
e) is we use natural key (like zip code) as primary key or we use primary key generated from sequence
and Tom said...
If your audit trail is something you need to use, you would of course put it into the database - in a table - so you can
back it up with the other data
restore it with the other data
secure it
query it
use it
... have a fear that storing this logging information in tables will reduce performance. ...
IF YOU HAVE A REQUIREMENT TO LOG IT, it cannot reduce performance, it is part of your specification.
will it go faster without logging it? YES
will it be right if you do not log it? NO
therefore, logging it is as important as the transaction that causes the logging to happen in the first place
PLEASE - stop thinking of an audit trail as something less than the rest of your system. Audit trails are crucial bits of your system
Do you want to see slow? Go ahead, log to a file. You know what you'll need to do in order to do that safely? SERIALIZE ALL TRANSACTIONS (only one guy at a time can write to that file) - and since you need to support rollback, you'll be writing a TON of file code.
Writing to a file for an audit trail would be among the top ten biggest mistakes you can make.
I hate triggers, I would suggest transactional API's written in PLSQL that are used to perform your transactions (not just simple silly table API's but full blown transactional API's).
b) do you need these objects tomorrow? If so, they belong in a database. If you don't care if they exist or not, put them where ever you like.
c) oh good luck with that. Speed up development in order to slow down production is the likely outcome. I don't really care how fast you churn out code if it doesn't scale or perform.
Did you know that most end users consider performance to be a feature? And most developers seem to consider it something for the DBA to deal with...
I would send the developers to some database training first - and slow down development if you want something good at the end.
d) i am a fan of natural keys when they exist and surrogates when they do not. I don't care if the key is more than one attribute, if it is the natural key, use it.
e) see (d)
Is this answer out of date? If it is, please let us know via a Comment