Database Schema

Database Schema

Users

column name	data type	details
id	integer	primary key, not null
username	varchar	not null, unique
firstName	varchar	not null
lastName	varchar	not null
email	varchar	not null, unique
photoURL	text
hashedPassword	varchar	not null
created_at	timestamp	not null, default: NOW
updated_at	timestamp	not null, default: NOW

Albums

column name	data type	details
id	integer	primary key, not null
userId	integer	not null
title	varchar	not null
description	text
created_at	timestamp	not null, default: NOW
updated_at	timestamp	not null, default: NOW

• Albums.userId references Users.id

Photos

column name	data type	details
id	integer	primary key, not null
userId	integer	not null
albumId	integer	not null
photoURL	text	not null
title	varchar
description	text
created_at	timestamp	not null, default: NOW
updated_at	timestamp	not null, default: NOW

• Photos.userId references Users.id
• Photos.albumId references Albums.id

Comments

column name	data type	details
id	integer	primary key, not null
userId	integer	not null
photoId	integer	not null
content	text	not null
created_at	timestamp	not null, default: NOW
updated_at	timestamp	not null, default: NOW

• Comments.userId references Users.id
• Comments.photoId references Photos.id

Views

column name	data type	details
id	integer	primary key, not null
userId	integer	not null
albumId	integer	not null
photoId	integer	not null
created_at	timestamp	not null, default: NOW
updated_at	timestamp	not null, default: NOW

• Views.userId references Users.id
• Views.albumId references Albums.id
• Views.photoId references Photos.id

Favorites

column name	data type	details
id	integer	primary key, not null
userId	integer	not null
photoId	integer	not null
created_at	timestamp	not null, default: NOW
updated_at	timestamp	not null, default: NOW

• Favorites.userId references Users.id
• Favorites.photoId references Photos.id

Tags

column name	data type	details
id	integer	primary key, not null
photoId	integer	not null
keyword	varchar	not null
created_at	timestamp	not null, default: NOW
updated_at	timestamp	not null, default: NOW

• Tags.photoId references Photos.id