The goal of this project is to create a social media platform that allows users to share photos, videos, and messages with each other. The platform will provide users with a simple and intuitive interface that makes it easy to upload and view content, as well as interact with other users through comments and likes.

The platform will be built using PostgreSQL, a powerful and reliable database management system that provides efficient and secure data storage. The data will be accessed and manipulated using SQL, a standard programming language for managing relational databases.

In addition to the basic features of the platform, we will also implement some advanced features such as photo and caption tagging, hashtagging, and user following. These features will help users to find and organize content, and also to connect with other users who share similar interests.

Overall, this project aims to create a fun and engaging social media platform that offers a wide range of features to its users, while also maintaining high levels of security and reliability.

RDD Design Tool: DBDiagram

RDBMS: PostgreSQL

This table contains information about the users of the application. It has columns for id, created_at, updated_at, username, bio, avatar, phone, email, password, and status. id is a unique identifier for each user, created_at and updated_at store the date and time when the user was created or updated, username is the user's name, bio is a short description of the user, avatar is the URL of the user's profile picture, phone is the user's phone number, email is the user's email address, password is the user's password, and status indicates the user's current status. The CHECK constraint ensures that at least one of phone or email is not null.

This table contains information about the posts created by the users. It has columns for id, created_at, updated_at, url, caption, lat, lng, and user_id. id is a unique identifier for each post, created_at and updated_at store the date and time when the post was created or updated, url is the URL of the post's image, caption is the post's caption, lat and lng are the post's location coordinates, and user_id is the identifier of the user who created the post.

This table contains information about the comments created on posts. It has columns for id, created_at, updated_at, contents, user_id, and post_id. id is a unique identifier for each comment, created_at and updated_at store the date and time when the comment was created or updated, contents is the text of the comment, user_id is the identifier of the user who created the comment, and post_id is the identifier of the post on which the comment was created.

This table contains information about the likes given by users on posts and comments. It has columns for id, created_at, user_id, post_id, and comment_id. id is a unique identifier for each like, created_at stores the date and time when the like was created, user_id is the identifier of the user who gave the like, and either post_id or comment_id is the identifier of the post or comment that was liked. The CHECK constraint ensures that a like can be given only to a post or a comment, but not both at the same time. The UNIQUE constraint ensures that a user can like a post or a comment only once.

This table contains information about the tags given by users on posts' images. It has columns for id, created_at, updated_at, user_id, post_id, x, and y. id is a unique identifier for each photo tag, created_at and updated_at store the date and time when the tag was created or updated, user_id is the identifier of the user who created the tag, post_id is the identifier of the post on which the tag was created, and x and y are the coordinates of the tag's position on the image. The UNIQUE constraint ensures that a user can tag a post's image only once.

This table contains information about the tags given by users in posts' captions. It has columns for id, created_at, user_id, and post_id. id is a unique identifier for each caption tag, created_at stores the date and time when the tag was created, user_id is the identifier of the user who created the tag, and post_id is the identifier of the post on which the tag was created. The UNIQUE constraint ensures that a user can tag a post's caption only once.

This table contains information about the hashtags used in posts' captions. It has columns for id, created_at, and title. id is a unique identifier for each hashtag, created_at stores the date and time when the hashtag was created, and title is the text of the hashtag. The UNIQUE constraint ensures that a hashtag can be used only once.

This table associates posts with hashtags. It has columns for id, hashtag_id, and post_id. id is a unique identifier for each row, hashtag_id is the identifier of the hashtag associated with the post, and post_id is the identifier of the post associated with the hashtag. The UNIQUE constraint ensures that a post can be associated with a hashtag only once.

followers table: This table contains information about the followers of the users. It has columns for id, created_at, leader_id, and follower_id. id is a unique identifier for each row, created_at stores the date and time when the follower relation was created, leader_id is the identifier of the user who is being followed, and follower_id is the identifier of the user who is following. The UNIQUE constraint ensures that a user can follow another user only once.

data.sql can be restored as mock data into the database.

SELECT COUNT(*)
FROM users;

Output:

5345

SELECT COUNT(*)
FROM likes;

Output:

752009

SELECT *
FROM users
ORDER BY id DESC
LIMIT 3;

Output:

SELECT username, caption
FROM users
JOIN posts ON posts.user_id = users.id
WHERE posts.user_id = 200;

Output:

SELECT username, COUNT(*)
FROM users
JOIN likes ON likes.user_id = users.id
GROUP BY username;

Output:

Displaying only the top 10 for the documentation.

Erol Gelbul - Website

Project Link: Image Zen DB

(back to top)