What are the best practices for designing tables and relationships in a MySQL database for a reservation system in PHP?

When designing tables and relationships in a MySQL database for a reservation system in PHP, it is important to properly structure the tables to efficiently store and retrieve reservation data. This can be achieved by creating separate tables for reservations, customers, and rooms, and establishing relationships between them using foreign keys. Additionally, consider implementing indexes on commonly queried columns to improve performance.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL
);

CREATE TABLE rooms (
    room_id INT PRIMARY KEY AUTO_INCREMENT,
    room_number INT NOT NULL,
    capacity INT NOT NULL
);

CREATE TABLE reservations (
    reservation_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    room_id INT,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (room_id) REFERENCES rooms(room_id)
);