Posted by Tom Cooper on 06/26/07 00:29

In addition to the other replies, I would add that foreign key constraints
are just one of many tools the database designer can use to help ensure that
bad data does not get placed in your database. Other tools include check
constraints, using the right datatypes (eg, store dates in a datetime
column, not a varchar column), sometimes triggers, etc.

So an important question is what the consequences will be if (when!, my
experience is if bad data can be put into a database, sooner or later, it
will be) invalid data is put into your audit table(s). That might range
from nobody really cares, to it's going to be a lot of work to fix it, to
somebody (you?) gets fired, to your company would be subject to a
significant fine, to somebody might go to prison (if, for example, your
audit trail is being used to prove compliance with SOX). So ask yourself
questions like what will happen if your boss comes to you and says the audit
trail says that user x created project y at time z, but there is no project
y in the system.

I certainly have tables in databases I have designed that do not have any
foreign key relationships to other tables, but before implementing one, I
would always think carefully about it.

Tom

<nyathancha@hotmail.com> wrote in message
news:1182755325.216207.318140@g37g2000prf.googlegroups.com...
> 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.
>

[Back to original message]