relational design question

[livefree75]

Hi - I have a tool that allows managers to nominate their fellow
employees for an award. Part of the nomination is specifying the
education of an employee. This data can change year to year, and I
want to keep track of the data as it existed in each year.

I have a nominations table:
* nomination_id
year
candidate_id
nominator_id

And I have an education_entries table:
* ee_id
school
year
degree

And I have a nom_education_entries table:
* nom_id (FK to nominations.nomination_id)
* ee_id (FK to education_entires.ee_id)

My intent is for each education_entries row to be able to be used for
multiple nominations, but for only one employee. I don't want
nominations for different employees to use the same education_entries
row.

What's the best way to alter the design of this to do what I need?

[Fred.]

I think you need to think in problem terms
for a while before you go back to data. What
is obvious is that there can be muliple
educational reports from the same or over the
years different sources, and they can be
redundant or in conflict.

What you really need to come down to is a
set of education records for each nominee
with conficts and redundancies resolved.

So my shot would be that you really need
two education tables, one for reports
where each record contains a foreign key
to the reporting nomination, and one for
resolved records where each record
contains the nominee_id, which I assume
remains the same from year to year.

Of course if you are going to resolve
these conficts in the data entry process
you can drop the table for educational
reports. If entering from paper, you
will need to file the paper anyhow.

Fred.