Re: Database design question - Isolated, unrelated tables

On Mon, 25 Jun 2007 00:08:45 -0700, nyathancha_at_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