SQL and schema design

The concept

A schema is the shape of your data: tables, the columns in each, the type of every column, and which column links two tables. Spend twenty minutes drawing it on paper before writing any query — a job board needs users, jobs and applications, and every line of code after that is easier because the shape is settled.

Use an integer primary key on every table, a foreign key to point at another table, and add an index to any column you filter or join on. created_at on every row costs nothing and answers "how many this month" for ever. Never store what you can calculate, and never put two values in one column.

CREATE TABLE jobs (
  id         INT PRIMARY KEY,
  title      VARCHAR(160) NOT NULL,
  company_id INT NOT NULL,
  created_at DATETIME
);

CREATE TABLE applications (
  id     INT PRIMARY KEY,
  job_id INT NOT NULL,
  email  VARCHAR(160) NOT NULL,
  status VARCHAR(20) DEFAULT 'applied'
);

CREATE INDEX idx_applications_job ON applications (job_id);