Database Schema

This is the PostgreSQL database schema for the application.

users

column name	data type	details
id	integer	not null, primary key
username	string	not null, indexed, unique
email	string	not null, indexed, unique
bio	text
password_digest	string	not null
session_token	string	not null, indexed, unique
avatar	blob
created_at	datetime	not null
updated_at	datetime	not null

• index on username, unique: true
• index on email, unique: true
• index on session_token, unique: true
• Has many collections
• Has many likes
• Has many liked collections through likes
• Has one attached avatar

artworks

column name	data type	details
id	integer	not null, primary key
aic_id	integer	not null, indexed, unique
title	string
alt_text	string
artist_title	string
image_url	string	not null
created_at	datetime	not null
updated_at	datetime	not null

• index on image_url
• index on aic_id, unique: true
• Has many collection_artworks
• Has many collections through collection_artworks

saved_artworks

column name	data type	details
id	integer	not null, primary key
user_id	integer	not null, indexed, foreign key
artwork_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• Join table between users and artworks
• user_id references users
• artwork_id references artworks
• index on [:artwork_id, :user_id], unique: true

collections

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

• user_id references users
• index on user_id
• Belongs to user
• Has many collection_artworks
• Has many artworks through collection artworks

collection_artworks

column name	data type	details
id	integer	not null, primary key
collection_id	integer	not null, indexed, foreign key
artwork_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• Join table between collections and artworks
• collection_id references collections
• artwork_id references artworks
• index on [:collection_id, :user_id], unique: true

collection_likes

column name	data type	details
id	integer	not null, primary key
user_id	integer	not null, indexed, foreign key
collection_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• Join table between users and collections
• user_id references users
• collection_id references collections
• index on [:collection_id, :user_id], unique: true

We don't need a separate index for collection_id or user_id because the first index adds it for us.

collection_comments

column name	data type	details
id	integer	not null, primary key
user_id	integer	not null, indexed, foreign key
collection_id	integer	not null, indexed, foreign key
body	text	not null
created_at	datetime	not null
updated_at	datetime	not null

• user_id references users
• collection_id references collections
• index on [:collection_id, :user_id], unique: true