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:

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:

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:

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:

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:

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:

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:

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:

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.