|
|
Posted by Hugo Kornelis on 06/25/07 22:47
On Mon, 25 Jun 2007 00:08:45 -0700, nyathancha@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?
Hi nyathancha,
It can happen, but it's definitely not common. For me, it would be a
reason to look again, but not to dismiss the design right away.
I have once encountered a situation where I needed unrelated tables.
This had to do with auditing, but not at all like the method you are
proposing - in fact, I don't really like what I think you're trying to
do. Having one table to log "everything" shares many of the problems of
the EAV design - you'll be creating a very generic table with a few very
generic columns. They can hold everything, making it virtually
impossible to constrain or query the data in the table. If this is the
kind of audit tale that should normally never be used but is only kept
for the 1 in a million chance of a completely unforeseen disaster, and
wasting countless man hours to sift manually through the collected data
is an acceptable price to pay in that situation, than this design MIGHT
be considered. In all other cases, I'd steer away from it and go for a
more constrained design.
The situation where I had to use unrelated tables was at a firm that had
to keep a full record of changes for some tables - so for each of those
tables, a history table was made with all the same columns, plus a
datetime (as part of the primary key), userid of who made the change,
etc. We then added triggers to the main tables to ensure that each
modification in those tables was properly recorded in the corresponding
history table. But we did NOT define any foreign keys, for the simple
reason that after e.g. a deletion of a customer, the change history of
that customer still had to be kept on file; we couldn't remove the
customer from the history table, and a foreign key to the customers
table would have prevented the DELETE.
Another example of a design with an unrelated table that I never used in
practice but can imagine easily enough, would be a single-row table to
hold an application's "processing date" (so that a batch that runs past
midnight can all be processed as if all was on the same date, and that
actions can be "redone" [or tested] on a simulated dy - I have worked
with such systems back in my mainframe PL/I programming days, but they
used flat files rather than databases <g>).
>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!)
For examples of good design, please don't look at any Microsoft supplied
samples. Even though AdventureWorks is miles ahead of Northwind and
pubs, it's still filled to the brim with bad practices.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Navigation:
[Reply to this message]
|