Database Schema

Database Schema

Users

Column	Data Type	Details
id	integer	not null, unique true, primary key
username	string	not null, unique true
email	string	not null, unique true
password_digest	string	not null, unique true
session_token	string	not null, unique true
title	string
description	string
private_posts	bool	default false
private_likes	bool	default true
private_followers	bool	default true
private_followings	bool	default true

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

Posts

First draft: photos, gifs, text, quotes, links second draft: photosets third draft: audio, video

has_many post_media

Column	Data Type	Details
id	integer	not null, unique true, primary key
title	string	optional
user_id	integer	not null
post_type	string	not null
description	string
private	bool	based on user preferences

• index on user_id

Alternatively, may have one url called content_url, and frontend will use type to decide how to render the url

if type === photoset, include corresponding photos from postmedia

PostMedia

(belongs_to post)

Column	Data Type	Details
id	integer	not null, unique true, primary key
post_id	integer	not null, indexed, foreign key (references posts)
media_type	string	not null
media_url	string	not null

• index on post_id

UserFollows

Column	Data Type	Details
id	integer	not null, unique true, primary key
following_id	integer	not null, unique true, indexed, foreign key (references users, person following the other user)
followed_id	integer	not null, unique true, indexed, foreign key (references users, the person being followed)
private	bool	based on user preferences

• index on follower_id
• index on folloing_id
• index on [follower_id, following_id], unique: true

Likes

Column	Data Type	Details
id	integer	not null, unique true, primary key
user_id	integer	not null, unique true, indexed, foreign key (references user who liked post)
post_id	integer	not null, unique true, indexed, foreign key (references posts)
private	bool	based on user preferences

• index on user_id
• index on post_id
• index on [user_id, post_id], unique: true

Reblogs

Column	Data Type	Details
id	integer	not null, unique true, primary key
author_id	integer	not null, unique true, indexed, foreign key (references users, original poster of material)
user_id	integer	not null, unique true, indexed, foreign key (references users, person who reblogged)
post_id	integer	not null, unique true, indexed, foreign key (references posts)
description	string

• index on user_id
• index on post_id

Collections

Column	Data Type	Details
id	integer	not null, unique true, primary key
user_id	integer	not null, unique true, indexed, foreign key (references users)
collection_name	string	not,null unique true in scope (user_id)
description	string	optional
private	bool	based on user preferences

• index on user_id

CollectionPosts

Column	Data Type	Details
id	integer	not null, unique true, primary key
collection_id	integer	not null, unique true, indexed, foreign key (references collections)
post_id	integer	not null, unique true in scope (collection_id), indexed, foreign key (references posts)
private	bool	based on user preferences

• index on collection_id
• index on post_id
• index on [collection_id, post_id], unique: true

tags

Column	Data Type	Details
id	integer	not null, unique true, primary key
name	string	not null, unique
(tags deleted when not in use)

taggings

Column	Data Type	Details
id	integer	not null, primary key
tag_id	integer	not null, unique in scope (post_id), indexed, foreign key (references tags)
post_id	integer	not null, unique in scope (tag_id), indexed, foreign key (references posts)

• index on tag_id
• index on post_id
• index on [tag_id, post_id], unique: true