Relational Database question

Hi,

Maybe this is just me overthinking or not thinking properly.

My question is as follows

suppose you have a simple relationship

employee object
  (emp_id,
   name,
   dept_id)

department
(dept_id,

  dept_name,
   timestamp) - with dept_id being the primary key

request
(employee_id,

  request_type....
  timestamp)

And suppose you choose to either save the dept_id at the instantantiation of the request record or not, but if you want to somehow record which department the employee was in at the time of the request. You could choose to put the dept_id in the request record. My question is......

If after the request entry was inserted, and then the look-up table department is changed, then a subsequent lookup against that dept_id will return a different name that what was valid at the time of the insert.

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.

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?

thank you for any responses

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

David Received on Mon Jun 05 2000 - 00:00:00 CDT