Path: csiph.com!x330-a1.tempe.blueboxinc.net!newsfeed.hal-mli.net!feeder3.hal-mli.net!newsfeed.hal-mli.net!feeder2.hal-mli.net!news.glorb.com!postnews.google.com!do4g2000vbb.googlegroups.com!not-for-mail
From: livefree75 <jpittman2@gmail.com>
Newsgroups: comp.databases.ms-sqlserver
Subject: relational design question
Date: Wed, 11 Jan 2012 07:24:51 -0800 (PST)
Organization: http://groups.google.com
Lines: 27
Message-ID: <390061ca-db22-4194-a2de-fd1f19e76740@do4g2000vbb.googlegroups.com>
NNTP-Posting-Host: 199.46.198.231
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
X-Trace: posting.google.com 1326295956 31268 127.0.0.1 (11 Jan 2012 15:32:36 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 11 Jan 2012 15:32:36 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: do4g2000vbb.googlegroups.com; posting-host=199.46.198.231; posting-account=6yZ3kQoAAAAqnfySR_78_sxYCCi-GF6d
User-Agent: G2/1.0
X-Google-Web-Client: true
X-Google-Header-Order: ASELNKCHRU
X-HTTP-UserAgent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:9.0.1) Gecko/20100101 Firefox/9.0.1,gzip(gfe)
Xref: x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:898

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?