Database Schema

Jump to bottom

Hanqing Chen edited this page Aug 8, 2018 · 6 revisions

App Academy Full Stack Project

Flickr Clone Database Schema

`users`

Column Name	Data Type	Details
id	Integer	not null, primary key
username	String	not null, indexed, unique
email	String	not null, indexed, unique
password_digest	String	not null
session_token	String	not null, indexed, unique
created_at	datetime	not null
profile_pic_url	String	not null
fname	String	not null
lname	String	not null

has many photos
has many albums
has many comments
username, email, session_token validation: indexed: true, uniqueness: true

`photos`

Column Name	Data Type	Details
id	Integer	not null, primary key
image_url	String	not null, unique
owner_id	Integer	not null, foreign key, indexed
name	String	not null, unique
description	String	not null
uploaded_at	datetime	not null

owner_id references users
has many albums through PhotoAlbums table
image_url, name validation: uniqueness: true

`albums`

Column Name	Data Type	Details
id	Integer	not null, primary key
creator_id	Integer	not null, foreign key, indexed
name	String	not null
description	String	not null
created_at	datetime	not null

creator_id references users
has many photos through PhotoAlbums table

`photo_albums`

Column Name	Data Type	Details
id	Integer	not null, primary key
album_id	Integer	not null, foreign key, indexed
photo_id	Integer	not null, foreign key, indexed

album_id references albums
photo_id references photos

`comments`

Column Name	Data Type	Details
id	Integer	not null, primary key
commentor_id	Integer	not null, foreign key
photo_id	Integer	not null
name	String	not null, indexed
body	Text	not null
created_at	datetime	not null

commentor_id references users
photo_id references photos

`tags`

Column Name	Data Type	Details
id	Integer	not null, primary key
user_id	Integer	not null, indexed
photo_id	Integer	not null, foreign key, indexed
tags	string	not null
created_at	datetime	not null

user_id references users
photo_id references photos