Database Schema

Users

column name	data type	detail
id	integer	not null, primary key
username	string	not null, unique
passwordHash	binary	not null
bio	text
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	not null
description	string
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	not null
back	string	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	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	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