Key Entities (Tables)

Users Table

 

  • Purpose: Stores information about users (both customers and admins).

 

  • Table Name: users

 

  • Columns:

    • user_id (Primary Key): Unique identifier for the user.

    • username: User’s chosen username.

    • email: User’s email address.

    • password_hash: Hashed password for authentication.

    • phone_number: User’s contact number.

    • created_at: Timestamp of when the user registered.

    • updated_at: Timestamp of the last update to the user’s profile.

    • role: Can be ‘customer’, ‘admin’, etc.

 

Example:

CREATE TABLE users (
user_id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
phone_number VARCHAR(15),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
role VARCHAR(20) DEFAULT 'customer'
);

 

Movies Table

 

  • Purpose: Stores details about movies (for booking and display purposes).

 

  • Table Name: movies

 

  • Columns:

    • movie_id (Primary Key): Unique identifier for the movie.

    • title: Movie title.

    • genre: Genre of the movie (Action, Drama, Comedy, etc.).

    • duration: Length of the movie in minutes.

    • rating: Average rating of the movie.

    • language: Language(s) the movie is available in.

    • release_date: Date the movie was released.

    • description: Brief summary of the movie.

    • created_at: Timestamp of movie creation.

    • updated_at: Timestamp of last update.

 

Example:

CREATE TABLE movies (
movie_id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
genre VARCHAR(50),
duration INT,
rating FLOAT,
language VARCHAR(50),
release_date DATE,
description TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

 

Shows Table

 

  • Purpose: Stores showtimes and movie screenings at different theaters.

 

  • Table Name: shows

 

  • Columns:

    • show_id (Primary Key): Unique identifier for each show.

    • movie_id (Foreign Key): Links to the movies table.

    • theater_id (Foreign Key): Links to the theaters table.

    • show_time: Date and time of the show.

    • available_seats: Number of seats available for the show.

    • created_at: Timestamp of show creation.

    • updated_at: Timestamp of last update.

 

Example:

CREATE TABLE shows (
show_id INT PRIMARY KEY AUTO_INCREMENT,
movie_id INT,
theater_id INT,
show_time DATETIME,
available_seats INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (movie_id) REFERENCES movies(movie_id),
FOREIGN KEY (theater_id) REFERENCES theaters(theater_id)
);

 

Theaters Table

 

  • Purpose: Stores information about the theaters where movies are screened.

 

  • Table Name: theaters

 

  • Columns:

    • theater_id (Primary Key): Unique identifier for each theater.

    • theater_name: Name of the theater.

    • location: The address or location of the theater.

    • total_seats: Total number of seats in the theater.

    • created_at: Timestamp of when the theater was added.

    • updated_at: Timestamp of last update.

 

Example:

CREATE TABLE theaters (
theater_id INT PRIMARY KEY AUTO_INCREMENT,
theater_name VARCHAR(255) NOT NULL,
location VARCHAR(255),
total_seats INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

 

Bookings Table

 

  • Purpose: Stores the bookings made by users.

 

  • Table Name: bookings

 

  • Columns:

    • booking_id (Primary Key): Unique identifier for the booking.

    • user_id (Foreign Key): Links to the users table.

    • show_id (Foreign Key): Links to the shows table.

    • seats: Number of seats booked.

    • total_amount: Total amount for the booking.

    • booking_status: Status (e.g., confirmed, cancelled).

    • payment_status: Status of payment (e.g., paid, pending).

    • created_at: Timestamp of booking.

    • updated_at: Timestamp of last update.

 

Example:

CREATE TABLE bookings (
booking_id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
show_id INT,
seats INT,
total_amount FLOAT,
booking_status VARCHAR(50) DEFAULT 'confirmed',
payment_status VARCHAR(50) DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (show_id) REFERENCES shows(show_id)
);

 

Payments Table

 

  • Purpose: Tracks payment transactions for bookings.

 

  • Table Name: payments

 

  • Columns:

    • payment_id (Primary Key): Unique identifier for the payment.

    • booking_id (Foreign Key): Links to the bookings table.

    • payment_method: Method of payment (Credit Card, UPI, etc.).

    • payment_status: Payment status (e.g., success, failed).

    • amount: Amount paid.

    • payment_date: Date and time of payment.

    • created_at: Timestamp of payment creation.

 

Example:

CREATE TABLE payments (
payment_id INT PRIMARY KEY AUTO_INCREMENT,
booking_id INT,
payment_method VARCHAR(50),
payment_status VARCHAR(50),
amount FLOAT,
payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (booking_id) REFERENCES bookings(booking_id)
);

7. Reviews Table

  • Purpose: Allows users to review and rate movies.

 

  • Table Name: reviews

 

  • Columns:

    • review_id (Primary Key): Unique identifier for the review.

    • user_id (Foreign Key): Links to the users table.

    • movie_id (Foreign Key): Links to the movies table.

    • rating: Rating given to the movie (e.g., 1-5 stars).

    • review_text: The text content of the review.

    • created_at: Timestamp of when the review was submitted.

 

Example:

CREATE TABLE reviews (
review_id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
movie_id INT,
rating INT,
review_text TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (movie_id) REFERENCES movies(movie_id)
);

8. Genres Table

  • Purpose: Stores the genres of movies.

 

  • Table Name: genres

 

  • Columns:
  • genre_id (Primary Key): Unique identifier for each genre.

    • genre_name: The name of the genre (e.g., Drama, Action).

 

Example:

CREATE TABLE genres (
genre_id INT PRIMARY KEY AUTO_INCREMENT,
genre_name VARCHAR(50)
);

Relationships Between Tables

  • Users can make multiple Bookings.
  • Movies can have multiple Reviews.
  • Movies can be shown at multiple Theaters and Shows.
  • Bookings are associated with a Show and a User.
  • Payments are linked to Bookings.
  • Shows are linked to a Movie and a Theater.

Normalization and Design Considerations

  • Normalization: The schema is normalized to the 3rd normal form (3NF), ensuring that redundant data is minimized.

 

  • Indexes: You should create indexes on frequently searched columns such as movie_id, user_id, show_id, and theater_id for quick lookups.

 

  • Scalability: Partitioning of data (e.g., user data, movie data) based on location or user type can help improve scalability.

 

  • Consistency: Transactions are used for booking and payment operations to maintain data consistency.
PreviousNext