Database Design: A Sports League Booking and Automation System
An end-to-end relational database that automates league creation, team signups, field rentals, payment processing, and cancellations for a community sports organisation.
Overview
Community sports leagues run on a tangle of spreadsheets, group chats, and manual processes — ripe for missed registrations, double-booked fields, and payment confusion. This project replaces that chaos with a purpose-built relational database system that automates the full league management lifecycle.
From initial league creation through to team signups, field rental scheduling, payment tracking, and cancellation handling — every process is modelled, automated, and secured through SQL stored procedures, triggers, and role-based user privileges.
System Capabilities
- League Management: Admins can create new leagues with defined start dates, sport types, maximum team counts, and fee structures — all stored in a normalised schema that prevents data duplication.
- Team Registration: Teams sign up through a stored procedure that validates available slots, checks payment status, and automatically updates league rosters — eliminating overbooking.
- Field Rental Scheduling: A booking system manages field availability across multiple venues and time slots, preventing double-booking through constraint-based conflict detection.
- Payment Processing: Payments are recorded against team and rental records. Triggers automatically update payment status and flag overdue accounts for admin follow-up.
- Cancellation Handling: Cancellations trigger a cascade of updates — freeing field slots, adjusting team counts, and recording refund eligibility based on cancellation timing rules.
Technical Design
Requirements Gathering
Business requirements were translated into entity relationships, identifying the core objects (League, Team, Player, Field, Booking, Payment) and the relationships between them.
Schema Design
A fully normalised relational schema was designed to third normal form (3NF), eliminating redundancy and ensuring data integrity across all tables.
Stored Procedures & Triggers
Complex business logic — registration validation, payment status updates, cancellation cascades — was encapsulated in stored procedures and triggers to keep application logic in the database layer.
User Privileges
Role-based access control was implemented with distinct privilege sets for admins, team managers, and read-only viewers — ensuring data security and appropriate access at each level.
Explore the Project
Browse the full schema, stored procedures, and documentation on GitHub.