For an instructor lead, in-depth look at learning SQL click below.
Whether you’re setting up a local movie theatre’s online booking platform, or digging deep into the domain of flight bookings or even concert tickets, an efficient ticket booking system is at the heart of such businesses. A well-structured ticket booking system provides seamless user experience and cuts down on operational challenges. Today, we’ll learn how to build a ticket booking system using SQL, the language of databases.
Designing the Database
First, we need to set up our database for the ticket booking system. We’ll require tables for Customers (Customer_Table), Events (Event_Table), Tickets (Ticket_Table), and Bookings (Booking_Table).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
CREATE TABLE Customer_Table( Customer_ID INT PRIMARY KEY, Customer_Name VARCHAR(100), Email VARCHAR(100) ); CREATE TABLE Event_Table( Event_ID INT PRIMARY KEY, Event_Name VARCHAR(100), Event_Date DATE ); CREATE TABLE Ticket_Table( Ticket_ID INT PRIMARY KEY, Event_ID INT, FOREIGN KEY (Event_ID) REFERENCES Event_Table(Event_ID) ); CREATE TABLE Booking_Table( Booking_ID INT PRIMARY KEY, Customer_ID INT, Ticket_ID INT, FOREIGN KEY (Customer_ID) REFERENCES Customer_Table(Customer_ID), FOREIGN KEY (Ticket_ID) REFERENCES Ticket_Table(Ticket_ID) ); |
Booking a Ticket
Now that our tables are set, let’s consider a use case where a customer wants to book a ticket for an event. This would require an INSERT command to add a row to the Booking_Table.
|
1 2 3 4 |
INSERT INTO Booking_Table(Customer_ID, Ticket_ID) VALUES(1, 100); |
Checking Available Tickets for an Event
Next, let’s see how we can check the available tickets for a specific event. Here’s how to do it:
|
1 2 3 4 5 |
SELECT COUNT(Ticket_ID) AS 'Available Tickets' FROM Ticket_Table WHERE Event_ID = 200 AND Ticket_ID NOT IN (SELECT Ticket_ID FROM Booking_Table WHERE Event_ID = 200); |
Canceling a Booking
In case a customer wants to cancel a booking, we would need to DELETE a row from the Booking_Table. Here’s how to do it:
|
1 2 3 |
DELETE FROM Booking_Table WHERE Booking_ID = 50; |
Conclusion
In this blog post, we walked you through how to build a basic ticket booking system using SQL. We used SQL statements like CREATE TABLE, INSERT, SELECT and DELETE to perform different operations. As your requirements become more complex, you can add more tables, views, or even stored procedures to simplify tasks and enhance the functionality of your ticket booking system.