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
title	string
status	string
password_digest	string	not null
session_token	string	not null, indexed, unique
created_at	datetime	not null
updated_at	datetime	not null

Details

• display_name is indexed to allow for quick search of users
• email and session_token must be unique

Associations

• A user has_many created_channels
• A user has_many authored_messages
• A user has_many subscribed_channels through subscriptions
• A user has_many subscribed_groups through subscriptions

channels

column_name	data_type	details
id	integer	not null, primary key
creator_id	integer	not null, foreign key
name	string	not null, indexed, unique
topic	string
description	string
created_at	datetime	not null
updated_at	datetime	not null

Details

• creator_id references the user who created this channel
• name is indexed to allow for quicker search of channels

Associations

• A channel belongs_to the channel_creator
• A channel has_many channel_members through subscriptions
• A channel has_many messages

messages

column_name	data_type	details
id	integer	not null, primary key
text	text	not null
author_id	integer	not null, foreign key
messagable_id	integer	not null, foreign key
messagable_type	string	not null
edited	boolean	default => false
created_at	datetime	not null
updated_at	datetime	not null

Details

• author_id refers to the user who created the message
• messagable_id and messagable_type refers to where the message was added (channel or group).
• text should not be null
• edited will be used to flag if this message has been edited by the message's author

Associations

• Using Polymorphic associations, a message belongs_to either a channel or a group
• A message belongs_to an author who can edit or delete that message

groups

column_name	data_type	details
id	integer	not null, primary key
hidden	boolean	default => false
created_at	datetime	not null
updated_at	datetime	not null

Details

• This table will be used for 2-person direct messages and larger group messages
• hidden will be used to display or hide the group from the user's sidebar

Associations

• A group has_many users
• A group has_many group_members through subscriptions
• A group has_many messages

subscriptions

column_name	data_type	details
id	integer	not null, primary key
subscriber_id	integer	not null, foreign key
subscribable_id	integer	not null, foreign key
subscribable_type	string	not null
created_at	datetime	not null
updated_at	datetime	not null

Details

• Joins table for channels, groups and subscribers
• subscriber_id links to the users table
• subscribable_id and subscribable_type link to the channels and groups tables
• [subscriber_id, subscribable_id, subscribable_type] must be unique to ensure a user cannot subscribe to the same channel or group twice

Associations

• A subscription belongs_to a subscribed_channel or a subscribed_group
• A subscription belongs_to a subscriber

Bonus

reactions

column_name	data_type	details
id	integer	not null, primary key
user_id	integer	not null, foreign key
message_id	integer	not null, foreign key
type	string	not null
created_at	datetime	not null
updated_at	datetime	not null