Database Schema

DB Schema

Projects

column name	data type	details
id	integer	not null, primary key
title	string	not null
description	text
category	string
thumbnail_url	string
videos	string	url from youtube
author_id	string	not null, indexed
published_date	datetime	not null
has_many :views
has_many :comments
has_many :likes
belongs_to :author

• author_id references users
• items we will need with joins
  • author name
  • number of views
  • comments associated with this project
  • followers of this project

Items

column name	data type	details
id	integer	not null, primary key
project_id	integer	index
type	string	index (video, image, text)
text	text
caption	text
image_file_name	string
image_content_type	string
image_file_size	integer
image_updated_at	datetime

Comments

column name	data type	details
id	integer	not null, primary key
user_id	integer	index
project_id	integer	index
body	text	not null
timestamp	datetime	not null

Users

column name	data type	details
id	integer	not null, primary key
username	string	not null, add index
fname	string	not null
lname	string	not null
password_digest	string	not null
session_token	string	not null
about_me	text
location	string
`image_file_name"	string
`image_content_type"	string
`image_file)size"	integer
image_updated_at	datetime
has_many :projects
has_many :following
has_many :followers
has_many :projects_liked
has_many :comments

• items we will need with joins
  • myProjects
  • projects I'm following
  • projects I've liked
  • Comments I've left

Likes_join

column name	data type	details
project_id	integer	index
user_id	integer	index

• should nest an index of these

Followers join

column name	data type	details
author_id	integer	index
follower_id	integer	index

• users follow other users (not projects)
• we'll need two nested indexes