From: "mindmeld.idcomm.com" <etheil@idcomm.com>
Newsgroups: comp.databases.theory
Subject: Archival or Snapshot data (database design)
Date: Tue, 30 Apr 2002 09:22:43 -0600
Lines: 56
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
NNTP-Posting-Host: 216.98.199.32
X-Original-NNTP-Posting-Host: 216.98.199.32
Message-ID: <3cceb672$1@mindmeld.idcomm.com>
X-Trace: 30 Apr 2002 09:21:22 -0700, 216.98.199.32
Path: news.easynews.com!easynews!crtntx1-snh1.gtei.net!paloalto-snh1.gtei.net!paloalto-snf1.gtei.net!news.gtei.net!dimensional.com!pulsar.dimensional.com!207.40.197.76.MISMATCH!mindmeld.idcomm.com
Xref: easynews comp.databases.theory:20605
X-Received-Date: Tue, 30 Apr 2002 08:55:19 MST (news.easynews.com)

I have a design issue and I'm not sure where this should be handled
(Database, Business Logic, etc.). I have a table which currently has foreign
key constraints to a few other tables.
>>
create table Event
(
eventID int primary key,
authorID smallint not null foreign key references Employee(EmployeeID),
venueID smallint not null foreign key references Venue(VenueID)
)

create table employee
(
EmployeeID smallint primary key ,
FirstName varchar(50) not null,
LastName varchar(50) not null
)

create table Venue
(
VenueID smallint primary key,
Name varchar(100) not null,
Address varchar(100) not null,
City varchar(100) not null,
State char(2) not null,
Zip char(5) not null
)
>>
The problem with this setup is that I need a snapshot of information within
the Event table so that changes made to columns within the Employee and
Venue table don't affect the existing records with the event table (like if
venue.name is updated or someone's last name changes).

So, I was going to remove the foreign key references and then simply insert
the values into the Event table (changing the Employee and Venue columns to
varchar).  This leaves the employee and venue tables without any kind of
reference to any other tables wthin the database and I'm not sure if that is
acceptable (I think I've been beat over the head with database relationships
and it makes me nervous to see a table without any kind of relationship).
Heck, I'm not even sure this is the best way to do this. I still want to be
able to enforce the integrity of these values, but it apears the best way to
do this is to pull the character values from the employee and venue tables,
present them to the client and then rely on business logic to insert the
correct values into the event table, disregarding the foreign key
relationships altogether.

Does this make sense? Any suggestions on how this might be handled within
the database (check constraints maybe) or just some kind of validation that
I'm on the right track?

Thanks,

Eric Theil