Schema

Postgres Database Schema

users

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

• Index on email, display_name, and session_token
  • display_name is indexed for searching users
• Uniqueness on email and session_token
• A user can have many channels as an admin, and can belong to many channels as a member
• A user can have many messages

channels

column name	data type	details
id	integer	not null, primary key
name	string	not null, indexed
description	string
admin_id	integer	not null, indexed, foreign key
public	boolean	not null
created_at	datetime	not null
updated_at	datetime	not null

• Index on name and admin_id
  • name is indexed for searching channels
• public defaults to true
• A channel can have many users and many messages through users
• A channel belongs to a user through admin_id

messages

column name	data type	details
id	integer	not null, primary key
user_id	integer	not null, foreign key, indexed
messageable_id	integer	not null, foreign key, indexed
messageable_type	string	not null
body	string	not null
created_at	datetime	not null, indexed
updated_at	datetime	not null

• Index on user_id, messageable_id, and created_at
• A message belongs to a user through user_id
• A message belongs to a channel or direct_message through the messageable_id and messageable_type
  • Can also belong to a thread if I reach the bonus
• messageable_type can be either channel or direct_message
  • Can also be thread if I reach the bonus

direct_messages

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

• A direct_message can have many users and many messages

memberships

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

• memberships is a joins table for users and channels/direct_messages
• Index on user_id and memberable_id
• A user can belong either a channel or a direct_message through the memberable_id and memberable_type
  • Can also belong to a thread if I reach the bonus
• memberable_type can be either channel or direct_message'
  • Can also be thread if I reach the bonus

Bonus

threads

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

Index on parent_message_id Has many messages and users Belongs to a message through parent_message_id and belongs to a channel or direct_message through the message