Database Schema

Database Schema Diagram

Users

column name	data type	detail
id	integer	not null, primary key
username	string(32)	not null, unique
passwordHash	binary	not null
bio	text(512)
created_at	timestamp	not null
updated_at	timestamp	not null

User_study_deck

column name	data type	detail
id	integer	not null, primary key
user_id	integer	not null, foreign key
deck_id	integer	not null
toStudy	boolean	not null
created_at	timestamp	not null
updated_at	timestamp	not null

• user_id references users.id
• deck_id references decks.id

User_study_card

column name	data type	detail
id	integer	not null, primary key
user_id	integer	not null, foreign key
card_id	integer	not null
isCorrect	boolean	not null
created_at	timestamp	not null
updated_at	timestamp	not null

• user_id references users.id
• card_id references cards.id

Decks

column name	data type	detail
id	integer	not null, primary key
title	string(64)	not null
description	string(256)
user_id	integer	not null, foreign key
created_at	timestamp	not null
updated_at	timestamp	not null

• user_id references users.id

Cards

column name	data type	detail
id	integer	not null, primary key
front	string(512)	not null
back	string(512)	not null
deck_id	integer	not null, foreign key
created_at	timestamp	not null
updated_at	timestamp	not null

• deck_id references decks.id

Tags

column name	data type	detail
id	integer	not null, primary key
name	string(16)	not null
deck_id	integer	not null, foreign key
created_at	timestamp	not null
updated_at	timestamp	not null

• deck_id references decks.id

Comments

column name	data type	detail
id	integer	not null, primary key
content	text(256)	not null
user_id	integer	not null, foreign key
deck_id	integer	not null, foreign key
created_at	timestamp	not null
updated_at	timestamp	not null

• user_id references users.id
• deck_id references decks.id