Posted by Erland Sommarskog on 06/25/07 22:08

(nyathancha@hotmail.com) writes:
> 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?

I have a couple of tables in the database that I work with that for
one reason or another have neither in- or outgoing foreign keys. And
I can't say that it keeps me awake at night.

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

In the end what matters a lot is how this data is going to be used. I've
been floating the idea for our app, to have triggers that captures the
after image in an xml column together with table name and keys. Then
it would be simple to write a generic application that reads the log
entries for a certain entity (an account, a customer, whatever) and
displays only the changed columns.

The table would essentially be a database in itself about the rest of
the database.

(Our system also have audit and history tables that do have FK relationships
to the mother tables.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]