Postgres Database Schema

users

column name	data type	details
id	integer	not null, primary key
fname	string	not null
lname	string	not null
username	string	not null, indexed, unique
email	string	not null, indexed, unique
birthday	date	not null
gender	string	not null
password_digest	string	not null
session_token	string	not null, indexed, unique
bio	string
workplace	string
education	string
hometown	string
relationship_status_id	string
name_pronunciation	string
website	string
created_at	datetime	not null
updated_at	datetime	not null

• index on username, unique: true
• index on email, unique: true
• index on session_token, unique: true

posts

column name	data type	details
id	integer	not null, primary key
author_username	integer	not null, indexed, foreign key
board_username	integer	not null, indexed, foreign key
body	string	not null
created_at	datetime	not null
updated_at	datetime	not null

• author_id and board_id reference users table
• index on author_id
• index on board_id

comments

column name	data type	details
id	integer	not null, primary key
author_id	integer	not null, indexed, foreign key
post_id	integer	not null, indexed, foreign key
body	string	not null
created_at	datetime	not null
updated_at	datetime	not null

• user_id references users table
• post_id references posts table
• index on user_id
• index on post_id

friend_requests

column name	data type	details
id	integer	not null, primary key
requester_id	integer	not null, indexed, foreign key
requestee_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• requester_id and requestee_id reference users table
• index on [:requester_id, :requestee_id], unique: true

friendships

column name	data type	details
id	integer	not null, primary key
friender_id	integer	not null, indexed, foreign key
friended_id	integer	not null, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• friender_id and friended_id reference users table
• index on [:friender_id, :friended_id], unique: true

likes

column name	data type	details
id	integer	not null, primary key
user_id	integer	not null, indexed, foreign key
likeable_id	integer	not null, indexed, foreign key
likeable_type	string	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• polymorphic table
• user_id references users
• likeable_id references posts table or comments table
• likeable_type references Post model or Comment model
• index on [:user_id, :likeable_id, :likeable_type], unique: true
• index on [:likeable_id]