Database

Beauty Sallon App – MYSQL Database Design

devtechtutor.com
devtechtutor.com
01/07/2024
Updated on 01/07/2024
20 Mins Read
59 Views
0 Comments
https://www.alphaservesp.com/blog/what-is-sql-database-structure-types-examples

Building a comprehensive and efficient Beauty Salon app involves meticulous planning and precise implementation of various features tailored to meet the needs of customers, salon owners, and administrators. This documentation outlines the MySQL database design, including the Entity-Relationship (ER) diagram, relationships, entities, and attributes essential for the app’s backend development. Additionally, it breaks down the application into modular components, allowing for a step-by-step development process.

Overview of the Beauty Salon App

The Beauty Salon app aims to create a seamless platform where users can easily book salon services, salon owners can manage their businesses, and administrators can oversee the entire ecosystem. The app is divided into three primary panels:

  1. Customer Panel: Focuses on user-centric functionalities such as service selection, booking appointments, making payments, and leaving reviews.
  2. Owner Panel: Designed to meet the specific needs of salon professionals, enabling them to manage their salons, services, and customer requests efficiently.
  3. Admin Panel: Empowers administrators with complete control over the app, including managing users, salons, and payments, and ensuring the smooth operation of the platform.

Key Features

Customer Panel

  • User Registration: Streamlined sign-up processes for new users.
  • Selecting Services: Allows users to choose preferred services and salons.
  • Profiles: Enables users to view profiles of beauticians or salon professionals.
  • Bookings: Facilitates the request and booking of appointments.
  • Payment: Incorporates secure payment methods for seamless transactions.
  • Reviews: Permits users to rate salons and experts based on service quality.
  • Subscriptions: Offers subscription models for regular salon services.
  • Chat: Enables quick communication within the app.

Owner Panel

  • Registration: Allows the registration of one or multiple salons.
  • Digital Catalog: Showcases a variety of services offered.
  • User Requests: Manages appointment requests efficiently.
  • Professional Profiles: Facilitates the addition and management of salon experts’ profiles.
  • Service History: Provides a comprehensive view of past services for strategic management.

Admin Panel

  • User/Stylist/Salon Management: Exercises control over all profiles and actions.
  • Approval: Approves or disapproves registrations based on specified criteria.
  • Payment Management: Manages transactions and user payments securely.
  • Monetization: Offers options for generating revenue through the app, such as ads, subscriptions, and promotions.

Revenue Model

The app aims to generate revenue through several strategies, including:

  • Prominent Listings: Charging salons for enhanced visibility.
  • Commission Fees: Earning a percentage from each transaction.
  • Subscription Packages: Offering subscription plans for regular services.
  • Advertisements: Introducing in-app ads for additional income.
  • Subscriptions: Providing premium subscriptions for discounted services.

Database Design and Modules

This documentation will now detail the database design, including the ER diagram, entities, attributes, and relationships. It will then break down the development process into modules, each focusing on specific functionalities and features of the app. This modular approach ensures a clear and structured pathway for building the app step-by-step, enabling developers to create a robust and scalable Beauty Salon application.

Setting Up MySQL with Apache and Using MySQL Workbench

To start with creating the User Management Module in your Beauty Salon app, you first need to set up a MySQL database environment. This guide will walk you through the process of installing Apache, MySQL, and using MySQL Workbench to create your database.

Step 1: Install Apache

Apache is a widely used web server software. Although it’s not strictly necessary for running MySQL, it’s often used in conjunction with MySQL in web development environments (e.g., LAMP stack: Linux, Apache, MySQL, PHP).

Installation on Windows

  1. Download XAMPP:

    • Go to the XAMPP website and download the XAMPP installer for Windows.

  2. Install XAMPP:

    • Run the installer and follow the on-screen instructions. Ensure that you select Apache and MySQL during the installation process.

  3. Start Apache:

    • Open the XAMPP Control Panel and click “Start” next to Apache.

Installation on macOS

  1. Download XAMPP:

    • Go to the XAMPP website and download the XAMPP installer for macOS.

  2. Install XAMPP:

    • Run the installer and follow the on-screen instructions. Ensure that you select Apache and MySQL during the installation process.

  3. Start Apache:

    • Open the XAMPP Control Panel and click “Start” next to Apache.

Installation on Linux

  1. Install Apache:

    • Open a terminal and run the following commands
sudo apt update
sudo apt install apache2

Start Apache:

  • Start the Apache service with:
sudo systemctl start apache2

Step 2: Install MySQL

Installation on Windows

  1. Download MySQL Installer:

    • Go to the MySQL website and download the MySQL Installer for Windows.

  2. Install MySQL:

    • Run the installer and choose the “Developer Default” setup type.
    • Follow the on-screen instructions to complete the installation, including setting a root password.

  3. Start MySQL:

    • Open the MySQL Workbench or use the XAMPP Control Panel to start the MySQL service.

Installation on macOS

  1. Download MySQL DMG Archive:

  2. Install MySQL:

    • Open the DMG file and run the installer. Follow the on-screen instructions, including setting a root password.

  3. Start MySQL:

    • Start MySQL from the System Preferences or use terminal commands
sudo /usr/local/mysql/support-files/mysql.server start

Installation on Linux

  1. Install MySQL:

    • Open a terminal and run the following commands
sudo apt update
sudo apt install mysql-server

Secure MySQL Installation:

  • Run the security script to set the root password and secure MySQL
sudo mysql_secure_installation

Start MySQL:

  • Start the MySQL service with
sudo systemctl start mysql

Step 3: Using MySQL Workbench

MySQL Workbench is a powerful tool for managing MySQL databases. It provides a graphical interface for designing, creating, and managing databases.

Installation of MySQL Workbench

  1. Download MySQL Workbench:

  2. Install MySQL Workbench:

    • Run the installer and follow the on-screen instructions to complete the installation.

Connecting to MySQL Server

  1. Open MySQL Workbench:

    • Launch MySQL Workbench from your applications menu.

  2. Create a New Connection:

    • Click the “+” button next to “MySQL Connections”.
    • Enter the connection details:
      • Connection Name: Name your connection (e.g., Local MySQL).
      • Hostname: Usually localhost if running locally.
      • Port: Default is 3306.
      • Username: root or your MySQL username.
      • Password: Enter your password (or click “Store in Vault” to save it).

  3. Test Connection:

    • Click “Test Connection” to ensure that MySQL Workbench can connect to your MySQL server.
    • Click “OK” to save the connection.

Creating the Database

  1. Open the Connection:

    • Double-click your newly created connection to open it.

  2. Create a New Database:

    • Click on the “Create a new schema in the connected server” icon (a database icon with a plus sign).
    • Enter a name for your database (e.g., BeautySalon).
    • Click “Apply”, review the SQL script, and then click “Apply” again to create the database.

  3. Select the Database:

    • In the Navigator panel on the left, right-click on your new database and select “Set as Default Schema”.

Module 1: User Management Module

Purpose: The User Management Module handles user registration, profile management, and authentication. This module ensures that users can create accounts, manage their profiles, and securely log in to the app.

Components:

  • User Registration
  • User Profile
  • User Authentication

Entities and Attributes:

  1. User
    • UserID: INT, Primary Key, Auto Increment
    • Name: VARCHAR(100)
    • Email: VARCHAR(100), Unique
    • Password: VARCHAR(100)
    • Phone: VARCHAR(15)
    • Address: VARCHAR(255)
    • Created_At: TIMESTAMP

ER Diagram for User Management Module

ER Diagram:

The ER Diagram for the User Management Module consists of a single entity User with attributes to store user details. Here’s a textual representation of the diagram:

+--------------+
|    User      |
+--------------+
| UserID       |
| Name         |
| Email        |
| Password     |
| Phone        |
| Address      |
| Created_At   |
+--------------+

Explanation of Components

  1. User Registration:

    • Users will provide their Name, Email, Password, Phone, and Address.
    • The Created_At attribute will capture the timestamp when the user registers.

  2. User Profile:

    • Users can update their profile information, such as Name, Phone, and Address.
    • The Email attribute should remain unique and unchangeable to avoid conflicts.

  3. User Authentication:

    • Users will log in using their Email and Password.
    • Ensure passwords are stored securely, ideally using hashing algorithms.

Implementation

Step 4: Creating the User Table

Now that the database is created, you can create the User table.

  1. Open SQL Editor:

    • Click on the “Create a new SQL tab for executing queries” icon (a sheet of paper with a SQL symbol).

  2. Write the SQL Script:

    • Enter the following SQL script to create the User table
CREATE TABLE User (
    UserID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) NOT NULL UNIQUE,
    Password VARCHAR(100) NOT NULL,
    Phone VARCHAR(15),
    Address VARCHAR(255),
    Created_At TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

  • Execute the Script:

    • Click the “Execute” button (a lightning bolt icon) to run the script.

  • Verify the Table Creation:

    • In the Navigator panel, expand your database and the “Tables” section to see the newly created User table.

The User Management Module is fundamental to the Beauty Salon app, providing essential functionalities for user registration, profile management, and authentication. By implementing this module, you lay the foundation for user interactions within the app.

Module 2: Salon Management Module

Purpose: The Salon Management Module manages salon information, beautician profiles, and services offered. This module allows salon owners to register their salons, add beautician profiles, and manage the services they offer.

Components:

  • Salon Registration
  • Beautician Management
  • Service Management

Entities and Attributes:

  1. Salon

    • SalonID: INT, Primary Key, Auto Increment
    • Name: VARCHAR(100)
    • Address: VARCHAR(255)
    • Phone: VARCHAR(15)
    • OwnerID: INT, Foreign Key

  2. Beautician

    • BeauticianID: INT, Primary Key, Auto Increment
    • Name: VARCHAR(100)
    • Email: VARCHAR(100), Unique
    • Phone: VARCHAR(15)
    • Profile: TEXT
    • SalonID: INT, Foreign Key

  3. Service

    • ServiceID: INT, Primary Key, Auto Increment
    • Name: VARCHAR(100)
    • Description: TEXT
    • Price: DECIMAL(10, 2)
    • SalonID: INT, Foreign Key

ER Diagram for Salon Management Module

ER Diagram:

The ER Diagram for the Salon Management Module includes three entities: Salon, Beautician, and Service. Here’s a textual representation of the diagram:

+--------------+          +--------------+           +--------------+
|    Salon     | 1----n   |  Beautician  | 1----n    |   Service    |
+--------------+          +--------------+           +--------------+
| SalonID (PK) |          | BeauticianID (PK)|       | ServiceID (PK)|
| Name         |          | Name         |           | Name         |
| Address      |          | Email        |           | Description  |
| Phone        |          | Phone        |           | Price        |
| OwnerID (FK) |          | Profile      |           | SalonID (FK) |
+--------------+          | SalonID (FK) |           +--------------+
                          +--------------+

Here’s a simple description of the diagram components:

  1. Salon Entity:

    • Represented by a rectangle labeled “Salon”.
    • Contains attributes with SalonID marked as the primary key (PK) and OwnerID as a foreign key (FK).

  2. Beautician Entity:

    • Represented by a rectangle labeled “Beautician”.
    • Contains attributes with BeauticianID marked as the primary key (PK) and SalonID as a foreign key (FK).

  3. Service Entity:

    • Represented by a rectangle labeled “Service”.
    • Contains attributes with ServiceID marked as the primary key (PK) and SalonID as a foreign key (FK).

SQL Script for Creating Tables

To implement the Salon Management Module, you will need to create the Salon, Beautician, and Service tables in your MySQL database using the following SQL scripts:

CREATE TABLE Salon (
SalonID INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Address VARCHAR(255) NOT NULL,
Phone VARCHAR(15) NOT NULL,
OwnerID INT NOT NULL,
FOREIGN KEY (OwnerID) REFERENCES User(UserID)
);

CREATE TABLE Beautician (
BeauticianID INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) NOT NULL UNIQUE,
Phone VARCHAR(15) NOT NULL,
Profile TEXT,
SalonID INT NOT NULL,
FOREIGN KEY (SalonID) REFERENCES Salon(SalonID)
);

CREATE TABLE Service (
ServiceID INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Description TEXT NOT NULL,
Price DECIMAL(10, 2) NOT NULL,
SalonID INT NOT NULL,
FOREIGN KEY (SalonID) REFERENCES Salon(SalonID)
);

The Salon Management Module is crucial for managing the operational aspects of salons within the Beauty Salon app. By implementing this module, salon owners can effectively manage their business information, beautician profiles, and service offerings.

Module 3: Booking Management Module

Purpose: The Booking Management Module handles service booking by users. This module enables users to select services, request bookings, and manage booking statuses.

Components:

  • Service Selection
  • Booking Request
  • Booking Status Management

Entities and Attributes:

  1. Booking
    • BookingID: INT, Primary Key, Auto Increment
    • UserID: INT, Foreign Key
    • ServiceID: INT, Foreign Key
    • BookingDate: DATE
    • Status: ENUM(‘Pending’, ‘Confirmed’, ‘Cancelled’)
    • Created_At: TIMESTAMP

ER Diagram for Booking Management Module

ER Diagram:

The ER Diagram for the Booking Management Module includes the Booking entity and its relationships with the User and Service entities. Here’s a textual representation of the diagram:

+--------------+          +--------------+           +--------------+
|    User      | 1----n   |   Booking    |  n----1   |   Service    |
+--------------+          +--------------+           +--------------+
| UserID (PK)  |          | BookingID (PK)|          | ServiceID (PK)|
| ...          |          | UserID (FK)  |           | ...          |
+--------------+          | ServiceID (FK)|           +--------------+
                          | BookingDate   |
                          | Status        |
                          | Created_At    |
                          +--------------+

To create a visual representation of the ER Diagram, you can use tools such as draw.io, Lucidchart, or any other ERD software. Here’s a simple description of the diagram components:

  1. User Entity:

    • Represented by a rectangle labeled “User”.
    • Contains attributes with UserID marked as the primary key (PK).

  2. Booking Entity:

    • Represented by a rectangle labeled “Booking”.
    • Contains attributes with BookingID marked as the primary key (PK), and UserID and ServiceID as foreign keys (FK).

  3. Service Entity:

    • Represented by a rectangle labeled “Service”.
    • Contains attributes with ServiceID marked as the primary key (PK).

SQL Script for Creating Tables

To implement the Booking Management Module, you will need to create the Booking table in your MySQL database using the following SQL script:

CREATE TABLE Booking (
    BookingID INT AUTO_INCREMENT PRIMARY KEY,
    UserID INT NOT NULL,
    ServiceID INT NOT NULL,
    BookingDate DATE NOT NULL,
    Status ENUM('Pending', 'Confirmed', 'Cancelled') NOT NULL,
    Created_At TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (UserID) REFERENCES User(UserID),
    FOREIGN KEY (ServiceID) REFERENCES Service(ServiceID)
);

Explanation of Components

  1. Service Selection:

    • Users browse and select the services they want to book.
    • Services are linked to salons, and users can view details before booking.

  2. Booking Request:

    • Users can request a booking for selected services.
    • The booking details include UserID, ServiceID, BookingDate, and Status.

  3. Booking Status Management:

    • Users and salon owners can update the booking status (Pending, Confirmed, Cancelled).
    • Status updates help manage appointments effectively.

The Booking Management Module is vital for facilitating service bookings within the Beauty Salon app. By implementing this module, users can seamlessly book services, and salon owners can manage appointments efficiently.

Module 4: Payment Management Module

Purpose: The Payment Management Module manages payments made by users for bookings. This module ensures that users can securely process payments, and both users and salon owners can view payment histories.

Components:

  • Payment Processing
  • Payment History

Entities and Attributes:

  1. Payment
    • PaymentID: INT, Primary Key, Auto Increment
    • BookingID: INT, Foreign Key
    • UserID: INT, Foreign Key
    • Amount: DECIMAL(10, 2)
    • PaymentDate: TIMESTAMP
    • PaymentMethod: ENUM(‘Credit Card’, ‘Debit Card’, ‘PayPal’)

ER Diagram for Payment Management Module

ER Diagram:

The ER Diagram for the Payment Management Module includes the Payment entity and its relationships with the User and Booking entities. Here’s a textual representation of the diagram:

+--------------+          +--------------+           +--------------+
|    User      | 1----n   |   Payment    | n----1    |   Booking    |
+--------------+          +--------------+           +--------------+
| UserID (PK)  |          | PaymentID (PK)|          | BookingID (PK)|
| ...          |          | BookingID (FK)|          | UserID (FK)  |
+--------------+          | UserID (FK)  |           | ServiceID (FK)|
                          | Amount       |           | ...          |
                          | PaymentDate  |
                          | PaymentMethod|
                          +--------------+

SQL Script for Creating Tables

To implement the Payment Management Module, you will need to create the Payment table in your MySQL database using the following SQL script:

CREATE TABLE Payment (
    PaymentID INT AUTO_INCREMENT PRIMARY KEY,
    BookingID INT NOT NULL,
    UserID INT NOT NULL,
    Amount DECIMAL(10, 2) NOT NULL,
    PaymentDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PaymentMethod ENUM('Credit Card', 'Debit Card', 'PayPal') NOT NULL,
    FOREIGN KEY (BookingID) REFERENCES Booking(BookingID),
    FOREIGN KEY (UserID) REFERENCES User(UserID)
);

Explanation of Components

  1. Payment Processing:

    • Users can make payments for their bookings.
    • The payment details include BookingID, UserID, Amount, PaymentDate, and PaymentMethod.

  2. Payment History:

    • Users and salon owners can view the history of payments.
    • This history helps in tracking financial transactions and ensuring accurate record-keeping.

The Payment Management Module is essential for handling financial transactions within the Beauty Salon app. By implementing this module, users can securely process payments, and both users and salon owners can track payment histories effectively.

Module 5: Review Management Module

Purpose: The Review Management Module allows users to rate and review services. This module enables users to provide feedback on services, which helps other users make informed decisions and helps salon owners improve their offerings.

Components:

  • Write Review
  • View Reviews

Entities and Attributes:

  1. Review
    • ReviewID: INT, Primary Key, Auto Increment
    • UserID: INT, Foreign Key
    • ServiceID: INT, Foreign Key
    • Rating: INT (1-5)
    • Comment: TEXT
    • Created_At: TIMESTAMP

ER Diagram for Review Management Module

ER Diagram:

The ER Diagram for the Review Management Module includes the Review entity and its relationships with the User and Service entities. Here’s a textual representation of the diagram:

+--------------+          +--------------+           +--------------+
|    User      | 1----n   |   Review     | n----1    |   Service    |
+--------------+          +--------------+           +--------------+
| UserID (PK)  |          | ReviewID (PK)|           | ServiceID (PK)|
| ...          |          | UserID (FK)  |           | ...          |
+--------------+          | ServiceID (FK)|           +--------------+
                          | Rating       |
                          | Comment      |
                          | Created_At   |
                          +--------------+

To create a visual representation of the ER Diagram, you can use tools such as draw.io, Lucidchart, or any other ERD software. Here’s a simple description of the diagram components:

  1. User Entity:

    • Represented by a rectangle labeled “User”.
    • Contains attributes with UserID marked as the primary key (PK).

  2. Service Entity:

    • Represented by a rectangle labeled “Service”.
    • Contains attributes with ServiceID marked as the primary key (PK).

  3. Review Entity:

    • Represented by a rectangle labeled “Review”.
    • Contains attributes with ReviewID marked as the primary key (PK), and UserID and ServiceID as foreign keys (FK).

SQL Script for Creating Tables

To implement the Review Management Module, you will need to create the Review table in your MySQL database using the following SQL script:

CREATE TABLE Review (
    ReviewID INT AUTO_INCREMENT PRIMARY KEY,
    UserID INT NOT NULL,
    ServiceID INT NOT NULL,
    Rating INT CHECK (Rating BETWEEN 1 AND 5),
    Comment TEXT,
    Created_At TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (UserID) REFERENCES User(UserID),
    FOREIGN KEY (ServiceID) REFERENCES Service(ServiceID)
);

Explanation of Components

  1. Write Review:

    • Users can write reviews for services they have availed.
    • Reviews include a Rating (1-5) and a Comment.

  2. View Reviews:

    • Users can view reviews for different services.
    • Reviews help users make informed decisions based on others’ experiences.

The Review Management Module is vital for gathering user feedback and helping other users make informed choices. By implementing this module, the Beauty Salon app can ensure that users have a voice in evaluating services and salon owners can receive valuable feedback for improvement.

The beauty and wellness industry is rapidly evolving, and the demand for digital solutions that streamline operations and enhance customer experiences is at an all-time high. In this context, a comprehensive beauty salon application can significantly benefit both customers and salon owners by providing a seamless, integrated platform for booking services, managing appointments, and more. This article outlines the database design for such an application using MongoDB, including detailed explanations of the ER (Entity-Relationship) and EVA (Entity-Value-Attribute) diagrams, relationships, entities, and attributes.

Purpose

The primary purpose of this article is to serve as a comprehensive guide for designing the backend of a beauty salon application. By following this guide, developers can ensure a robust and scalable database structure that supports the application’s core functionalities. The design aims to cater to the needs of various user roles, including customers, salon owners, and administrators, providing them with tailored features and efficient data management.

Scope

This documentation covers:

  • Entity Definitions: Detailed descriptions of entities involved in the application.
  • Attribute Specifications: Attributes associated with each entity.
  • Relationships: How entities relate to each other.
  • Diagrams: Visual representations of the database design through ER and EVA diagrams.
  • Modular Development Approach: A step-by-step guide for implementing the application in modules, ensuring a clear and organized development process.

Features Breakdown

The application is divided into three main panels, each with specific features:

  1. Customer Panel:

    • User Registration
    • Selecting Services
    • Viewing Profiles of Beauticians or Salon Professionals
    • Booking Appointments
    • Secure Payment Methods
    • Rating and Reviewing Salons and Experts
    • Subscription Models
    • In-app Chat

  2. Owner Panel:

    • Salon Registration
    • Digital Catalog of Services
    • Managing Appointment Requests
    • Adding and Managing Professional Profiles
    • Viewing Service History

  3. Admin Panel:

    • Managing User, Stylist, and Salon Profiles
    • Approving or Disapproving Registrations
    • Payment Management
    • Generating Revenue through Ads, Subscriptions, and Promotions

Revenue Model

The revenue generation for the salon app involves multiple strategies to ensure profitability and business growth. These strategies include:

  • Prominent Listings for enhanced visibility.
  • Commission Fees for transactions facilitated through the app.
  • Subscription Packages offering monthly services and reward points.
  • Advertisements within the app.
  • Subscription Fees for discounted services.

This article focuses on the backend implementation using MongoDB, providing a detailed database design to support the application’s functionalities. By following the outlined design and modular approach, developers can create a scalable and efficient beauty salon application tailored to the industry’s needs

Module 6: Subscription Management Module

Purpose: The Subscription Management Module manages user subscriptions for salon services. This module allows users to subscribe to monthly or yearly plans, providing them with access to services and rewards.

Components:

  • Subscription Plans
  • Subscription Management

Entities and Attributes:

  1. Subscription
    • SubscriptionID: INT, Primary Key, Auto Increment
    • UserID: INT, Foreign Key
    • Type: ENUM(‘Monthly’, ‘Yearly’)
    • StartDate: DATE
    • EndDate: DATE
    • Created_At: TIMESTAMP

ER Diagram for Subscription Management Module

ER Diagram:

The ER Diagram for the Subscription Management Module includes the Subscription entity and its relationship with the User entity. Here’s a textual representation of the diagram:

+--------------+          +-----------------+
|    User      | 1----n   |  Subscription   |
+--------------+          +-----------------+
| UserID (PK)  |          | SubscriptionID (PK) |
| ...          |          | UserID (FK)     |
+--------------+          | Type            |
                          | StartDate       |
                          | EndDate         |
                          | Created_At      |
                          +-----------------+

To create a visual representation of the ER Diagram, you can use tools such as draw.io, Lucidchart, or any other ERD software. Here’s a simple description of the diagram components:

  1. User Entity:

    • Represented by a rectangle labeled “User”.
    • Contains attributes with UserID marked as the primary key (PK).

  2. Subscription Entity:

    • Represented by a rectangle labeled “Subscription”.
    • Contains attributes with SubscriptionID marked as the primary key (PK), and UserID as a foreign key (FK).

SQL Script for Creating Tables

To implement the Subscription Management Module, you will need to create the Subscription table in your MySQL database using the following SQL script:

CREATE TABLE Subscription (
    SubscriptionID INT AUTO_INCREMENT PRIMARY KEY,
    UserID INT NOT NULL,
    Type ENUM('Monthly', 'Yearly') NOT NULL,
    StartDate DATE NOT NULL,
    EndDate DATE NOT NULL,
    Created_At TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (UserID) REFERENCES User(UserID)
);

Explanation of Components

  1. Subscription Plans:

    • Users can choose from subscription plans (Monthly or Yearly).
    • Plans provide access to specific services and rewards.

  2. Subscription Management:

    • Users can manage their subscriptions, view start and end dates, and track their subscription status.

The Subscription Management Module is essential for providing users with flexible service options and generating recurring revenue for the Beauty Salon app. By implementing this module, users can subscribe to plans that best fit their needs, and salon owners can offer attractive packages to retain customers.

Module 7: Chat Module

Purpose: The Chat Module facilitates communication between users and beauticians. This module enables users to send messages, ask questions, and receive responses from beauticians, improving customer service and engagement.

Components:

  • Messaging System

Entities and Attributes:

  1. Chat
    • ChatID: INT, Primary Key, Auto Increment
    • UserID: INT, Foreign Key
    • BeauticianID: INT, Foreign Key
    • Message: TEXT
    • Sent_At: TIMESTAMP

ER Diagram for Chat Module

ER Diagram:

The ER Diagram for the Chat Module includes the Chat entity and its relationships with the User and Beautician entities. Here’s a textual representation of the diagram:

+--------------+          +--------------+           +--------------+
|    User      | 1----n   |    Chat      | n----1    |  Beautician  |
+--------------+          +--------------+           +--------------+
| UserID (PK)  |          | ChatID (PK)  |           | BeauticianID (PK)|
| ...          |          | UserID (FK)  |           | ...          |
+--------------+          | BeauticianID (FK) |      +--------------+
                          | Message      |
                          | Sent_At      |
                          +--------------+

To create a visual representation of the ER Diagram, you can use tools such as draw.io, Lucidchart, or any other ERD software. Here’s a simple description of the diagram components:

  1. User Entity:

    • Represented by a rectangle labeled “User”.
    • Contains attributes with UserID marked as the primary key (PK).

  2. Beautician Entity:

    • Represented by a rectangle labeled “Beautician”.
    • Contains attributes with BeauticianID marked as the primary key (PK).

  3. Chat Entity:

    • Represented by a rectangle labeled “Chat”.
    • Contains attributes with ChatID marked as the primary key (PK), and UserID and BeauticianID as foreign keys (FK).

SQL Script for Creating Tables

To implement the Chat Module, you will need to create the Chat table in your MySQL database using the following SQL script:

CREATE TABLE Chat (
    ChatID INT AUTO_INCREMENT PRIMARY KEY,
    UserID INT NOT NULL,
    BeauticianID INT NOT NULL,
    Message TEXT NOT NULL,
    Sent_At TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (UserID) REFERENCES User(UserID),
    FOREIGN KEY (BeauticianID) REFERENCES Beautician(BeauticianID)
);

Explanation of Components

  1. Messaging System:
    • Users can send messages to beauticians.
    • Beauticians can respond to user inquiries.
    • Messages include text and a timestamp.

The Chat Module is essential for improving communication between users and beauticians, enhancing customer service and engagement. By implementing this module, the Beauty Salon app can provide a direct line of communication for users to ask questions, receive answers, and build relationships with beauticians.

Module 8: Admin Management Module

Purpose: The Admin Management Module empowers administrators with complete control over the app, including managing users, salons, beauticians, and services. This module ensures the smooth operation of the platform by providing oversight and approval functionalities.

Components:

  • User Management
  • Salon Management
  • Beautician Management
  • Service Approval

Entities and Attributes:

  1. Admin
    • AdminID: INT, Primary Key, Auto Increment
    • Name: VARCHAR(100)
    • Email: VARCHAR(100), Unique
    • Password: VARCHAR(100)
    • Phone: VARCHAR(15)
    • Created_At: TIMESTAMP

ER Diagram for Admin Management Module

ER Diagram:

The ER Diagram for the Admin Management Module includes the Admin entity and its relationships with the User, Salon, Beautician, and Service entities. Here’s a textual representation of the diagram:

+--------------+          +--------------+
|    Admin     |          |    User      |
+--------------+          +--------------+
| AdminID (PK) |          | UserID (PK)  |
| Name         |          | ...          |
| Email        |          +--------------+
| Password     |          +--------------+
| Phone        |          |    Salon     |
| Created_At   |          +--------------+
+--------------+          | SalonID (PK) |
                          | ...          |
                          +--------------+
                          +--------------+
                          |  Beautician  |
                          +--------------+
                          | BeauticianID (PK) |
                          | ...          |
                          +--------------+
                          +--------------+
                          |   Service    |
                          +--------------+
                          | ServiceID (PK) |
                          | ...          |
                          +--------------+

SQL Script for Creating Tables

To implement the Admin Management Module, you will need to create the Admin table in your MySQL database using the following SQL script:

CREATE TABLE Admin (
    AdminID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) NOT NULL UNIQUE,
    Password VARCHAR(100) NOT NULL,
    Phone VARCHAR(15),
    Created_At TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Explanation of Components

  1. User Management:

    • Admins can manage user profiles, including viewing, editing, and deleting users.

  2. Salon Management:

    • Admins can manage salon profiles, including approving new salons, editing salon details, and removing salons.

  3. Beautician Management:

    • Admins can manage beautician profiles, including approving new beauticians, editing beautician details, and removing beauticians.

  4. Service Approval:

    • Admins can manage services offered by salons, including approving new services and editing or removing existing services.

Conclusion

The Admin Management Module is crucial for maintaining the overall control and smooth operation of the Beauty Salon app. By implementing this module, administrators can ensure that all aspects of the platform, from user accounts to service offerings, are managed effectively and efficiently.

Module 9: Extras

Now that we have outlined the individual modules and their corresponding entities, relationships, and attributes, the next step is to integrate these modules into a cohesive backend system using MySQL. This section will summarize the entire database structure and provide SQL scripts for creating all the tables necessary for the Beauty Salon app.

Complete ER Diagram

The complete ER Diagram for the Beauty Salon app incorporates all entities and their relationships. Here’s a summary of the key entities and relationships:

  1. User

    • UserID: INT, Primary Key, Auto Increment
    • Name: VARCHAR(100)
    • Email: VARCHAR(100), Unique
    • Password: VARCHAR(100)
    • Phone: VARCHAR(15)
    • Address: VARCHAR(255)
    • Created_At: TIMESTAMP

  2. Salon

    • SalonID: INT, Primary Key, Auto Increment
    • Name: VARCHAR(100)
    • Address: VARCHAR(255)
    • Phone: VARCHAR(15)
    • OwnerID: INT, Foreign Key (UserID)

  3. Beautician

    • BeauticianID: INT, Primary Key, Auto Increment
    • Name: VARCHAR(100)
    • Email: VARCHAR(100), Unique
    • Phone: VARCHAR(15)
    • Profile: TEXT
    • SalonID: INT, Foreign Key (SalonID)

  4. Service

    • ServiceID: INT, Primary Key, Auto Increment
    • Name: VARCHAR(100)
    • Description: TEXT
    • Price: DECIMAL(10, 2)
    • SalonID: INT, Foreign Key (SalonID)

  5. Booking

    • BookingID: INT, Primary Key, Auto Increment
    • UserID: INT, Foreign Key (UserID)
    • ServiceID: INT, Foreign Key (ServiceID)
    • BookingDate: DATE
    • Status: ENUM(‘Pending’, ‘Confirmed’, ‘Cancelled’)
    • Created_At: TIMESTAMP

  6. Payment

    • PaymentID: INT, Primary Key, Auto Increment
    • BookingID: INT, Foreign Key (BookingID)
    • UserID: INT, Foreign Key (UserID)
    • Amount: DECIMAL(10, 2)
    • PaymentDate: TIMESTAMP
    • PaymentMethod: ENUM(‘Credit Card’, ‘Debit Card’, ‘PayPal’)

  7. Review

    • ReviewID: INT, Primary Key, Auto Increment
    • UserID: INT, Foreign Key (UserID)
    • ServiceID: INT, Foreign Key (ServiceID)
    • Rating: INT (1-5)
    • Comment: TEXT
    • Created_At: TIMESTAMP

  8. Subscription

    • SubscriptionID: INT, Primary Key, Auto Increment
    • UserID: INT, Foreign Key (UserID)
    • Type: ENUM(‘Monthly’, ‘Yearly’)
    • StartDate: DATE
    • EndDate: DATE
    • Created_At: TIMESTAMP

  9. Chat

    • ChatID: INT, Primary Key, Auto Increment
    • UserID: INT, Foreign Key (UserID)
    • BeauticianID: INT, Foreign Key (BeauticianID)
    • Message: TEXT
    • Sent_At: TIMESTAMP

  10. Admin

    • AdminID: INT, Primary Key, Auto Increment
    • Name: VARCHAR(100)
    • Email: VARCHAR(100), Unique
    • Password: VARCHAR(100)
    • Phone: VARCHAR(15)
    • Created_At: TIMESTAMP

SQL Script for Creating All Tables

Here is the complete SQL script to create all the tables in your MySQL database:

-- Create User Table
CREATE TABLE User (
    UserID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) NOT NULL UNIQUE,
    Password VARCHAR(100) NOT NULL,
    Phone VARCHAR(15),
    Address VARCHAR(255),
    Created_At TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create Salon Table
CREATE TABLE Salon (
    SalonID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Address VARCHAR(255) NOT NULL,
    Phone VARCHAR(15) NOT NULL,
    OwnerID INT NOT NULL,
    FOREIGN KEY (OwnerID) REFERENCES User(UserID)
);

-- Create Beautician Table
CREATE TABLE Beautician (
    BeauticianID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) NOT NULL UNIQUE,
    Phone VARCHAR(15) NOT NULL,
    Profile TEXT,
    SalonID INT NOT NULL,
    FOREIGN KEY (SalonID) REFERENCES Salon(SalonID)
);

-- Create Service Table
CREATE TABLE Service (
    ServiceID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Description TEXT NOT NULL,
    Price DECIMAL(10, 2) NOT NULL,
    SalonID INT NOT NULL,
    FOREIGN KEY (SalonID) REFERENCES Salon(SalonID)
);

-- Create Booking Table
CREATE TABLE Booking (
    BookingID INT AUTO_INCREMENT PRIMARY KEY,
    UserID INT NOT NULL,
    ServiceID INT NOT NULL,
    BookingDate DATE NOT NULL,
    Status ENUM('Pending', 'Confirmed', 'Cancelled') NOT NULL,
    Created_At TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (UserID) REFERENCES User(UserID),
    FOREIGN KEY (ServiceID) REFERENCES Service(ServiceID)
);

-- Create Payment Table
CREATE TABLE Payment (
    PaymentID INT AUTO_INCREMENT PRIMARY KEY,
    BookingID INT NOT NULL,
    UserID INT NOT NULL,
    Amount DECIMAL(10, 2) NOT NULL,
    PaymentDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PaymentMethod ENUM('Credit Card', 'Debit Card', 'PayPal') NOT NULL,
    FOREIGN KEY (BookingID) REFERENCES Booking(BookingID),
    FOREIGN KEY (UserID) REFERENCES User(UserID)
);

-- Create Review Table
CREATE TABLE Review (
    ReviewID INT AUTO_INCREMENT PRIMARY KEY,
    UserID INT NOT NULL,
    ServiceID INT NOT NULL,
    Rating INT CHECK (Rating BETWEEN 1 AND 5),
    Comment TEXT,
    Created_At TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (UserID) REFERENCES User(UserID),
    FOREIGN KEY (ServiceID) REFERENCES Service(ServiceID)
);

-- Create Subscription Table
CREATE TABLE Subscription (
    SubscriptionID INT AUTO_INCREMENT PRIMARY KEY,
    UserID INT NOT NULL,
    Type ENUM('Monthly', 'Yearly') NOT NULL,
    StartDate DATE NOT NULL,
    EndDate DATE NOT NULL,
    Created_At TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (UserID) REFERENCES User(UserID)
);

-- Create Chat Table
CREATE TABLE Chat (
    ChatID INT AUTO_INCREMENT PRIMARY KEY,
    UserID INT NOT NULL,
    BeauticianID INT NOT NULL,
    Message TEXT NOT NULL,
    Sent_At TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (UserID) REFERENCES User(UserID),
    FOREIGN KEY (BeauticianID) REFERENCES Beautician(BeauticianID)
);

-- Create Admin Table
CREATE TABLE Admin (
    AdminID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) NOT NULL UNIQUE,
    Password VARCHAR(100) NOT NULL,
    Phone VARCHAR(15),
    Created_At TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This comprehensive SQL script covers the creation of all the necessary tables for the Beauty Salon app, integrating all the modules and their respective functionalities. By running this script, you can set up the backend database structure for your app, ready for further development and integration with the front-end and business logic components.

Enhancing the Admin Management Module with Search and Filtering Options

To add search and filtering capabilities for the admin within the Admin Management Module, we need to enhance our database design and consider how the SQL queries will be structured. This functionality will enable administrators to efficiently search and filter through users, salons, beauticians, and services.

Updated Entities and Attributes for Search and Filtering

The entities do not need additional attributes for search and filtering, but the implementation of search and filtering will involve writing SQL queries to handle these functionalities.

SQL Queries for Search and Filtering

  1. Search Users

    • Admins can search users by name, email, phone, and address
SELECT * FROM User
WHERE Name LIKE '%search_term%' OR
      Email LIKE '%search_term%' OR
      Phone LIKE '%search_term%' OR
      Address LIKE '%search_term%';

Search Salons

  • Admins can search salons by name, address, and phone.
SELECT * FROM Salon
WHERE Name LIKE '%search_term%' OR
      Address LIKE '%search_term%' OR
      Phone LIKE '%search_term%';

Search Beauticians

  • Admins can search beauticians by name, email, and phone.
SELECT * FROM Beautician
WHERE Name LIKE '%search_term%' OR
      Email LIKE '%search_term%' OR
      Phone LIKE '%search_term%';

Search Services

  • Admins can search services by name and description.
SELECT * FROM Service
WHERE Name LIKE '%search_term%' OR
      Description LIKE '%search_term%';

Filtering Options

  1. Filter Users by Registration Date

    • Admins can filter users by a date range.
SELECT * FROM User
WHERE Created_At BETWEEN 'start_date' AND 'end_date';

Filter Salons by Owner

  • Admins can filter salons by owner (User).
SELECT * FROM Salon
WHERE OwnerID = owner_id;

Filter Beauticians by Salon

  • Admins can filter beauticians by salon.
SELECT * FROM Beautician
WHERE SalonID = salon_id;

Filter Services by Price Range

  • Admins can filter services by a price range
SELECT * FROM Service
WHERE Price BETWEEN min_price AND max_price;

Adding search and filtering options to the Admin Management Module greatly enhances the admin’s ability to manage the platform efficiently. By implementing the provided SQL queries and ensuring that the backend API supports these functionalities, administrators can easily search and filter through various entities within the Beauty Salon app. This added functionality will improve the overall management experience and operational efficiency.

Please share this article if you like it!

Share It!
devtechtutor.com
Written By

devtechtutor.com

Welcome to DevTechTutor.com, your ultimate resource for mastering web development and technology! Whether you're a beginner eager to dive into coding or an experienced developer looking to sharpen your skills, DevTechTutor.com is here to guide you every step of the way. Our mission is to make learning web development accessible, engaging, and effective.

Other Articles

Share Article

Facebook
Twitter
LinkedIn
Reddit
Telegram
WhatsApp
Email
Threads
X
Print

Our site uses cookies. By using this site, you agree to the Privacy Policy and Terms of Use.

Accept