Database Design II: A Sports Club Booking and Automation System

10 min readDec 11, 2023

A properly designed database is the foundation for efficient and effective data management. It is a critical asset for any organization that relies on data to drive its operations.

This project is going into details how I designed a Booking System Database for a Sports complex and how I automate activities in the database

In case you missed it, check out my last Database Design project

Project Overview

Case Study

Building a Sports Complex Booking and Automation System

Objective

Develop a comprehensive database system to manage the booking process of a sports complex, including facilities such as tennis courts, badminton courts, multi-purpose fields, and an archery range. Additionally, automate booking processes, including payments, updating member records, and providing a seamless experience for registered users.

Key Features

The sports complex has the following facilities: 2 tennis courts, 2 badminton courts, 2 multi-purpose fields and 1 archery range.
Each facility can be booked for a duration of one hour.
Only registered users are authorized to make bookings.
Only members who have made full payments are allowed to terminate their membership
No two members can book the same facility on the same date and time.
Users can cancel their bookings. Cancellations are allowed up to the day prior to the booked date.

Project Breakdown

After thoroughly reviewing the Project overview and understanding what needs to be done, I broke down the project into the following stages

Database Setup and Creation
Table Creation, Altering Table Constraints and Inserting Data
Creating Views and Setting up EER Diagram
Automate Database Activity
Query Optimization
User Management and Privileges
Backup and Recovery
Database Security

NB: This booking system will be built on MySQL Database

Database Setup and Creation

Before setting up a database, here are a list of steps I take

Determined the purpose of the database
Gathered all of the types of information I wanted to record in the database, such as members and facilities, bookings etc.
Divided the information items into major entities or subjects, such as Membership or Bookings. Each subject then becomes a table.
Decided what information I wanted to store in each table. Each item becomes a field, and is displayed as a column in the table.
Chose each table’s primary key. The primary key is a column that is used to uniquely identify each row.
Looked at each table and decided how the data in one table is related to the data in other tables. Added fields to tables or create new tables to clarify the relationships, as necessary.

After coming up with a blueprint for my Database System (the blueprint is included later in the article), I proceeded to Creating my Database.

-- DROP DATABASE IF EXISTS
DROP DATABASE IF EXISTS Sports_DB;

-- CREATE A DATABASE FOR SPORT COMPLEX
CREATE DATABASE Sports_DB 
DEFAULT CHARACTER SET utf8mb4;

Table Creation, Altering Table Constraints and Inserting Data

After coming up with a blueprint for the database, the next step was to create the tables, add table constraints and establish a relationship between the tables.

The tables to be used in the database were divided into the following

Membership: The Membership table contains details about the members registered in the sport complex.
It contains information such as the unique ID of the members, their password, email and other columns.
Pending_Terminations: The pending_terminations table contains Records from the members table that will be transferred here under certain circumstances.
Facilities: This table contains information regarding the facilities at the sport complex. Such facilities include tennis courts, badminton courts etc.
Bookings: This table contains information on the booking for the members. It helps track details on each members bookings
Facilitator: This table contains information on the facilitators in charge of organizing and maintaining the facilities
Payments: This table contains all payments from members or ex-members who have pending payments. This table stores information on all transactions.

The script below contains the SQL scripts used to build the membership and bookings table only. The full scripts can be found in my GitHub Repository.

USE Sports_DB;

-- TO CREATE THE MEMBERSHIP TABLE

DROP TABLE IF EXISTS membership;

CREATE TABLE membership (
 member_id VARCHAR(255),
    password VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    member_since DATETIME NOT NULL DEFAULT NOW(),
    payment_due DECIMAL(5,2) NOT NULL DEFAULT 0,
    PRIMARY KEY (member_id)
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- TO CREATE THE TABLE FOR BOOKINGS

DROP TABLE IF EXISTS bookings;

CREATE TABLE bookings (
 booking_id INT UNSIGNED AUTO_INCREMENT UNIQUE,
    facility_id VARCHAR(255) NOT NULL,
    booked_date DATE NOT NULL,
    booked_time TIME NOT NULL,
    member_id VARCHAR(255),
    datetime_of_booking DATETIME NOT NULL DEFAULT NOW(),
    payment_status VARCHAR(255) NOT NULL DEFAULT "Unpaid",
    PRIMARY KEY (booking_id)
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The next step I took was to set the table constraints. Some of the rules I set for the constraints are:

When the record in the memberships table is updated or deleted, the record in the booking table will also be updated or deleted accordingly
When the record in the facilities table is updated or deleted, the record in the bookings table will also be updated or deleted accordingly
Members should not be able to book the same facility at the same time(Hour) on the same day

I was able to solve the first two rules using Foreign keys and Referential Constraints. To implement the third rule, the facility_id , booked_date and booked_time must be unique.

The scripts are below

-- Altering the bookings table to add foreign key
ALTER TABLE bookings 
  ADD CONSTRAINT facility_fk 
  FOREIGN KEY (facility_id) 
  REFERENCES facilities(facility_id) 
  ON DELETE CASCADE
  ON UPDATE CASCADE;

-- Altering the facilities table to add foreign key
ALTER TABLE facilities 
  ADD CONSTRAINT facilitator_fk 
  FOREIGN KEY (facilitator_id) 
  REFERENCES facilitator(facilitator_id) 
  ON DELETE SET NULL
  ON UPDATE CASCADE;

-- Altering the bookings table to add Unique key

ALTER TABLE bookings
ADD UNIQUE INDEX `unique_booking` (facility_id, booked_date, booked_time);

Data Hashing and Encryption

Hashing is a one-way cryptographic function that transforms the original password into a fixed-length string of characters, making it computationally infeasible to reverse the process and obtain the original password.

Creating View & ER Diagram

The next step I took was to create an Entity Relationship Diagram for the purpose of visually representing the structure of a database, including its entities (tables), relationships between entities, and the attributes associated with each entity.

The ER diagram for the booking system is seen below

The diagram above represents the 6 tables in the booking system, 4 tables are connected because they have relationship with themselves but 2 other tables payments and pending_terminations do not have a direct relationship with other tables but they have an Indirect or Event-driven relationship.

Let me explain

Once a member in the membership table terminates his contract but he is still owing some money ( payment_due > 0), his details will be moved to the pending_terminations table.

Once a member makes a payment, it will be updated in the payment_due column in the membership table or if an ex-member, then it will be updated in the pending_terminations table.

The tables are not directly connected but activities in one table triggers actions in another table.

Creating Views

Now that I have created the tables and inserted some data, next step was to create views.

Specifically, I created views that shows all the booking details of each booking and all booking details in the current week. This would give the management and admin an easy view to all the historical booking details and booking details in the current week so that proper preparations can be made

You can find the script in my GitHub Repository.

Automating Database Activity

The goal of automating database activity is to streamline, optimize, and schedule routine or repetitive tasks, improving efficiency, accuracy, and overall management of the database environment.

To begin this, I initially studied all repetitive booking and payment activities that would be done on the database and automated them to save time and improve efficiency. These automations were done using Stored Procedures and Database Triggers in MySQL.

Here are the list of activities I automated;

inserting new members into the membership table
Deleting members from the membership table
Updating data in the members and Facilitators credentials on the membership and facilitator table. Credentials such as name, email, password etc.
Making a booking and inserting it on the bookings table (you must be a confirmed member and you have to pick an facility at a time it is not booked)
Checking for available slots before making a booking
Canceling a booking based on the condition that you have not paid for the booking and the booking cannot be cancelled on the day of the booking
Moving records with cancelled memberships to the pending_terminations table when they still owe the sports complex
Updating payment_status and payment_due for members after payments have been made to the payments table

These are most of the automations I implemented on the database and the scripts used to create the automation can be found in my GitHub Repository.

Query Optimization

To improve query performance, Indexes will be set up so as to improve the performance of database when querying the database.

The Query Optimization techniques used for the database was Indexes and below are the scripts used to create the indexes on selected columns

-- To create an index on the listed columns
CREATE INDEX facility_type_idx ON Facilities (Facility_id);
CREATE INDEX booking_period_idx ON bookings (booked_date, booked_time);
CREATE INDEX facilitator_name_idx ON Facilitator (full_name);
CREATE INDEX email_idx ON membership (email);
CREATE INDEX payment_member_idx ON Payments (member_id);
CREATE INDEX pending_member_idx ON Pending_terminations (member_id);

-- To show all indexex in the database
SELECT DISTINCT TABLE_NAME,
  INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'sports_db';

Note that the choice of which columns to index should be based on a thorough understanding of your database and you can start from columns used for JOIN and WHERE operations in your analysis.

The choice of columns to index is indeed flexible and can evolve over time based on the usage patterns of your database.

User Management & Privileges

User management is important because they play a vital role in ensuring the security, integrity, and efficiency of a database system. They enforce access control, limiting database interactions to authorized individuals and preventing unauthorized access.

For this database, I would be creating 3 users, the details are below

Admin: The user should be able to perform all actions on the database from creating, querying, changing and deleting instructors,
members, bookings, facilities etc.
Facilitator: The facilitator should be able to check and query the facilitator and facilities tables and should be able to
update their personal details on the facilitator table. They should also be able to view bookings details and
bookings for the week
Member: Members should be able to check and update their details on the membership table. Members should be able to view available
bookings, should be able to make bookings and can view their own weekly bookings.

The script used to carry out this process is documented in my repository.

Backup and Recovery

We use Backups to make sure our database is protected and recoverable in the event of loss. There are different types of backup but the backup I performed is a Logical backup.

In a logical backup, you are able to store the SQL statements needed to recreate the database and populate it. In MySQL, this is done using mysqldump.

Another way of backing up your database is by Database Replication but this project does not cover database replication.

The backed up file is also present in my GitHub Repository. So you can recreate the database on your local device.

Database Security

Securing a database for a sports complex is essential to protect sensitive information, ensure data integrity, and maintain the overall reliability of the system.

There are several security threats that database are prone to. To understand more about the security threats that face database, check here

Here are some security best practices the team can adopt

If changes are made in the database, try not to store sensitive information, and if you have to, encrypt it
Ensure you limit access to the data. Very few employee will need access to the data and for the ones that have access, really consider who you are giving write/edit access to. Have an active plan for removing facilitators and member access when they resign or quit.
Take Authentication seriously. Ensure that members are encouraged to use strong passwords rather than a weak one. Also ensure that they change their passwords regularly, at least once every 30 days.
Consistently backup your database and make sure it is up-to-date just incase of a breach or malfunction. The data and database wont be lost.
Educate database administrators, developers, and other personnel about security best practices. Regular training sessions help create a security-aware culture and reduce the likelihood of human errors. Database Security is everyone’s priority.

Full Project Documentation and Scripts here

Thank you for reading till the end. I would love your thoughts on the Database, Do you think more features could be added to improve the performance of the database? I would love to hear it