Schema

Postgres Database Schema

users

column name	data type	details
id	integer	not null, primary key
username	string	not null, indexed, unique
password_digest	string	not null
session_token	string	not null, indexed, unique
created_at	datetime	not null
updated_at	datetime	not null

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

posts

column name	data type	details
id	integer	not null, primary key
title	string	not null
body	text
media	string
link	string
poster_id	integer	not null, indexed
community_id	integer	not null, indexed
created_at	datetime	not null
updated_at	datetime	not null

• index on poster_id
• index on community_id
• poster_id is a reference to an id in the users table
• community_id is a reference to an id in the communities table

comments

column name	data type	details
id	integer	not null, primary key
content	text	not null
commenter_id	integer	not null, indexed
post_id	integer	not null, indexed
parent_comment_id	integer	indexed
created_at	datetime	not null
updated_at	datetime	not null

• index on commenter_id
• index on post_id
• index on parent_comment_id
• commenter_id is a reference to an id in the users table
• post_id is a reference to an id in the posts table
• parent_comment_id is a reference to an id in the comments table

votes

column name	data type	details
id	integer	not null, primary key
vote	boolean	not null
user_id	integer	not null, indexed
parent_id	integer	not null, indexed
parent_type	string	not null
created_at	datetime	not null
updated_at	datetime	not null

• index on user_id
• index on parent_id
• user_id is a reference to an id in the users table
• parent_id is a reference to an id in either the posts or comments table
  • determined by the parent_type

saves

column name	data type	details
id	integer	not null, primary key
user_id	integer	not null, indexed
parent_id	integer	not null, indexed
parent_type	string	not null
created_at	datetime	not null
updated_at	datetime	not null

• index on user_id
• index on parent_id
• user_id is a reference to an id in the users table
• parent_id is a reference to an id in either the posts or comments table
  • determined by the parent_type

follows

column name	data type	details
id	integer	not null, primary key
user_id	integer	not null, indexed
community_id	integer	not null, indexed
created_at	datetime	not null
updated_at	datetime	not null

• index on user_id
• index on community_id
• user_id is a reference to an id in the users table
• communities_id is a reference to an id in the communities table

communities

column name	data type	details
id	integer	not null, primary key
sub	string	not null, indexed, unique
about	text	not null
creator_id	integer	not null, indexed
created_at	datetime	not null
updated_at	datetime	not null

• index on sub, unique: true
• index on creator_id
• creator_id is a reference to an id in the users table