Skip to content

Akhilesh4506/Turf_Booking_System

BranchesTags

Repository files navigation

Turf Booking System

A dockerized backend service built with Spring Boot and MySQL to manage sports venues, time slots, and bookings with availability checks and conflict prevention. This simulates a real-world sports ground/turf booking system.

Tech Stack

  • Java 17 with Spring Boot 3.2.0
  • MySQL 8.0
  • Docker & Docker Compose
  • REST APIs (No UI)

Features

  1. Venue Management: Add, list, view, and delete sports venues
  2. Slot Management: Add time slots per venue with overlap prevention
  3. Availability API: Fetch available venues for a given time range & sport
  4. Booking Management: Book, view, and cancel slots safely with double-booking prevention

Assumptions

The following assumptions are made in this system:

  1. Booking Model: One booking corresponds to exactly one slot (1 booking = 1 slot)
  2. Slot Immutability: Once a slot is booked, its time cannot be modified
  3. Cancellation Policy: Cancelled bookings immediately free the slot for re-booking
  4. Database: Single MySQL instance is used, no external caching layer
  5. Sports Validation: Sports must be validated against the external API (https://stapubox.com/sportslist/)
  6. Concurrency: Pessimistic locking is used to prevent double bookings
  7. Time Validation: Start time must be before end time for all slots

Database Schema

The system uses three main entities:

Venues Table

  • id (Primary Key)
  • name (VARCHAR 200, NOT NULL)
  • address (VARCHAR 500, NOT NULL)
  • sport_code (VARCHAR 50, NOT NULL, Indexed)
  • sport_id (INTEGER)
  • created_at (TIMESTAMP)

Slots Table

  • id (Primary Key)
  • venue_id (Foreign Key to Venues, Indexed)
  • slot_date (DATE, NOT NULL, Indexed)
  • start_time (TIME, NOT NULL, Indexed)
  • end_time (TIME, NOT NULL, Indexed)
  • is_available (BOOLEAN, NOT NULL, Indexed)
  • price (DOUBLE, NOT NULL)
  • created_at (TIMESTAMP)

Bookings Table

  • id (Primary Key)
  • slot_id (Foreign Key to Slots, UNIQUE, Indexed)
  • customer_name (VARCHAR 200, NOT NULL)
  • customer_email (VARCHAR 200, NOT NULL, Indexed)
  • customer_phone (VARCHAR 20)
  • status (ENUM: CONFIRMED, CANCELLED, Indexed)
  • created_at (TIMESTAMP)
  • updated_at (TIMESTAMP)

API Documentation

Base URL

http://localhost:8080

1. Create Venue

POST /venues

Creates a new sports venue.

Request Body:

{
  "name": "Football Ground A",
  "address": "123 Sports Street, City",
  "sportCode": "FOOTBALL",
  "sportId": 1
}

Response: 201 Created

{
  "id": 1,
  "name": "Football Ground A",
  "address": "123 Sports Street, City",
  "sportCode": "FOOTBALL",
  "sportId": 1,
  "createdAt": "2024-01-15T10:00:00"
}

cURL Example:

curl -X POST http://localhost:8080/venues \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Football Ground A",
    "address": "123 Sports Street, City",
    "sportCode": "FOOTBALL",
    "sportId": 1
  }'

2. Get All Venues

GET /venues

Retrieves all venues.

Response: 200 OK

[
  {
    "id": 1,
    "name": "Football Ground A",
    "address": "123 Sports Street, City",
    "sportCode": "FOOTBALL",
    "sportId": 1,
    "createdAt": "2024-01-15T10:00:00"
  }
]

cURL Example:

curl -X GET http://localhost:8080/venues

3. Get Venue by ID

GET /venues/{id}

Retrieves a specific venue by ID.

Response: 200 OK

{
  "id": 1,
  "name": "Football Ground A",
  "address": "123 Sports Street, City",
  "sportCode": "FOOTBALL",
  "sportId": 1,
  "createdAt": "2024-01-15T10:00:00"
}

cURL Example:

curl -X GET http://localhost:8080/venues/1

4. Delete Venue

DELETE /venues/{id}

Deletes a venue. Cannot delete venues with active bookings.

Response: 204 No Content

cURL Example:

curl -X DELETE http://localhost:8080/venues/1

5. Create Slot

POST /venues/{venueId}/slots

Creates a time slot for a venue. Prevents overlapping slots.

Request Body:

{
  "slotDate": "2024-01-20",
  "startTime": "10:00:00",
  "endTime": "11:00:00",
  "price": 500.0
}

Response: 201 Created

{
  "id": 1,
  "venueId": 1,
  "slotDate": "2024-01-20",
  "startTime": "10:00:00",
  "endTime": "11:00:00",
  "isAvailable": true,
  "price": 500.0,
  "createdAt": "2024-01-15T10:00:00"
}

cURL Example:

curl -X POST http://localhost:8080/venues/1/slots \
  -H "Content-Type: application/json" \
  -d '{
    "slotDate": "2024-01-20",
    "startTime": "10:00:00",
    "endTime": "11:00:00",
    "price": 500.0
  }'

6. Get Available Venues

GET /venues/available

Fetches available venues and slots for a given time range. Optionally filters by sport code.

Query Parameters:

  • startDate (required): Start date (YYYY-MM-DD)
  • startTime (required): Start time (HH:mm:ss)
  • endDate (required): End date (YYYY-MM-DD)
  • endTime (required): End time (HH:mm:ss)
  • sportCode (optional): Filter by sport code

Response: 200 OK

{
  "venues": [
    {
      "venueId": 1,
      "venueName": "Football Ground A",
      "address": "123 Sports Street, City",
      "sportCode": "FOOTBALL",
      "sportId": 1,
      "availableSlots": [
        {
          "slotId": 1,
          "slotDate": "2024-01-20",
          "startTime": "10:00:00",
          "endTime": "11:00:00",
          "price": 500.0
        }
      ]
    }
  ]
}

cURL Example:

curl -X GET "http://localhost:8080/venues/available?startDate=2024-01-20&startTime=09:00:00&endDate=2024-01-20&endTime=12:00:00&sportCode=FOOTBALL"

7. Create Booking

POST /bookings

Creates a booking for a slot. Prevents double booking using pessimistic locking.

Request Body:

{
  "slotId": 1,
  "customerName": "John Doe",
  "customerEmail": "john.doe@example.com",
  "customerPhone": "+1234567890"
}

Response: 201 Created

{
  "id": 1,
  "slotId": 1,
  "venueId": 1,
  "venueName": "Football Ground A",
  "slotDate": "2024-01-20",
  "startTime": "10:00:00",
  "endTime": "11:00:00",
  "customerName": "John Doe",
  "customerEmail": "john.doe@example.com",
  "customerPhone": "+1234567890",
  "status": "CONFIRMED",
  "price": 500.0,
  "createdAt": "2024-01-15T10:00:00"
}

cURL Example:

curl -X POST http://localhost:8080/bookings \
  -H "Content-Type: application/json" \
  -d '{
    "slotId": 1,
    "customerName": "John Doe",
    "customerEmail": "john.doe@example.com",
    "customerPhone": "+1234567890"
  }'

8. Get Booking by ID

GET /bookings/{id}

Retrieves a specific booking by ID.

Response: 200 OK

{
  "id": 1,
  "slotId": 1,
  "venueId": 1,
  "venueName": "Football Ground A",
  "slotDate": "2024-01-20",
  "startTime": "10:00:00",
  "endTime": "11:00:00",
  "customerName": "John Doe",
  "customerEmail": "john.doe@example.com",
  "customerPhone": "+1234567890",
  "status": "CONFIRMED",
  "price": 500.0,
  "createdAt": "2024-01-15T10:00:00"
}

cURL Example:

curl -X GET http://localhost:8080/bookings/1

9. Cancel Booking

PUT /bookings/{id}/cancel

Cancels a booking and immediately frees the slot.

Response: 200 OK

{
  "id": 1,
  "slotId": 1,
  "venueId": 1,
  "venueName": "Football Ground A",
  "slotDate": "2024-01-20",
  "startTime": "10:00:00",
  "endTime": "11:00:00",
  "customerName": "John Doe",
  "customerEmail": "john.doe@example.com",
  "customerPhone": "+1234567890",
  "status": "CANCELLED",
  "price": 500.0,
  "createdAt": "2024-01-15T10:00:00"
}

cURL Example:

curl -X PUT http://localhost:8080/bookings/1/cancel

10. Get Bookings by Customer Email

GET /bookings/customer/{email}

Retrieves all bookings for a customer.

Response: 200 OK

[
  {
    "id": 1,
    "slotId": 1,
    "venueId": 1,
    "venueName": "Football Ground A",
    "slotDate": "2024-01-20",
    "startTime": "10:00:00",
    "endTime": "11:00:00",
    "customerName": "John Doe",
    "customerEmail": "john.doe@example.com",
    "customerPhone": "+1234567890",
    "status": "CONFIRMED",
    "price": 500.0,
    "createdAt": "2024-01-15T10:00:00"
  }
]

cURL Example:

curl -X GET http://localhost:8080/bookings/customer/john.doe@example.com

Error Responses

All error responses follow this format:

{
  "error": "Error message description"
}

Common HTTP Status Codes:

  • 400 Bad Request: Validation errors or invalid input
  • 404 Not Found: Resource not found
  • 500 Internal Server Error: Unexpected server errors

Getting Started

Prerequisites

  • Docker and Docker Compose installed
  • Java 17 (for local development, optional)

Running the Application

  1. Clone or navigate to the project directory:

    cd /path/to/project

  2. Start the services using Docker Compose:

    docker-compose up --build

    This will:

    • Build the Spring Boot application
    • Start MySQL database
    • Start the application on port 8080

  3. Verify the application is running:

    curl http://localhost:8080/venues

  4. Stop the services:

    docker-compose down

Database Connection

  • Host: localhost (from host machine) or mysql (from within Docker network)
  • Port: 3306
  • Database: sports_booking
  • Username: root
  • Password: rootpassword

Sports API Integration

The system integrates with the external sports API at https://stapubox.com/sportslist/ to validate sport codes and IDs. When creating a venue, the system validates that the provided sportCode and sportId exist in the external API.

Conflict Prevention

The system implements several mechanisms to prevent conflicts:

  1. Slot Overlap Prevention: When creating a slot, the system checks for overlapping time slots on the same date for the same venue.

  2. Double Booking Prevention: Uses pessimistic locking (PESSIMISTIC_WRITE) when booking a slot to prevent concurrent bookings of the same slot.

  3. Slot Immutability: Once a slot is booked, its time cannot be modified (enforced at the application level).

  4. Immediate Slot Release: When a booking is cancelled, the slot is immediately marked as available.

Database Indexes

The following indexes are created for optimal query performance:

  • venues.sport_code: For filtering venues by sport
  • slots.venue_id: For finding slots by venue
  • slots.slot_date, start_time, end_time: Composite index for date/time range queries
  • slots.is_available: For filtering available slots
  • bookings.slot_id: For finding booking by slot (unique constraint)
  • bookings.status: For filtering bookings by status
  • bookings.customer_email: For finding bookings by customer

Testing the APIs

You can use the provided cURL examples above or import the following Postman collection format:

Postman Collection (JSON)

{
  "info": {
    "name": "Sports Venue Booking API",
    "schema": "https://schema.getpostman.com/json/collection/v2.1.0/collection.json"
  },
  "item": [
    {
      "name": "Create Venue",
      "request": {
        "method": "POST",
        "header": [{"key": "Content-Type", "value": "application/json"}],
        "body": {
          "mode": "raw",
          "raw": "{\n  \"name\": \"Football Ground A\",\n  \"address\": \"123 Sports Street\",\n  \"sportCode\": \"FOOTBALL\",\n  \"sportId\": 1\n}"
        },
        "url": "http://localhost:8080/venues"
      }
    },
    {
      "name": "Get All Venues",
      "request": {
        "method": "GET",
        "url": "http://localhost:8080/venues"
      }
    },
    {
      "name": "Create Slot",
      "request": {
        "method": "POST",
        "header": [{"key": "Content-Type", "value": "application/json"}],
        "body": {
          "mode": "raw",
          "raw": "{\n  \"slotDate\": \"2024-01-20\",\n  \"startTime\": \"10:00:00\",\n  \"endTime\": \"11:00:00\",\n  \"price\": 500.0\n}"
        },
        "url": "http://localhost:8080/venues/1/slots"
      }
    },
    {
      "name": "Get Available Venues",
      "request": {
        "method": "GET",
        "url": {
          "raw": "http://localhost:8080/venues/available?startDate=2024-01-20&startTime=09:00:00&endDate=2024-01-20&endTime=12:00:00",
          "query": [
            {"key": "startDate", "value": "2024-01-20"},
            {"key": "startTime", "value": "09:00:00"},
            {"key": "endDate", "value": "2024-01-20"},
            {"key": "endTime", "value": "12:00:00"}
          ]
        }
      }
    },
    {
      "name": "Create Booking",
      "request": {
        "method": "POST",
        "header": [{"key": "Content-Type", "value": "application/json"}],
        "body": {
          "mode": "raw",
          "raw": "{\n  \"slotId\": 1,\n  \"customerName\": \"John Doe\",\n  \"customerEmail\": \"john.doe@example.com\",\n  \"customerPhone\": \"+1234567890\"\n}"
        },
        "url": "http://localhost:8080/bookings"
      }
    },
    {
      "name": "Cancel Booking",
      "request": {
        "method": "PUT",
        "url": "http://localhost:8080/bookings/1/cancel"
      }
    }
  ]
}

Project Structure

.
├── src/
│   └── main/
│       ├── java/com/sportsbooking/
│       │   ├── controller/      # REST Controllers
│       │   ├── service/         # Business Logic
│       │   ├── repository/      # Data Access Layer
│       │   ├── entity/          # JPA Entities
│       │   ├── dto/             # Data Transfer Objects
│       │   ├── exception/       # Exception Handlers
│       │   └── config/          # Configuration
│       └── resources/
│           └── application.yml  # Application Configuration
├── Dockerfile                   # Docker image definition
├── docker-compose.yml           # Docker Compose configuration
├── pom.xml                      # Maven dependencies
└── README.md                    # This file

License

This project is created for demonstration purposes.

About

No description, website, or topics provided.

Resources

Readme
Activity

Stars

0 stars

Watchers

0 watching

Forks

0 forks
Report repository

Releases

No releases published

Packages

 
 
 

Contributors

Languages