Loading...
Let us say there are 10 Million DAU.
This service covers 1000 cities worldwide.
Each city has 4 theaters on average.
There are 50 Movies, 20 shows
There are 20 shows/day/theater
There are 150 seats/screen
Each theater plays on average 10 movies.
Each movie has 4 shows on average.
Overall, 100M movie tickets are sold monthly.
For each booking, I will estimate it would take 256B to store, considering:
256 * 100M = 25.6GB / month
In two years, it would require 614.4GB. Considering some user growth and open capacity, let's say we will need 1TB in the main DB.
Avg TPS: 10M DAU /86400 =115.74 TPS
Peak TPS: 3* times
summary:
User Information Data Storage: 75 GB
Theater, Movies, Shows, Screens, Seats Data Storage: 0.25 GB
Search Index Data Storage: 1 MB
Transaction Data Storage: 300 GB
E-Ticket and QR Code Data Storage: 500 GB
{ "date": "2024-09-01", "seats": ["A1", "A2", "B3"], "userId": "user123", "paymentInfo": { "method": "credit_card", "cardNumber": "4111111111111111", "expirationDate": "12/25", "cvv": "123" } }
Response: {
"bookingId": "booking789",
"theaterId": "123",
"showId": "456",
"date": "2024-09-01",
"seats": ["A1", "A2", "B3"],
"totalPrice": 37.50,
"currency": "USD",
"status": "confirmed",
"eTicket": {
"qrCode": "qrcode-string",
"downloadLink": "https://theaterService.com/tickets/booking789/download"
}
the key entities are:
sql
Copy code
CREATE TABLE Theaters (
theaterId INT PRIMARY KEY,
name VARCHAR(255),
address VARCHAR(255),
city VARCHAR(100),
state VARCHAR(50),
zipcode VARCHAR(10)
);
sql
Copy code
CREATE TABLE Movies (
movieId INT PRIMARY KEY,
title VARCHAR(255),
genre VARCHAR(100),
language VARCHAR(50),
releaseDate DATE
);
sql
Copy code
CREATE TABLE Actors (
actorId INT PRIMARY KEY,
name VARCHAR(255)
);
sql
Copy code
CREATE TABLE MovieActors (
movieId INT,
actorId INT,
FOREIGN KEY (movieId) REFERENCES Movies(movieId),
FOREIGN KEY (actorId) REFERENCES Actors(actorId),
PRIMARY KEY (movieId, actorId)
);
sql
Copy code
CREATE TABLE Shows (
showId INT PRIMARY KEY,
theaterId INT,
movieId INT,
showtime TIME,
date DATE,
FOREIGN KEY (theaterId) REFERENCES Theaters(theaterId),
FOREIGN KEY (movieId) REFERENCES Movies(movieId)
);
sql
Copy code
CREATE TABLE Seats (
seatId INT PRIMARY KEY,
showId INT,
row VARCHAR(10),
seatNumber VARCHAR(10),
status VARCHAR(50),
FOREIGN KEY (showId) REFERENCES Shows(showId)
);
sql
Copy code
CREATE TABLE Bookings (
bookingId INT PRIMARY KEY,
userId VARCHAR(50),
showId INT,
date DATE,
totalPrice DECIMAL(10, 2),
currency VARCHAR(10),
status VARCHAR(50),
FOREIGN KEY (showId) REFERENCES Shows(showId)
);
sql
Copy code
CREATE TABLE BookingSeats (
bookingId INT,
seatId INT,
FOREIGN KEY (bookingId) REFERENCES Bookings(bookingId),
FOREIGN KEY (seatId) REFERENCES Seats(seatId),
PRIMARY KEY (bookingId, seatId)
);
sql
Copy code
SELECT * FROM Theaters WHERE zipcode = '90210';
sql
Copy code
SELECT row, seatNumber, status
FROM Seats
WHERE showId = (
SELECT showId
FROM Shows
WHERE theaterId = {theaterId} AND movieId = {movieId} AND date = {date} AND showtime = {time}
) AND status = 'available';
sql
Copy code
SELECT m.title, m.genre, m.language
FROM Movies m
JOIN MovieActors ma ON m.movieId = ma.movieId
JOIN Actors a ON ma.actorId = a.actorId
WHERE a.name = 'Tom Hanks' AND m.genre = 'Drama' AND m.language = 'English';
sql
Copy code
BEGIN TRANSACTION;
INSERT INTO Bookings (userId, showId, date, totalPrice, currency, status)
VALUES ('user123', {showId}, '2024-09-01', 37.50, 'USD', 'confirmed');
INSERT INTO BookingSeats (bookingId, seatId)
VALUES (LAST_INSERT_ID(), (SELECT seatId FROM Seats WHERE showId = {showId} AND row = 'A' AND seatNumber = 'A1')),
(LAST_INSERT_ID(), (SELECT seatId FROM Seats WHERE showId = {showId} AND row = 'A' AND seatNumber = 'A2'));
UPDATE Seats
SET status = 'booked'
WHERE showId = {showId} AND (row = 'A' AND seatNumber = 'A1') OR (row = 'A' AND seatNumber = 'A2');
COMMIT;
If you opt for a NoSQL approach (e.g., MongoDB), the schema can be more flexible and denormalized, which is useful for hierarchical data. However, the key trade-off is that it might require more storage space and could be more complex to maintain consistency.
zipcode to allow efficient retrieval of theaters based on location.movieId to distribute movies across shards efficiently.theaterId to ensure that all related data for a theater's shows and seats are stored together.Shows and Seats tables by date to optimize queries for specific dates.Bookings and Seats tables to ensure consistency and prevent double booking. Use asynchronous replication for Movies and Theaters to balance performance and availability.zipcode.actors, genres, and languages.theaterId and synchronous replication for strong consistency.userId and synchronous replication for booking consistency.Let's dive deeper into two critical components of the system: Seat Management Service and Booking Service. These components are essential for handling concurrency, ensuring data consistency, and providing a smooth user experience during the seat selection and booking process.
The Seat Management Service is responsible for tracking seat availability, handling concurrent seat selections, and updating seat statuses during the booking process. This service ensures that seats cannot be double-booked, even under high traffic conditions.
theaterId and showId. Each seat has a unique identifier, row, seat number, and status.CREATE TABLE Seats (
seatId INT PRIMARY KEY,
showId INT,
theaterId INT,
row VARCHAR(10),
seatNumber VARCHAR(10),
status ENUM('available', 'reserved', 'booked'),
version INT DEFAULT 0,
FOREIGN KEY (showId) REFERENCES Shows(showId),
INDEX (theaterId, showId, row, seatNumber)
);
seat:{showId}:{row}:{seatNumber}.reserved.version column in the Seats table is used to implement optimistic locking.booked, the Seat Management Service checks the current version number. If the number has changed since the seat was reserved, the update is aborted, and the user must retry.theaterId and showId, allowing the database to handle a high volume of concurrent reads and writes without contention.The Booking Service is responsible for processing seat reservations, handling payments, and finalizing bookings. This service ensures that the booking process is atomic, meaning that either all steps are completed successfully or none are.
userId to distribute the load and allow efficient querying.CREATE TABLE Bookings (
bookingId INT PRIMARY KEY,
userId VARCHAR(50),
showId INT,
theaterId INT,
date DATE,
seats JSON,
totalPrice DECIMAL(10, 2),
currency VARCHAR(10),
status ENUM('pending', 'confirmed', 'failed'),
paymentStatus ENUM('pending', 'completed', 'failed'),
createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (showId) REFERENCES Shows(showId),
INDEX (userId)
);
reserved.Bookings table is updated to confirmed, and the seats are updated to booked in the Seats table.userId, allowing the system to distribute the load evenly across database shards.Notification service:
Asynchronous Task Handling:
Queue Implementation:
Payment Service:
Immediate Retry:
Fallback to Alternative Payment Gateway:
Transaction Rollback:
User Notification:
Audit Logging:
Technology Choice: Distributed Locking with Redis
Trade-offs:
Why Distributed Locking?
SQL was chosen for Bookings and Seats because of the need for strong consistency, transactional integrity, and complex relational queries. NoSQL was selected for Movies and Theaters to handle large-scale, flexible data storage where schema flexibility and horizontal scalability are more critical.
Handling Exhausted Retries:
The Seat Management Service is responsible for handling seat reservations and ensuring that no double bookings occur. If this service fails, users might be unable to check seat availability or complete bookings.
The Booking Service is responsible for processing and confirming bookings. A failure here could lead to incomplete bookings, double charges, or a poor user experience.
The Payment Gateway is a critical external dependency. If it fails, users will not be able to complete payments, leading to abandoned bookings.