Data model for recruitment database with reserve list (1 Viewer)

[JohnLeeS]

Hi all,

I'm pretty new to Access and while I can cope with a basic relational database, I've no idea the best way to structure the following:

My task is to create a recruitment database to track vacancy campaigns, the candidates invited to interview, those successful at interview, and the candidates who are unsuccessful but are held on a reserve list. Candidates from the reserve list can then be selected as successful for another vacancy.

A basic overview of where I'm starting (there will be extra tables for recording Interview/Interview panels etc. But I'll restrict it to where I'm stuck):

TblVacancy
VacancyID
VacancyRole
etc.

TblCandidate
CandidateID
VacancyID
CandidateFirstName
CandidateLastName
Successful (y/n)
ReserveList (y/n)

QryReserveList
TblCandidate[ReserveList] = Yes

I need to know what VacancyID a candidate came from originally, and also what VacancyID they were successful in. For those directly hired, this is easy; I don't know how to record this for those from the Reserve List.

Do I need an extra foreign key in TblCandidate for SuccessfulVacancyID? How would I update this for all candidates? Or is there a completely different and better way to model this?

Apologies if any of the above is confusing. And thank you in advance for any help, it's greatly appreciated!

 

[plog]

Candidates from the reserve list can then be selected as successful for another vacancy.

You need another table---VacancyCandidate. Since a single vacancy can have multiple candidates and a single candidate can go to multiple vacancies you need a table to sort all that out. This is what that table looks like:

VacancyCandidate
vc_ID, autonumber, primary key
vc_DateTime, datetime, will hold when candidate applied for vacancy
ID_Candidate, number, foreign key back to candidate table (CandidateID)
ID_Vacancy, number, foreign key back to vacancy table (VacancyID)
ID_Success, Yes/No, determines if candidate was successful for this vacancy

Also, Successful and VacancyID come out of Candidate table. Those values are now stored in VacancyCandidate. With those changes you can now do everything you need:

-assign candidates to multiple vacancies
-lookup candidates initial vacancy submissiom (via vc_DateTime)

 

[JohnLeeS]

You need another table---VacancyCandidate. Since a single vacancy can have multiple candidates and a single candidate can go to multiple vacancies you need a table to sort all that out. This is what that table looks like:

VacancyCandidate
vc_ID, autonumber, primary key
vc_DateTime, datetime, will hold when candidate applied for vacancy
ID_Candidate, number, foreign key back to candidate table (CandidateID)
ID_Vacancy, number, foreign key back to vacancy table (VacancyID)
ID_Success, Yes/No, determines if candidate was successful for this vacancy

Also, Successful and VacancyID come out of Candidate table. Those values are now stored in VacancyCandidate. With those changes you can now do everything you need:

-assign candidates to multiple vacancies
-lookup candidates initial vacancy submissiom (via vc_DateTime)

Brilliant thank you, I'll give that a go!

 

[Ken Sheridan]

For a binary relationship type like this you'll probably need two separate interfaces, one in which a Candidates parent form contains a subform based on the VacancyCandidate table which models the relationship type, the other in which a Vacancies parent form similarly contains a subform based on the VacancyCandidate table. Data entered or edited in either interface will automatically be reflected in the other.

The attached simple little demo file illustrates this type of model, allowing the data to be viewed either as activities per parent, or parents per activity. In your case the subform would contain multiple columns of course, each bound to a column in the table. There is no need to include a control bound to whichever foreign key column links the subform by referencing the parent form's primary key. The correct value will be inserted into the relevant foreign key column automatically by the linking mechanism when a new row is inserted in the subform.

 

[JohnLeeS]

For a binary relationship type like this you'll probably need two separate interfaces, one in which a Candidates parent form contains a subform based on the VacancyCandidate table which models the relationship type, the other in which a Vacancies parent form similarly contains a subform based on the VacancyCandidate table. Data entered or edited in either interface will automatically be reflected in the other.

The attached simple little demo file illustrates this type of model, allowing the data to be viewed either as activities per parent, or parents per activity. In your case the subform would contain multiple columns of course, each bound to a column in the table. There is no need to include a control bound to whichever foreign key column links the subform by referencing the parent form's primary key. The correct value will be inserted into the relevant foreign key column automatically by the linking mechanism when a new row is inserted in the subform.

Thank you! I'll take a look at this too

Appreciate the help

 

[GPGeorge]

Here's another example of the design pattern for forms to manage many-to-many relationships. It's based on songs and musicians, but the logical structure is similar, as is the interface. https://www.gpcdata.com/downloads/MusicandArtistDemo.zip