CompanyID à Company name, address, city, state, zip, phone, contact first name, contact last name
CompanyID ààOpeningID //Company can have many openings
CandidateID à Last name, first name, address, city, state, zip, phone
// Candidate can fill many openings, can have many qualifications
CandidateID ààOpeningID, Qualification
// A standard list of codes and descriptions
QualificationCode à Description
OpeningID à CompanyID, StartingDate, EndingDate, HourlyPay
OpeningID àà QualificationCodes
// Filled opening
OpeningID à CandidateID, total hours worked
CompanyID, OpeningID à
CandidateID, OpeningID à
CandidateID, Qualification à
OpeningID à QualificationCode
Company, Candidate, Job opening, Qualification
COMPANY: CompanyID, CompanyName, Address, City, State, Zip, Phone, ContactFirstName, ContactLastName
CANDIDATE: CandidateID, LastName, FirstName, Address, City, State, Zip, Phone
JOB_OPENING: OpeningID, CompanyID, StartingDate, EndingDate, HourlyPay, CandidateID
QUALIFICATION: QualificationCode, Description
The notation below is a poor man's ER diagam. The > and < represent crow's feet.
A company can have many openings (and must have at least 1, or it's not in our database), and an opening has exactly 1 company (and it is required).
COMPANY |-|-------------------- |-< JOB_OPENING
An opening can require many qualifications, but must have at least 1. A qualification can be associated with many openings, but does not necessarily have to be associated with any specific opening.
JOB_OPENING >-0------------------ |-< QUALIFICATION
A candidate can have many qualifications (and must have at least 1). A qualification can be associated with many candidates, but does not necessarily have to be associated with any specific candidate.
CANDIDATE >-0----------------| < QUALIFICATION
We do not have to keep track of which jobs a candidate applied for, only the ones for which he was placed. The following reflects placements, not jobs for which an applicant applied or interviewed for. Looking at the right side below, a candidate can fill many jobs (over time), but does not necessarily have to have ever filled a job. Looking at the left side, a job opening can be filled by one candidate, but it may not be filled at all.
CANDIDATE |-0------------------0-< JOB_OPENING
Things to note:
· We have two N:M relationships. These will be handled by creating a new INTERSECTION TABLE using the keys from the two entities in the relationship.