Database Schema

Database Schema

Users

column name	data type	details
id	integer	not null, primary key
user_url	string	not null, indexed
firstname	string	not null
lastname	string	not null
email	string	not null, indexed, unique
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: I think that user url should be set automatically, it is the only unique attribute (as far as I'm aware) on the user model - used for profile url.

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
body	string	not null
author_id	integer	not null, indexed, foreign key
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
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