I am developing an Online Application portal. But I am stuck in designing my database tables, and am unsure about the best approach to take.
Background: how the portal is supposed to work
Let me tell you a scenario and how the portal works.
Sam is applying as data encoder. So he go to the online application portal, fill out the application form, and then submitted it.
Now, the default application status of Sam's application would be for examination.
The HR staff would then login to the portal, checks Sam's application, and then change the status to for initial interview.
The goal is to track Sam's(and every applicant's) application status from applicant to employee(if he passed).
- Should I create two tables? I was thinking of
applicant_status, as shown in the schema at the end of the question.
applicantcontains the information about the applicant, and then
applicant_statuswould be populated every time the HR staff changes the status of the applicant in the portal. This way I can track/log the application status of the applicant.
- Let's say I have implemented no. 1 question. What if the applicant
applied twice but for different position? Should I then insert his/her
applicantagain? But this time with a different applicant id.
- Or should I treat every application as unique even the applicant is the same person although different position?
Hope you can enlighten me with this. Any suggestion would be highly appreciated. Please see the screenshot so you can see my current table design.