Database Schema for Book My Show/ Ticket Master

Designing the database schema for a platform like BookMyShow/Ticket Master involves identifying the key entities and relationships between them.

Below is a detailed breakdown of the tables required:

1. Users

Stores information about the users.

Column Name	Data Type	Description
user_id	BIGINT (PK)	Unique identifier for the user
name	VARCHAR	Name of the user
email	VARCHAR	Email of the user
phone	VARCHAR	Phone number of the user
password_hash	VARCHAR	Hashed password
created_at	TIMESTAMP	Account creation date

2. Theaters

Stores details about theaters.

Column Name	Data Type	Description
theater_id	BIGINT (PK)	Unique identifier for the theater
name	VARCHAR	Theater name
location	VARCHAR	Address/location of the theater
city_id	BIGINT (FK)	Reference to the Cities table
capacity	INT	Total seating capacity

3. Cities

Stores cities where services are available.

Column Name	Data Type	Description
city_id	BIGINT (PK)	Unique identifier for the city
name	VARCHAR	City name
state	VARCHAR	State

4. Movies

Stores movie details.

Column Name	Data Type	Description
movie_id	BIGINT (PK)	Unique identifier for the movie
title	VARCHAR	Movie name
description	TEXT	Movie description
genre	VARCHAR	Genre (e.g., Action, Comedy)
language	VARCHAR	Language of the movie
duration	INT	Duration in minutes
rating	FLOAT	Average user rating
release_date	DATE	Release date of the movie

5. Shows

Stores details about shows in theaters.

Column Name	Data Type	Description
show_id	BIGINT (PK)	Unique identifier for the show
theater_id	BIGINT (FK)	Reference to the Theaters table
movie_id	BIGINT (FK)	Reference to the Movies table
start_time	TIMESTAMP	Start time of the show
end_time	TIMESTAMP	End time of the show
price	DECIMAL	Ticket price

6. Seats

Stores seating details for theaters.

Column Name	Data Type	Description
seat_id	BIGINT (PK)	Unique identifier for the seat
theater_id	BIGINT (FK)	Reference to the Theaters table
seat_number	VARCHAR	Seat number (e.g., A1, B5)
seat_type	ENUM	Type of seat (e.g., Regular, VIP)

7. Bookings

Stores booking details.

Column Name	Data Type	Description
booking_id	BIGINT (PK)	Unique identifier for the booking
user_id	BIGINT (FK)	Reference to the Users table
show_id	BIGINT (FK)	Reference to the Shows table
total_price	DECIMAL	Total price of the booking
status	ENUM	Booking status (Confirmed, Cancelled)
created_at	TIMESTAMP	Booking creation date

8. Booking_Seats

Stores seat information for each booking.

Column Name	Data Type	Description
booking_seat_id	BIGINT (PK)	Unique identifier for the booking-seat relation
booking_id	BIGINT (FK)	Reference to the Bookings table
seat_id	BIGINT (FK)	Reference to the Seats table

9. Payments

Stores payment details.

Column Name	Data Type	Description
payment_id	BIGINT (PK)	Unique identifier for the payment
booking_id	BIGINT (FK)	Reference to the Bookings table
amount	DECIMAL	Amount paid
payment_status	ENUM	Payment status (Success, Failed, Pending)
payment_method	ENUM	Payment method (Card, UPI, Net Banking)
created_at	TIMESTAMP	Payment creation date

10. Offers

Stores promotional offers.

Column Name	Data Type	Description
offer_id	BIGINT (PK)	Unique identifier for the offer
description	VARCHAR	Description of the offer
discount_percentage	FLOAT	Discount percentage
valid_from	TIMESTAMP	Offer start date
valid_to	TIMESTAMP	Offer end date

Relationships

• Users ↔ Bookings (One-to-Many)

• Bookings ↔ Booking_Seats ↔ Seats (Many-to-Many)

• Movies ↔ Shows (One-to-Many)

• Theaters ↔ Seats (One-to-Many)

• Shows ↔ Theaters (One-to-Many)

• Bookings ↔ Payments (One-to-One)