Multiple field primary key - need advice

[Patti]

I'm creating a database for hiring/recruitment. There will be at least 3
tables in which each candidate will have a record - one for address/phone
info, one for assessment scores and evaluations, etc.

Not all of the data for each candidate will be entered at once (because of
the assessment process) so I need to make sure that we don't inadvertently
open a multiple records for each candidate. I want to make the SSN the
primary key in all three tables, since it's the only unique piece of data,
but I cannot set the field to "allow no duplicates" because the candidate
may apply again at a later date.

What I decided to do was join the SSN with the Application Date to create a
multiple field primary key. Before I proceed, I was wondering if anyone has
any arguments for or against this. Would I be setting myself up for
problems down the road? Any decent alternatives? I really don't feel
comfortable going with an autonumber for the primary key, since I can't
ensure what we won't create dupe records that way.

Thanks in advance,

Patti

 

[Nikos Yannacopoulos]

Patti,

An applicant may apply several times, and may take several tests for each
application; along these lines, I would suggest one table for applicants
with the SSN as PK, a different table fro applications with an autonumber PK
and an SSN foreign key (so you can have a one to may relationships between
the two), another table for test results bound to the applications table
through the application number (the PK in the applications table being a
foreign key in the test results table) etc. In order to prevent double
application entries for an applicant on the same date (or, at least, warn
about it), I would use a bit of code behind the application entry form.

HTH,
Nikos