Database Tables

users

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

• index on username, email, session_token unique: true

listings

column	data type	details
id	integer	not null, primary key
author_id	integer	not null, foreign key
title	string	not null, indexed
body	text
price	integer	not null, indexed
offer	boolean	not null
sold	boolean	not null, indexed
condition	string	not null, indexed
color	string	not null, indexed
rarity	string	not null, indexed
types	string	not null, indexed
mana	integer	indexed
created_at	datetime	not null
updated_at	datetime	not null

• index on title, body, price, condition, color, rarity, types, mana, sold
• author_id references users

messages

column	data type	details
id	integer	not null, primary key
body	text	not null
from_id	integer	not null, indexed, foreign key
to_id	integer	not null, indexed, foreign key
list_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• index on from_id, to_id, list_id
• from_id references users (user sending the message)
• to_id references users (user recieving the message)
• list_id references listings (the listing that was messaged about)

reviews

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

• index on user_id, list_id
• user_id references users
• list_id references listings

comments

column	data type	details
id	integer	not null, primary key
body	text	not null
author_id	integer	not null, indexed, foreign key
list_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• index on author_id, list_id
• author_id referances users
• list_id referances listings

favorites

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

• index on user_id, list_id
• user_id references users
• list_id references listings

image source https://mtg.fandom.com/wiki/Parts_of_a_card?file=Parts_of_a_Magic_card.jpg