Results 1 to 4 of 4

Designing Relationship between Applicants, Employees, and Positions

  1. #1
    skidawgs is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    20

    Question Designing Relationship between Applicants, Employees, and Positions

    Hi there,

    I am creating a database which stores Applicant/Candidate data, Employee data, and various different positions. Currently, I have all Candidate/Applicant and Employee Data stored within a Single table, which is then in a Many-To-Many relationship with the Position Table. My question is, I have received some insight that splitting my PERSON table into two separate tables may be beneficial, and wanted to get some feedback on this thought. Also, If I split the table into two (CANDIDATE and EMPLOYEE Tables) then how should these two tables be connected in the Many-To-Many relationship with POSITIONS. For Example, should CANDIDATE be connected to to the CANDIDATE_POSITION Join table, with EMPLOYEE being a Optional One-To-One? Or Should I have the Employee Table linked directly to the Join Table directly? Or maybe both CANDIDATE and EMPLOYEE tables should be connected to the Join table between Position?
    Any insight would be greatly appreciated, thank you!

    Thanks,
    Skid

  2. #2
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,638
    I have received some insight that splitting my PERSON table into two separate tables may be beneficial,
    from what you have said, on the basis that employees can be applicants then I don't see any benefit in splitting the table. However, if you are an employment agency where employees mean the agency employees and candidates/positions are external to the agency (i.e. clients) then table should be split.

    Person table needs an 'isEmployee' flag - often this might be a employment start date/end date rather than a checkbox

  3. #3
    skidawgs is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    20
    Hi Ajax,

    Thank you for your advice. From your explanation I think keeping a single Person table will be best. Since I am already storing Start/ End Date within the Join Table between a Person and their Position, would you still recommend using a isEmployee Flag?

    Also, I'm assuming that storing Start/ End Date should be in the join table since it is specific for an individual person and that specific position. For Example a person would have various different start/ End Dates being stored if they are rehired on another position.

    Thanks Again!

  4. #4
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,638
    Since I am already storing Start/ End Date within the Join Table between a Person and their Position, would you still recommend using a isEmployee Flag?
    no - I was saying start/end dates are better.

    Also, I'm assuming that storing Start/ End Date should be in the join table since it is specific for an individual person and that specific position.
    without knowing what exactly your app is required to do, difficult to advise. but most likely - on the basis that someone is always in a position (and therefore an employee) until they leave the last one. The same dates can also be used to determine if a position is vacant and it's vacancy/filled history

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 10-25-2016, 03:58 PM
  2. Replies: 1
    Last Post: 08-20-2015, 05:28 AM
  3. Field Positions
    By MaineLady in forum Access
    Replies: 3
    Last Post: 07-25-2015, 05:18 AM
  4. Replies: 2
    Last Post: 07-31-2014, 05:45 AM
  5. Applicants Database
    By Rauf in forum Database Design
    Replies: 15
    Last Post: 05-25-2013, 02:20 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums