Database-Design-And-SQL-Queries Football-Ticket-Booking Database

Postgres Database Design and SQL queries for Football Ticket Booking System

This Repository Includes 3 Tables, ERD Diagram , Query.sql file and 3 Theory Video Topics. Tables are:

1. Users
2. Matches
3. Bookings

Database Tables

Users

Stores information about all registered users, including football fans and ticket managers.

Field Name	Description
user_id	Unique identifier for each user.
full_name	User's full name.
email	Unique email address.
role	User role (Football Fan or Ticket Manager)
phone_number	User's contact number.

---

Matches

Stores details about football matches available for ticket booking.

Field Name	Description
match_id	Unique identifier for each match.
fixture	Match fixture (like:Real Madrid vs Barcelona).
tournament_category	Competition or league name.
base_ticket_price	Standard ticket price.
match_status	Match availability status (Available, Selling Fast, Sold Out, Postponed)

---

Bookings

Stores ticket booking records made by users.

Field Name	Description
booking_id	Unique identifier for each booking.
user_id	References the user who made the booking.
match_id	References the booked match.
seat_number	Assigned seat number.
payment_status	Payment status (Pending, Confirmed, Cancelled, Refunded)
total_cost	Total booking amount.

---

Entity Relationships

• One User can create multiple Bookings.
• One Match can have multiple Bookings.
• Each Booking belongs to one User and one Match.

Users (1) ────< Bookings >──── (1) Matches

---

ER Diagram

SQL Query and SubQuery

Includes Joining Multiple Tables, filtering, Sorting, Pagination , Function

This Football-Ticket-Booking system demonstrates various SQL concepts used to retrieve, filter, and analyze data efficiently from postgres Database (PgAdmin 4 , BeeKeeper Studio).

Joining Multiple Tables

Combines data from two or more related tables using JOIN operations such as INNER JOIN and LEFT JOIN.

Example: Retrieving booking details along with user names and match information.

---

Filtering Data

Uses the WHERE clause to return only records that meet specific conditions.

Example: Finding all available Champions League matches.

---

Sorting Data

Uses the ORDER BY clause to arrange query results in ascending (ASC) or descending (DESC) order.

Example: Displaying matches based on ticket price from highest to lowest.

---

Pagination

Uses LIMIT and OFFSET to retrieve a specific subset of records, which is useful for handling large datasets.

Example: Displaying two matches while skipping the most expensive one.

---

Aggregate Functions

Uses built-in SQL functions such as AVG(), COUNT(), SUM(), MIN(), and MAX() to perform calculations on data.

Example: Calculating the average booking cost.

---

Subqueries

A query nested inside another query to provide intermediate results for the main query.

Example: Finding bookings whose total cost is greater than the average booking cost.

Theory Video Include 3 Topics

1. If a Primary Key column guarantees that all row entries are completely unique, why does the database system also explicitly forbid it from containing a NULL value?

2. Imagine a newly registered fan who hasn't bought any match tickets yet. If you run a LEFT JOIN linking the Users table (left) to the Bookings table (right), what will the resulting rows look like for that specific fan?

3. What is the difference between a main query and a subquery? In what scenarios would you choose to use a subquery over a standard JOIN operation?

Conclusion

This system demonstrates a complete Football Ticket Booking System database design using SQL (postgres). It includes well-structured tables for Users, Matches, and Bookings, along with proper relationships using primary and foreign keys.

Overall, this database system provides a strong foundation in relational database design and SQL query handling, which is essential for building scalable backend systems in real-world applications.

Author

© MD MUSTAFIZUR RAHMAN (@rahmancoder). All rights reserved.