Building a Restaurant Management System with Azure Database for MySQL

What we'll build

• A restaurant database system that manages:
• Menu items and categories
• Customer orders and reservations
• Staff scheduling
• Inventory tracking

Prerequisites

To get the most out of this blog post, you should have a basic understanding of database concepts and familiarity with using SQL. In addition, before getting started, it’s important to ensure that the following prerequisites are in place.

1. An Azure account (you can create a free account at portal.azure.com - https://aka.ms/try-mysql-free )
2. MySQL Workbench or Azure Data Studio installed.

Get started with the codebase on GitHub

The complete source code for this project is available on GitHub: Restaurant Management System Repository

The repository includes:

• Database schema and setup scripts
• Sample data insertion scripts
• Stored procedures for common operations
• Example queries and usage patterns
• Sample code for a full-stack restaurant management application built with React and Node.js, using Azure Database for MySQL

To get started with the codebase:

git clone https://github.com/FarahAbdo/restaurant-management.git
cd restaurant-management

Set up Azure Database for MySQL

To create an instance of Azure Database for MySQL, perform the following steps:

1. Log into the Azure portal, and then select Create a resource.
2. Search for Azure Database for MySQL, select Flexible Server (recommended for better cost management), and then select Create.
3. Specify the following details for the server: 
   • Server name: restaurant-mgmt-db (must be unique)
   • Region: Select the closest to your location
   • Version: MySQL 8.0
   • Authentication method: MySQL authentication
   • Admin username: restaurantadmin
   • Password: Create a strong password (save it securely!)
4. Configure the networking details for the server:
   • Select "Public access"
   • Add your IP address to firewall rules
   • Select the Allow Azure services to access server checkbox.

Design the database schema

Next, let’s create the database schema.

1. Use MySQL Workbench or Azure Data Studio to connect to your server.
2. To create the database, run the following command:

CREATE DATABASE restaurant_management;
USE restaurant_management;

    3. To create tables for menu categories, menu items, customers, orders, and order items, run the following commands:

CREATE TABLE menu_categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(50) NOT NULL,
    description TEXT
);

CREATE TABLE menu_items (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT,
    item_name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    is_available BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (category_id) REFERENCES menu_categories(category_id)
);

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(15),
    join_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10,2),
    status ENUM('pending', 'preparing', 'ready', 'delivered'),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
    order_id INT,
    item_id INT,
    quantity INT NOT NULL,
    item_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (item_id) REFERENCES menu_items(item_id)
);

Implement basic features using stored procedures

Let's implement some basic features! First, we’ll add sample data to our tables. In the MySQL Workbench prompt, run the following commands to add sample categories and menu items:

INSERT INTO menu_categories (category_name, description)
VALUES 
('Appetizers', 'Start your meal right'),
('Main Course', 'Our signature dishes'),
('Desserts', 'Sweet endings'),
('Beverages', 'Refreshing drinks');

INSERT INTO menu_items (category_id, item_name, description, price)
VALUES 
(1, 'Crispy Calamari', 'Fresh squid rings with marinara sauce', 12.99),
(2, 'Grilled Salmon', 'Atlantic salmon with herbs and lemon', 24.99),
(3, 'Chocolate Lava Cake', 'Warm chocolate cake with molten center', 8.99),
(4, 'Fresh Mint Lemonade', 'House-made lemonade with fresh mint', 4.99);

 

Now, let's create some useful stored procedures for common operations:

DELIMITER //

-- Procedure to add new order
CREATE PROCEDURE create_new_order(
    IN p_customer_id INT,
    IN p_status VARCHAR(20)
)
BEGIN
    INSERT INTO orders (customer_id, status)
    VALUES (p_customer_id, p_status);
    SELECT LAST_INSERT_ID() as order_id;
END //

-- Procedure to get daily sales report
CREATE PROCEDURE get_daily_sales(IN p_date DATE)
BEGIN
    SELECT 
        DATE(order_date) as sale_date,
        COUNT(order_id) as total_orders,
        SUM(total_amount) as daily_revenue
    FROM orders
    WHERE DATE(order_date) = p_date
    GROUP BY DATE(order_date);
END //
DELIMITER ;

Build and run your application connecting to the database

In the GitHub repository is the sample code for the restaurant management application built with React and Node.js, integrating with the database created on Azure Database for MySQL.

1. Clone the repository and install required dependencies using "npm install" command.
2. Create a configuration (.env) file in the backend directory: 

   AZURE_MYSQL_HOST=your-azure-mysql-server.mysql.database.azure.com
   AZURE_MYSQL_USER=your-username
   AZURE_MYSQL_PASSWORD=your-password
   AZURE_MYSQL_DATABASE=your-database-name

3. Start the backend and frontend servers: 

   cd backend
   npm run dev
   cd frontend
   npm run dev

4. Access the application at http://localhost:3000 !

Tips and tricks for performance, security and resiliency

1 - Set up monitoring

1. Enable Azure Monitoring for Azure Database for MySQL:
   • Go to your Azure Database for MySQL server in Azure Portal
   • Under the "Monitoring" drop down, select the "Metrics" tab.
   • Enable the required Azure Monitor metrics.
2. Create Alert Rules for the following metrics, by navigating to "Alerts" tab under "Monitoring".
   • High CPU usage (>80%)
   • Storage space running low (<20%)
   • Failed connections

Learn more: Azure DB for MySQL monitoring | Setting up alerts for Azure DB for MySQL

2 - Implement security best practices

1. Create a Read-Only User

CREATE USER 'report_user'@'%' 
IDENTIFIED BY 'SecurePassword123!';

GRANT SELECT ON restaurant_management.* 
TO 'report_user'@'%';

      2. Enable SSL Encryption. In the Azure Portal, go to your Azure DB for MySQL server

• • Go to "Server parameters" blade
  • Set require_secure_transport to ON

Learn more: Azure Database for MySQL security features

3 - Optimize the performance

1. Create Appropriate Indexes

CREATE INDEX idx_order_date 
ON orders(order_date);

-- Index for customer searches
CREATE INDEX idx_customer_email 
ON customers(email);

     2. Configure Server Parameters. In the Azure portal, go to your Azure DB for MySQL server

• • Go to "Server parameters" blade
  • Adjust innodb_buffer_pool_size based on your data size
  • Set appropriate max_connections value

4- Configure automated backups and test recovery

Backup: In the Azure Portal, go to your Azure DB for MySQL server

1. Go to "Backup" blade
2. Set retention period (7-35 days)
3. Enable geo-redundant backup storage

Test Restore:

1. Create a test restore point
2. Perform a point-in-time restore
3. Verify data integrity

Learn more: Backup and restore in Azure Database for MySQL

5 - Optimize costs

1. Choose the Right Tier
• Start with the basic "Burstable" tier for development
• Monitor usage patterns
• Scale up/down as needed
2. Enable Auto-shutdown
• For non-production environments, schedule shutdown (stop the server) during non-business hours

Learn more about cost optimization for Azure Database for MySQL

Troubleshoot common issues

Some common issues you may face and how to troubleshoot them:

1. Connection Issues
   • Verify firewall rules
   • Check SSL requirements
   • Validate connection string
2. Performance problems
   • Review query execution plans
   • Monitor slow query logs
   • Check resource utilization

Learn more about troubleshooting Azure Database for MySQL

Next Steps and App Improvements

1. Add additional features to your application
• Implement table reservation system
• Add inventory management
• Create customer loyalty program
2. Integration Ideas
• Connect with a Point of Sale (POS) system
• Implement online ordering
• Create mobile app integration

Conclusion

In this tutorial, we've built a complete Restaurant Management System using Azure Database for MySQL. We've covered essential aspects from initial setup to production deployment, including database design, security implementation, and performance optimization. The system provides a scalable, secure foundation that can grow with your business needs.

To enhance this system, consider adding table reservations, inventory tracking, or integrating with a POS system. Remember to regularly monitor performance and keep security measures up to date.

Whether you're managing a single restaurant or planning to scale, you now have the knowledge to build and maintain a robust cloud-based database solution.

Happy coding!