One to Many Question - Data relationship

[robbeh]

I have a table that tracks information about an employee. We use a simple Form to input the data, to control data entry we use tables for our list items and dropdowns on the form.

Specifically the field in question is the College field in the Employee Info Table.

tblEmployeeInfo
tblCollege - List of colleges

frmEmployeeInput
txtCollege - Control source College Field in the employee table, row source tblCollege

My question is, is there a better way to set this up using a one-to-many relationship? I am just thinking, what if a College changes their name and we change it in our database. Now all the people in the system will have a college name that is "depricated". What would be the standard for this?



Thanks in advance,
Robb

[June7]

Options:

1. create a new record for the new college name and set the old record as 'inactive' so it can be excluded from dropdown list for new record entry

2. save the college name into each record