Postgres Database Schema - KevLin2358/Slickr GitHub Wiki

users

column name	data type	details
id	integer	not null, primary key
username	string	not null, indexed, unique
first_name	string	not null, indexed
last_name	string	not null, indexed
email	string	not null, indexed, unique
password_digest	string	not null
session_token	string	not null, indexed, unique
created_at	datetime	not null
updated_at	datetime	not null

• indexed
  • username
  • first_name
  • last_name
  • email
  • session_token
• users has many photos
• users has many albums
• users has many comments
• users has many likes

photos

column name	data type	details
id	integer	not null, primary key
uploader_id	integer	not null, indexed, foreign_key
title	string	not null, indexed
description	text
created_at	datetime	not null
updated_at	datetime	not null

• indexed
  • uploader_id
  • title
• uploader_id references from table users id
• photos belongs to uploader
• photos has many comments

albums

column name	data type	details
id	integer	not null, primary key
owner_id	integer	not null, indexed, foreign_key
title	string	not null, indexed
description	text
created_at	datetime	not null
updated_at	datetime	not null

• indexed
  • user_id
  • title
• owner_id references from table users id
• albums belongs to owner

album_photo

column name	data type	details
id	integer	not null, primary key
album_id	integer	not null, indexed, foreign_key
photo_id	integer	not null, indexed, foreign_key
created_at	datetime	not null
updated_at	datetime	not null

• indexed
  • album_id
  • photo_id
• photo_id references from table photos id
• album_id references from table albums id

likes

column name	data type	details
id	integer	not null, primary key
liker_id	integer	not null, indexed, foreign_key
photo_id	integer	not null, indexed, foreign_key
created_at	datetime	not null
updated_at	datetime	not null

• indexed

  • liker_id
  • photo_id

• liker_id references from table users id

• photo_id references from table photos id

• index on [:liker_id, :photo_id], unique: true

• photo_id has one liker

• likes belongs users

comments

column name	data type	details
id	integer	not null, primary key
commenter_id	integer	not null, indexed, foreign_key
photo_id	integer	not null, indexed, foreign_key
body	text	not null
created_at	datetime	not null
updated_at	datetime	not null

• indexed

  • commenter_id
  • photo_id

• commenter_id references from table users id

• photo_id references from table photos id

• comments belongs to photos

• comments belongs to users

tag

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

photo_tags

column name	data type	details
id	integer	not null, primary key
tag_id	integer	not null, indexed, foreign_key
photo_id	integer	not null, indexed, foreign_key
created_at	datetime	not null
updated_at	datetime	not null

• indexed

  • tag_id
  • photo_id

• tag_id references from table tags id

• photo_id references from table photos id

followers

column name	data type	details
id	integer	not null, primary key
followee_id	integer	not null, indexed, foreign_key
follower_id	integer	not null, indexed, foreign_key
created_at	datetime	not null
updated_at	datetime	not null

• indexed

  • followee_id
  • follower_id

• followee_id and follower_id references from table users id

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

• followee_id has one follower_id