Database Schema

Database Schema

Users

column name	data type	details
id	integer	not null, primary key
user_url	string	not null, indexed
firstname	string	optional
lastname	string	optional
email	string	not null, indexed, unique
birthday	datetime	optional
sex	string	optional
bio	text	optional
profile_img_id	integer	optional
cover_photo_id	integer	optional
password_digest	string	not null
session_token	string	not null, indexed, unique
created_at	datetime	not null
updated_at	datetime	not null

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

note: For demonstration purposes, only email and password are required. Fields for first name and last name will be provided as optional fields on sign up, but if they aren't filled in a random name will be generated for them using faker

Friends

column name	data type	details
id	integer	not null, primary key
requesting_user_id	integer	not null, foreign key
requested_user_id	integer	not null, foreign key
approved	boolean	not null, default: false
created_at	datetime	not null
updated_at	datetime	not null

Posts

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

• index on author_id

PhotoAttachment

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

Comments

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

• index on post_id

• Add an optional parent comment ID in the future
• Add likes in the future - should there just be likes or should there be comment and post likes as separate models?

Photos

column name	data type	details
id	integer	not null, primary key
owner_id	integer	not null, indexed, foreign key
url	string	not null, unique: true
created_at	datetime	not null
updated_at	datetime	not null

• index on owner_url
• index on url, unique: true

possible additions: tag_ids(users), comments, likes

Chats (Possible Future Feature)

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

Messages (Possible Future Feature)

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

• index on chat_id
• when a user sends a message, it is associated with one chat that many users can potentially subscribe to
• the user that creates the chat can add members to the chat - the controller will modify the target users to subscribe them to the chat when it's created
• once added to a chat, any user can add new users to the conversation