Skip to content

Instantly share code, notes, and snippets.

@JosephScript
Created January 25, 2016 17:18
Show Gist options
  • Save JosephScript/252d1c14087560b513ed to your computer and use it in GitHub Desktop.
Save JosephScript/252d1c14087560b513ed to your computer and use it in GitHub Desktop.
Example of a one-to-one, one-to-many, and a many-to-many relationship using PostgreSQL
/** ---------- 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
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment