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).

My questions:

  1. Should I create two tables? I was thinking of applicant and applicant_status, as shown in the schema at the end of the question. applicant contains the information about the applicant, and then applicant_status would 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.
  2. 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 application to applicant again? But this time with a different applicant id.
  3. 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.

enter image description here

  • you need an application table. also please do not preface your table names – Ewan Aug 30 '18 at 6:30
  • @Ewan, Hi! If I understand it correctly, I have two tables. [application] and [applicant]. Thanks. – Dan Angelo Alcanar Aug 30 '18 at 6:44

1) Data structure

If you would have only one table (applicant), then each applicant could only have one status (i.e.the last one). THis doesn't meet the requirements.

With this single table you could however knwow more on historical statuses if you'd have somewhere an employee table that links back to applicant. But this would be cumbersome to use and it would still not meet the requirements of a full log.

So the best alternative to keep track of the full picture is indeed certainly your two tables. They implement a one-to-many relation. A slightly improved option would be to extract the redundant data of applicant_status into a third table status with a status_id and status_name

2) Several applications for the same applicant ?

In your current model, you don't make a distinction between the applicant and the application. If you need to keep track of different applications of a same person, one easy solution would be to extract all the fields that are dependent of the application (i.e. everything but the identity of the applicant) into a new table application

As you now have two different entities (applicant and application) that you need to process, each would have a different id.

The main difficulty in this scenario is not the technical one, but the business one: how to identify that an applicant already registered for an application in the past, so not to create a new record for someone who is already known.

3) Or only applications ?

Due to the main difficulty I mentioned in 2), the approach of only managing applications, regardless of other applications of the applicant, is certainly the more realistic approach.

In addition, there could be even business arguments that support this choice:

  • Why should I know that the applicant already applied in the past ? Example: suppose a candidate applies for an accounting position, and is rejected because he had not the required financial degree; would this historical information make any sense to the processing of a later application of the same candidate to a sales position ? Or to an accounting position if in the meantime he/she got the missing degree ?

  • In countries that have strong data protection rules, is it even allowed to keep such personal data for a very long time, especially if it could create a negative bias later on ?

In order to avoid ambiguities later on, I'd then suggest that you rename the applicant table into application, so that you'd have an application id. It is then more accurate about what your data really represents.

|improve this answer|||||
  • your no. 2 answer is exactly what I am thinking, should I check first if the name already exist? Then what if there is the typo in the name. something like that. The solution in my mind is create the record even thought it is already existing. Then just group by name. But then data redundancy is not good. – Dan Angelo Alcanar Aug 30 '18 at 7:49
  • and in your no. 3 answer, here in our country, there are jobs that doesn't require degree. As long as you can do the job, like warehouse helper, data encoder. So you can apply for different positions. – Dan Angelo Alcanar Aug 30 '18 at 7:53
  • and about the data protection, that is genius. I would ask my manager about that, it makes sense. You are right. Thank You for your answer. I appreciate it. – Dan Angelo Alcanar Aug 30 '18 at 7:56
  • @DanAngeloAlcanar glad if this may help you :-) coming back to solution 2: even with the right spelling, name+surname are not sufficient to identify reliably a person. Googling for my own name I can find at least 50 exact matches of completely unrelated persons; it could be damageable to both, the company and the candidates if you'd confuse them with the wrong person. Adding the address to the criteria increases the risk of mismatch (typos), and it could change over time for a same person. Date and place of birth usually allow a unique match, but in some countries, you might not ask this. – Christophe Aug 30 '18 at 12:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.