Chat Application Database Documentation

Database Overview

The Chat Application Model Database is designed to provide a robust and structured data storage solution for a chat application. It serves as the backbone for managing user accounts, friend relationships, and chat messaging features within the application.

This comprehensive documentation covers the database schema, tables, relationships, sample queries with outputs, and details about the implemented triggers.

For more details, you can also refer to the Chat Application Model Database SQL file on GitHub.

ER-Diagram

The ER-Diagram illustrates the entity-relationship model of the Chat Application Model Database. It visually represents the structure of the database, including entities, attributes, and relationships between them.

ER Diagram

For a detailed understanding of the database schema and relationships, refer to the ER-Diagram image provided above.

Database Schema

The database schema consists of four primary tables: admin, users, friends, and messages. Each table serves a distinct purpose in supporting various aspects of the chat application.

Users Table

The users table stores information about registered users in the chat application.

  • ID (INT, Primary Key): Unique identifier for each user.
  • Username (VARCHAR): User's username.
  • Email (VARCHAR): User's email address.
  • Status (VARCHAR): User's status, can be 'Active' or 'Inactive'.
  • AuthenticationTime (DATETIME): Timestamp of the user's last authentication.

Friends Table

The friends table maintains the relationships between users who are friends in the chat application.

  • ID (INT, Foreign Key): User ID.
  • FriendID (INT, Foreign Key): Friend's ID.

Messages Table

The messages table stores chat messages exchanged between users.

  • ID (INT, Primary Key): Unique identifier for each message.
  • FROMUID (INT, Foreign Key): Sender's ID.
  • ToUID (INT, Foreign Key): Receiver's ID.
  • MessageText (TEXT): The content of the message.
  • SentDt (DATETIME): Timestamp when the message was sent.
  • ReadStatus (VARCHAR): Message read status, can be 'Read' or 'Unread'.

Triggers

Triggers in the database are used to automatically perform actions or enforce rules when certain events occur in the tables. Here are the triggers implemented in the chat application model database:

  1. updateAuthenticationTime Trigger

    Function: Updates the AuthenticationTime of the sender when a new message is sent.

  2. updateUserKey Trigger

    Function: Updates the UserKey based on the user's Status before each update.

  3. updateUserKeyOnStatusChange Trigger

    Function: Updates UserKey when there is a change in the user's Status.

  4. updateAuthTimeOnFriendAdd Trigger

    Function: Updates AuthenticationTime for users involved in a new friendship.

  5. updateUserStatusOnNoFriends Trigger

    Function: Updates user status to 'Inactive' if they have no friends.

  6. updateUserStatusOnUnreadMessages Trigger

    Function: Updates user status to 'Active' if they receive a new unread message.

  7. deleteOldMessages Trigger

    Function: Deletes messages older than 6 months after a new message is inserted.

Stored Procedures

Stored procedures are precompiled SQL statements that are stored in the database and can be executed by the database management system. Here are the stored procedures implemented in the chat application model database:

  1. SendMessage Stored Procedure

    Function: Inserts a new message into the messages table.

    CALL SendMessage(1, 2, 'Hello, Sheetal!');

  2. markAllMessagesAsRead Stored Procedure

    Function: Marks all messages as read for a specific user.

    CALL markAllMessagesAsRead(1);

  3. deleteUserAndMessages Stored Procedure

    Function: Deletes a user and their associated messages.

    CALL deleteUserAndMessages(2);

  4. getFriendList Stored Procedure

    Function: Retrieves a list of friends for a user.

    CALL getFriendList(1);

  5. getLatestMessage Stored Procedure

    Function: Retrieves the latest message for a specific user.

    CALL getLatestMessage(1);

Queries

Queries are SQL statements used to retrieve specific data from the database. Here are some example queries along with their outputs:

  1. Retrieve all users and their friendships:

    SELECT u.ID AS UserID, u.Username, u.Email, f.FriendID, fu.Username AS FriendUsername, fu.Email AS FriendEmail
FROM users u
JOIN friends f ON u.ID = f.ID
JOIN users fu ON f.FriendID = fu.ID;

    Example Output:

    UserID Username Email FriendID FriendUsername FriendEmail
    1 Sudhanshu sudhanshu@email.com 2 Sheetal sheetal@email.com
    2 Sheetal sheetal@email.com 1 Sudhanshu sudhanshu@email.com

  2. Fetch messages sent by a specific user with sender and receiver details:

    SELECT m.ID AS MessageID, m.FROMUID AS SenderID, u1.Username AS SenderUsername,
       m.ToUID AS ReceiverID, u2.Username AS ReceiverUsername, m.SentDt AS SentDateTime, m.MessageText
FROM messages m
JOIN users u1 ON m.FROMUID = u1.ID
JOIN users u2 ON m.ToUID = u2.ID
WHERE m.FROMUID = 1;

    Example Output:

    MessageID SenderID SenderUsername ReceiverID ReceiverUsername SentDateTime MessageText
    1 1 Sudhanshu 2 Sheetal 2023-01-01 12:00 Hello, Sheetal!
    2 1 Sudhanshu 3 Arun 2023-01-02 14:30 Hi, Sudhanshu!