Resumes Database Schema

resumes.sql

CREATE DATABASE resumes;

CREATE TABLE resumes.users (
	id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	first_name     varchar(50)  NOT NULL,
	last_name      varchar(50)  NOT NULL,
	email          varchar(255) NOT NULL,
	phone          varchar(10)  NOT NULL,
	password       varchar(255) NOT NULL,
	remember_token varchar(100) NULL DEFAULT NULL,
	created_at     timestamp    NULL DEFAULT NULL,
	updated_at     timestamp    NULL DEFAULT NULL,
	PRIMARY KEY (id),
	UNIQUE KEY users_email_unique (email)
);

CREATE TABLE resumes.resumes (
	id         int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	user_id    int(10) UNSIGNED NOT NULL,
	created_at timestamp NULL DEFAULT NULL,
	updated_at timestamp NULL DEFAULT NULL,
	PRIMARY KEY (id),
	CONSTRAINT resumes_user_id_foreign_key FOREIGN KEY (user_id) REFERENCES resumes.users (id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE resumes.experience (
	id         int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	resume_id  int(10) UNSIGNED NOT NULL,
	job_title  varchar(255) NOT NULL,
	employer   varchar(255) NOT NULL,
	city       varchar(255) NOT NULL,
	state      varchar(2) NOT NULL,
	start_date date NULL DEFAULT NULL,
	end_date   date NULL DEFAULT NULL,
	created_at timestamp NULL DEFAULT NULL,
	updated_at timestamp NULL DEFAULT NULL,
	PRIMARY KEY (id),
	CONSTRAINT experience_resumes_id_foreign_key FOREIGN KEY (resume_id) REFERENCES resumes.resumes (id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE resumes.education (
	id          int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	resume_id   int(10) UNSIGNED NOT NULL,
	school_name varchar(255) NOT NULL,
	city        varchar(255) NOT NULL,
	state       varchar(2) NOT NULL,
	degree      varchar(255) NOT NULL,
	grad_year   varchar(4) NULL DEFAULT NULL,
	created_at timestamp NULL DEFAULT NULL,
	updated_at timestamp NULL DEFAULT NULL,
	PRIMARY KEY (id),
	CONSTRAINT education_resumes_id_foreign_key FOREIGN KEY (resume_id) REFERENCES resumes.resumes (id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE resumes.skills (
	id          int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	resume_id   int(10) UNSIGNED NOT NULL,
	skill       varchar(255) NOT NULL,
	created_at timestamp NULL DEFAULT NULL,
	updated_at timestamp NULL DEFAULT NULL,
	PRIMARY KEY (id),
	CONSTRAINT skills_resumes_id_foreign_key FOREIGN KEY (resume_id) REFERENCES resumes.resumes (id) ON DELETE CASCADE ON UPDATE CASCADE
);