Search code examples
mysqldatabasedatabase-designentity-relationship

Database Model for Job Application Portal

I need to implement a Job Application Platform for a fictive company in a University Project.

The Website is for a single company, not multiple companies like indeed.

Every potential applicant has to make an account and then he is able to apply for the jobs he wants to.

I came up with a model for the database and wanted to ask for feedback and potential improvement since I am very uncertain if it is good enough.

https://i.sstatic.net/5DVWB.png

The tables are in German so here is a translation:

Job advertisements (upper left)

  1. ID (PK)
  2. Department
  3. title
  4. Startdate of application time (if this date is reached the advertisement is visible on the website)
  5. Enddate (if this date is reached it automatically gets hidden)
  6. Description
  7. Type of job (internship, full-time etc)
  8. isActive (boolean if the adv. is visible)
  9. Location
  10. contact
  11. CV
  12. Anschreiben, Zeugnisse & weitere Doks is a boolean
    -> when it is true those documents are required
    -> release (HR has to check every advertisement and has to release them)
  • Employees (upper middle)
  1. ID
  2. Firstname
  3. Lastname
  4. Email
  5. role
  • External registrations (upper left - all accounts who register for the job portal)
  1. ID
  2. Firstname
  3. Lastname
  4. email
  5. phonenumber
  • Applications (lower middle table)
  1. ID (PK)
  2. Job advertisement ID (FK)
  3. Applicant ID (FK - ID from external registrations)
  4. Firstname
  5. Lastname
  6. email
  7. phonenumber
  8. status (shows if applications is being looked at and stuff)
  9. Files (boolean to check if a zip file was uploaded with the application)

So my thought behind this model were that if some applies for a job & fills out the application form the userID of this account & the advertisement ID of the particular job get saved into the database "applications". This way it is always identifiable who applied to which job.

Solution

  • JobAdvertisement

    • If IsActive is only related with StartDate and EndDate you should remove it and just check if inside time period.
    • Consider modelling Department, Location, Contact using additional tables.
    • Try finding a key and remove the surrogate
    • Consider using a reference table for the TypeOfJob attribute.

    Application

    • Remove the surrogate ID and just use a composite PK that consists of the JobAdvertisement and ExternalRegistration keys.
    • Remove FirstName, LastName, Email, PhoneNumber. You have all this in ExternalRegistration.
    • Consider using a reference table for the Status attribute.