Creating a MySQL Database to Track Job Applications

I’m looking for a job with a company who creates a product that I admire.  I want to be part of a talented team.

Organization is critical to the job hunting process. So, like for many things I want to organize in life, I created a MySQL Database. Here’s what it looks like:

ERR Diagram of Job Search Database
ERR Diagram of Job Search Database

Want to create it yourself?  Here is the MySQL code to run:

 


CREATE DATABASE  IF NOT EXISTS `job_search`;
USE `job_search`;

CREATE TABLE `applications` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `job_id` int(11) unsigned DEFAULT NULL,
  `date` date DEFAULT NULL,
  `result` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `job_id_idx` (`job_id`),
  CONSTRAINT `job_id_ap` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`id`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;


CREATE TABLE `companies` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `contact` varchar(100) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `location` varchar(200) DEFAULT NULL,
  `website` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `interviews` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `job_id` int(10) unsigned DEFAULT NULL,
  `date` date DEFAULT NULL,
  `type` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `job_id_idx` (`job_id`),
  CONSTRAINT `job_id_intv` FOREIGN KEY (`id`) REFERENCES `jobs` (`id`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `jobs` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) DEFAULT NULL,
  `company_id` int(11) unsigned DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `salary` varchar(45) DEFAULT NULL,
  `url` varchar(300) DEFAULT NULL,
  `referrer` varchar(45) DEFAULT NULL,
  `description` longtext,
  PRIMARY KEY (`id`),
  KEY `COMPANY` (`company_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

I also created a view that joins fields from 3 tables for easily seeing a list of jobs that I’ve applied to:


CREATE VIEW `applied_for_jobs` AS
    select 
        `jobs`.`title` AS `title`,
        `a`.`date` AS `date`,
        `jobs`.`salary` AS `salary`,
        `jobs`.`referrer` AS `referrer`,
        `c`.`name` AS `name`
    from
        ((`jobs`
        join `companies` `c` ON ((`jobs`.`company_id` = `c`.`id`)))
        join `applications` `a` ON ((`a`.`job_id` = `jobs`.`id`)))

 

The next question is what technology should I use to create a CRUD (create, read, update, delete) interface for this database?  I could just use MySQL Workbench or PhpMyAdmin to insert data, but it would be easier to have a web form that automatically validates the data and updates the database.  Since this is an InnoDB database with foreign keys and multiple tables, doing manual insertion takes some time because first you have to insert into the companies table, and then the jobs table, and then after submitting an application, inserting that into the applications table, and so on.

In the past, I’ve created CRUD interfaces with PHP & Javascript. However, I like to learn something new with each project I take on, so I’m considering learning Angular.js for this task. Do you think that’s a good choice? What’s your preferred framework for building CRUD interfaces for the web?