|
|
Posted by Tim on 06/25/07 10:07
On 25 Jun, 08:08, nyathan...@hotmail.com wrote:
> Hi,
>
> I have a question regarding best practices in database design. In a
> relational database, is it wise/necessary to sometimes create tables
> that are not related to other tables through a foreign Key
> relationship or does this always indicate some sort of underlying
> design flaw. Something that requires a re evaluation of the problem
> domain?
>
> The reason I ask is because in our application, the user can perform x
> number of high level operations (creating/updating projects, creating/
> answering surveys etc. etc.). Different users can perform different
> operations and each operation can manipulate one or more table. This
> part of the system is done and working. Now there is a requirement to
> have some sort of audit logging inside the database (separate from the
> text based log file that the application generates anyway). This
> "audit logging" table will contain high level events that occur inside
> the application (which may or may not relate to a particular
> operation). This table is in some sense related to every other table
> in the database, as well as data that is not in the database itself
> (exceptions, external events etc.). For example : it might have
> entries that specify that at time x user created project y, at time A
> user filled out survey B, at time C LDAP server was down, At time D an
> unauthorized login attempt occurred etc.
>
> As I said, these seems to suggest a stand alone, floating table with a
> few fields that store entries regarding whats going on the system
> without any direct relationship to other tables in the database. But I
> just feel uneasy about creating such an isolated table. Another option
> is to store the "logging" information in another schema/database, but
> that doubles the maintainance work load. Not really looking forward to
> maintaining/designing two different schemas.
>
> I had a look at the microsoft adventureworks database schema diagram
> and they seem to have 3 standalong tables - AWBuildVersion, ErrorLog
> and DatabaseLog (unless i am reading it wrong!)
>
> Any advice, Information or resources are much appreciated.
Hi Nyathan,
In general terms it is quite acceptable to have a standalone table
with no FK relationships instansiated.
Indeed, in times gone by whole databases were created in this manner
as the overhead for OLTP with all the index data manipulation behind
the scences could bring a system to its knees. (PK & FK are backed by
'hidden' indexes).
As regards your specific task, (and here you'll realise I'm not from a
SQL Server background), check to see if there is an existing audit
ability in the rdbms.
Some rdbms allow for tracking all the activity on a table or all the
activity of a certain user, some allow both and some changed from one
to the other, (rats).
If you need to create your own system then think generic.
A single table with datetime stamp, username, table effected, action
taken, and the say 100 columns of varchar 100.
Each table you are seeking to audit will need triggers, ( insert,
update, delete), that calls a genric stored procedure that populates
the table you have created.
If your going to be doing this for a lot of tables or repeatedly I'd
advise writting a little noddy program to get the source table column
information from the systables and then generate the triggers.
The triggers will need to cause the stored procedure to write away
both the before and after image of the data.
BEWARE of bulk updates or deletes, ( best to disable the triggers
before you do them).
Also archiving / purging of your generic table becomes interesting,
depending on activity levels.
I would not recommend the above for busy tables.
Hope that helps, Tim
Navigation:
[Reply to this message]
|