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