Example of a one-to-one, one-to-many, and a many-to-many relationship using PostgreSQL

talent_create_tables_data.sql

/** ---------- TALENT ---------- **/
DROP TABLE IF EXISTS talent CASCADE;

CREATE TABLE talent (
	talent_id serial PRIMARY KEY,
	first_name text,
	last_name text,
	phone text,
	created_at timestamp,
	updated_at timestamp
);
/** ---------- SKILLS ---------- **/
DROP TABLE IF EXISTS skills CASCADE;

CREATE TABLE skills (
	skill_id serial PRIMARY KEY,
	skill_name text,
	created_at timestamp,
	updated_at timestamp
);
/** ---------- TALENT-SKILLS ---------- **/
/* Helps form the many-to-many relationship between talent and skills.
 * Uses foreign key constraints via the REFERENCES key word.
 * Learn more about constraints in Postgres
 * http://www.postgresql.org/docs/9.5/static/ddl-constraints.html
 */
DROP TABLE IF EXISTS talent_skills;

CREATE TABLE talent_skills (
	talent_id int REFERENCES talent,
	skill_id int REFERENCES skills,
	PRIMARY KEY(talent_id, skill_id),
	created_at timestamp,
	updated_at timestamp
);
/** ---------- WAGE REQUIREMENTS ---------- **/
/* Foreign key constraint helps keep/define the 1-to-1 relationship 
 * between talent and wage_requirements.
 */
DROP TABLE IF EXISTS wage_requirements;

CREATE TABLE wage_requirements (
	wage_requirement_id int REFERENCES talent(talent_id) PRIMARY KEY,
	minimum int,
	maximum int,
	type varchar(8) CHECK (type IN ('salary', 'hourly')),
	created_at timestamp,
	updated_at timestamp
);
/** ---------- HISTORY ---------- **/
/* Another 1 to many relationship between talent and history.
 */
DROP TABLE IF EXISTS history CASCADE;

CREATE TABLE history (
	history_id serial PRIMARY KEY,
	org text,
	start_date date,
	end_date date,
	is_current boolean,
	talent_id int REFERENCES talent,
	created_at timestamp,
	updated_at timestamp
);