Re: Relational Database question

> Would a request record retrieval with a lookup of the dept_id
> foreign key also need a timestamp so that the correct value
> of the department name would be returned rather than the
> current value of the department name for this ID.

As you have suggested, your current schema does not keep track of "versions" of data. If the dept name changes, then joining the requests with the departments will produce a potentially incorrect department name. The question that you have to answer is wether or not keeping versions is important to you. If it is, then you'll have to change your database design slightly to capture the changes in versions. This is done all the time especially in data warehouses.  

> Would this mean that all lookup tables should really have a version
> controlled audit system in place. So a lookup of just dept_id
> wouldn't return a correct snapshot of the value of dept_name
> at the time the request record was inserted into the database?

All lookup tables do not have to keep versions. This is dependent on your application requirements. Some applications do not have this requirement so versions are not important.  

> is this highlighting a deficiency of using non-intelligent
> sequential numbers as primary keys rather than real values?

I would say that this is more a case of improper design for the business rules you are trying to capture. Sequential numbers are fine for PKs as long as you understand that there is no explicit relationship between the number and the row.

HTH,
Brian

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================