Designing an Instagram

Relational Database Design and Modeling for Software Engineers

0% completed

Instagram is a platform where users share content, interact with others through likes, comments, and messages, and manage their profiles. This guide will model its core functionalities in a step-by-step fashion, ensuring efficient and scalable database design.

In this case study, we’ll design a database for Instagram using Entity-Relationship (ER) modeling and map it to a relational schema. By the end, you’ll have a clear understanding of how Instagram’s core functionalities are structured through database design.

Requirements Analysis

For our case study, let's outline the key functionalities that the HMS should support:

User Management: Storing user details and managing their profiles.
Content Management: Handling posts, captions, and engagement.
Engagement: Tracking likes, comments, and group discussions.
Messaging: Supporting private messages between users.
Profile Insights: Providing dashboards with user analytics.
Group Management: Enabling users to join and interact within groups.

Step-by-Step ER Diagram Creation

We will build the ER diagram for the Hospital Management System through the following four steps.

Step 1: Identify Entities

Entities represent objects or concepts in the system that have data stored about them.

User
Post
Comment
Like
Direct_Message
Notification
Group
Profile_Info
Profile_Dashboard

Entities for an Instagram

Step 2: Detailing Attributes

1. User

User_ID (PK)
Username
Full_Name
Email
Phone_Number
Date_Joined
Profile_Picture

2. Post

Post_ID (PK)
Caption
Post_Date
User_ID (FK)

3. Comment

Comment_ID (PK)
Text
Comment_Date
User_ID (FK)
Post_ID (FK)

4. Like

Like_ID (PK)
Like_Date
User_ID (FK)
Post_ID (FK)

5. Direct_Message

Message_ID (PK)
Message_Text
Sent_Date
Sender_ID (FK)
Recipient_ID (FK)

6. Notification

Notification_ID (PK)
Notification_Text
Notification_Date
User_ID (FK)

7. Group

Group_ID (PK)
Group_Name
Description
Member
Created_By (FK to User_ID)

8. Profile_Info

User_ID (PK, FK)
Bio
Website
Number_of_Posts
Number_of_Followers
Number_of_Following

9. Profile_Dashboard

User_ID (PK, FK)
Total_Likes
Total_Comments
Total_Groups_Joined
Total_Posts_Shared

Attributes for an Instagram System

Step 3: Defining Relationships

1. User and Post

A user can create multiple posts.
Each post belongs to one user.
Type: One-to-Many (User to Post)

2. Post and Comment

A post can have multiple comments.
Each comment belongs to one post and one user.
Type: One-to-Many (Post to Comment)

3. Post and Like

A post can have multiple likes.
Each like is made by one user.
Type: One-to-Many (Post to Like)

4. User Follows Other Users

A user can follow multiple users.
Type: One-to-Many (User to User)

5. User and Comment

A user can add multiple comments to any post.
A Comment can belong to one Post.
Type: One-to-Many (User to Comment)

6. User and Like

A user can give likes to multiple Posts.
A Like belongs to one Post.
Type: One-to-Many (User to Like)

7. User and Direct_Message

A user can send messages to multiple users.
A message belongs to one sender and one recipient.
Type: One-to-Many (User to Direct_Message)

8. User and Notification

A user can receive multiple notifications.
Each notification belongs to one user.
Type: One-to-Many (User to Notification)

9. User and Group

A user can create or join multiple groups.
A group can have multiple users.
Type: Many-to-Many (User to Group via Group_Member)

10. User and Profile_Info

A user can have one profile information entry.
Profile information is unique to each user.
Type: One-to-One (User to Profile_Info)

11. User and Profile_Dashboard

A user can have one profile dashboard.
The dashboard is unique to each user.
Type: One-to-One (User to Profile_Dashboard)

Here is the final ER diagram.

ER Diagram for Bank Management System

Mapping the ER Diagram to a Relational Schema

When converting the ER diagram into a relational schema for an instagram, we need to carefully handle various components to ensure data integrity and optimal performance. Here are the key considerations:

Entity Tables and Attributes:
- Create a table for each entity identified in the ER diagram, such as User, Post, Group, etc.
- Define all attributes for each table, specifying appropriate data types and constraints. Also, create seprate table for multi-valued attributes like Member for the Group entity.
- Set primary keys (PK) for unique identification of records in each table.
Foreign Keys and Relationships:
- Establish foreign keys (FK) to represent relationships between entities, linking tables through common attributes.
- Ensure referential integrity by enforcing foreign key constraints, which maintain consistent and valid references between related tables.
Handling Many-to-Many Relationships:
- Introduce associative (junction) tables to resolve many-to-many relationships into two one-to-many relationships.
- Include composite primary keys in associative tables, typically combining the primary keys of the related entities.

Here is the final relational schema diagram for Instagram.

Relational Schema Diagram for the Instagram

Now, let's translate the ER model into SQL tables.

1. User Table

CREATE TABLE User (
    User_ID INT PRIMARY KEY,
    Username VARCHAR(50) UNIQUE,
    Full_Name VARCHAR(100),
    Email VARCHAR(100),
    Phone_Number VARCHAR(15),
    Date_Joined DATE,
    Profile_Picture VARCHAR(255)
);

2. Post Table

CREATE TABLE Post (
    Post_ID INT PRIMARY KEY,
    Caption TEXT,
    Post_Date DATE,
    User_ID INT,
    FOREIGN KEY (User_ID) REFERENCES User(User_ID)
);

3. Comment Table

CREATE TABLE Comment (
    Comment_ID INT PRIMARY KEY,
    Text TEXT,
    Comment_Date DATE,
    User_ID INT,
    Post_ID INT,
    FOREIGN KEY (User_ID) REFERENCES User(User_ID),
    FOREIGN KEY (Post_ID) REFERENCES Post(Post_ID)
);

4. Like Table

CREATE TABLE Like (
    Like_ID INT PRIMARY KEY,
    User_ID INT,
    Post_ID INT,
    FOREIGN KEY (User_ID) REFERENCES User(User_ID),
    FOREIGN KEY (Post_ID) REFERENCES Post(Post_ID)
);

5. Follow Table

CREATE TABLE Follow (
    Follow_ID INT PRIMARY KEY,
    Follower_ID INT,
    Followed_ID INT,
    FOREIGN KEY (Follower_ID) REFERENCES User(User_ID),
    FOREIGN KEY (Followed_ID) REFERENCES User(User_ID)
);

6. Direct_Message Table

CREATE TABLE Direct_Message (
    Message_ID INT PRIMARY KEY,
    Message_Text TEXT,
    Sent_Date DATE,
    Sender_ID INT,
    Recipient_ID INT,
    FOREIGN KEY (Sender_ID) REFERENCES User(User_ID),
    FOREIGN KEY (Recipient_ID) REFERENCES User(User_ID)
);

7. Notification Table

CREATE TABLE Notification (
    Notification_ID INT PRIMARY KEY,
    Notification_Text TEXT,
    Notification_Date DATE,
    User_ID INT,
    FOREIGN KEY (User_ID) REFERENCES User(User_ID)
);

8. Group Table

CREATE TABLE Group (
    Group_ID INT PRIMARY KEY,
    Group_Name VARCHAR(100),
    Description TEXT,
    Created_By INT,
    FOREIGN KEY (Created_By) REFERENCES User(User_ID)
);

9. Group_Member Table

CREATE TABLE Group_Member (
    Group_ID INT,
    User_ID INT,
    Join_Date DATE,
    PRIMARY KEY (Group_ID, User_ID),
    FOREIGN KEY (Group_ID) REFERENCES Group(Group_ID),
    FOREIGN KEY (User_ID) REFERENCES User(User_ID)
);

10. Profile_Info Table

CREATE TABLE Profile_Info (
    User_ID INT PRIMARY KEY,
    Bio TEXT,
    Website VARCHAR(100),
    Number_of_Posts INT,
    Number_of_Followers INT,
    Number_of_Following INT,
    FOREIGN KEY (User_ID) REFERENCES User(User_ID)
);

11. Profile_Dashboard Table

CREATE TABLE Profile_Dashboard (
    User_ID INT PRIMARY KEY,
    Total_Likes INT,
    Total_Comments INT,
    Total_Groups_Joined INT,
    Total_Posts_Shared INT,
    FOREIGN KEY (User_ID) REFERENCES User(User_ID)
);

.....

Like the course? Get enrolled and start learning!