Archival or Snapshot data (database design)

From: mindmeld.idcomm.com <etheil_at_idcomm.com>
Date: Tue, 30 Apr 2002 09:22:43 -0600
Message-ID: <3cceb672$1_at_mindmeld.idcomm.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 Received on Tue Apr 30 2002 - 17:22:43 CEST