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?

 

Anatomy of an Amazon Lookup Program for Online Arbitrage

I designed and coded a program that queries the Amazon Product Advertising API. It records the prices and sends an alert if the price meets a certain threshold. This might sound like a pretty simple idea, but there is actually a lot to consider.

Program Requirements

  • Retrieves the price data for thousands of items as frequently as allowed
  •  Sends a mobile alert and updates a website when a price match is made
  •  Allow for easy removal, addition and editing of “watched” products
  •  Displays graphs of historical price data, as well as sales rank
  • Program needs to start up automatically and run for long periods of time without any stability issues.

Additional Challenges (these were “discovered” later)

  •  Amazon changes their database, by redirecting ASINs, and they do not provide this information via their Amazon Product Advertising API
  •  The need to ignore certain sellers became apparent, and this also is not available through the Amazon Product Advertising API.
  •  Text data can take up a lot of space and tables become too large.

A Look at the Program I Developed

I solved all of these challenges.  I wrote the program first in Java and then re-wrote it in PHP and Javascript with more features.  You can write a program that achieves these tasks in pretty much any language.  A popular choice these days would probably be Python or Ruby on Rails or Node.js.  Since I’m going to be discussing general design & implementation here, this article aims to be helpful irregardless of which language you choose.

Key Design Ideas

  • I chose to separate the background worker thread, which looks up the price data, from the user-facing interface.
  • I used the Model-View-Controller (MVC) architectural design pattern.  My model was a MySQL database.  My controller was a PHP script.  My view was implemented primarily with Javascript and HTML/CSS (using the Bootstrap framework).
  • I created a visual dashboard that gives feedback as to the program’s current status, most recent matches, and recent error messages
  • Exceptions are logged but the program is able to recover from them and continue running.
  • I wrote an algorithm that throws away the least important data to save space.  I created summary tables that save just the daily low and high prices and I used these for my graphs.

The Dashboard

Screenshot of Amazon Lookup Program for Online Arbitrage
Screenshot of the Amazon Lookup Program for Online Arbitrage
  1. Green status indicator.  This will turn to a red X if the lookup program is not running correctly (for example, no internet connection or MySQL connection error)
  2. Link to the Status page, which displays errors and information about recent queries
  3. Input field to lookup an ASIN. If it’s in the database, a price history chart is displayed.
  4. The user can star a match that they are considering.
  5. Clicking on the product’s name links to the price history page, which also has a link to Amazon for easy purchasing.

 

The Amazon Search Portal

Screenshot of the Amazon Lookup Program - Search Portal
Screenshot of the Amazon Lookup Program – Search Portal

The Amazon Search Portal is the part of my program that allows for easy addition, editing, or removal of products from the database.   Products that are checkmarked are already in the database.  The details badge is a link to a popup that allows for editing the desired price and notes.  The update button allows for easy addition or removal of products.  I used Boostrap.js for the design elements.  It’s a really easy way to make a web app responsive and less ugly!

Thinking about doing something like this?  Already did it?

I’d love to hear your experience.  You can definitely make money buying low and selling high — that’s not new.  But with programming skills and a lot of time, you can certainly write programs that do a lot of the work for you!  Automation is definitely one of my favorite programming tasks to take on.