Database Schema

Users

column name	data type	details
id	integer	not null, primary key
username	string	not null, unique
email	string	not null, unique
bio	string	not null
profile_pic	string	not null
hashedPassword	string	not null
created_at	timestamp	not null
updated_at	timestamp	not null

Followers

Column name	data type	details
following_id	integer	not null, Foreign Key
follower_id	integer	not null, Foreign Key

• following_id references Users table

many to many relationship [user can follow many users and also have many folowers]

• follower_id references Users table

many to many relationship [user can follow many users and also have many folowers]

Likes

Column name	data type	details
user_id	integer	not null, Foreign Key
post_id	integer	not null, Foreign Key

• user_id references Users table

many to many relationship [user can make likes on many post]

• post_id references Posts table

many to many relationship [post can have many likes created by many users]

Comments

column name	data type	details
id	integer	not null, primary key
caption	string	not null
user_id	integer	not null, Foreign Key
post_id	integer	not null, Foreign Key
timestamp	datetime	not null
created_at	timestamp	not null
updated_at	timestamp	not null

• user_id references Users table

one to many relationship [user can make many comments but a comment can only belong to one user]

• post_id references Posts table

one to many relationship [a post can have many comments but a comment can only belong to one post]

Posts

column name	data type	details
id	integer	not null, primary key
caption	string	not null
user_id	string	not null, Foreign Key
pic_url	string	not null
timestamp	datetime	not null
created_at	timestamp	not null
updated_at	timestamp	not null

• user_id references Users table

one to many relationship [user can make many post but a post can only be owned by one user]