Design Movie Data Base

[tharunkumarreddytummala]

1. Users Table

CREATE TABLE Users (
    User_ID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    Password VARCHAR(255) NOT NULL,
    Phone_Number VARCHAR(15),
    Role ENUM('Admin', 'User') NOT NULL
);

2. Theatre Table

CREATE TABLE Theatres (
    Theatre_ID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Location VARCHAR(255) NOT NULL,
    Total_Seats INT NOT NULL
);

3. Movies Table

CREATE TABLE Movies (
    Movie_ID INT AUTO_INCREMENT PRIMARY KEY,
    Title VARCHAR(100) NOT NULL,
    Genre VARCHAR(50),
    Duration TIME NOT NULL,
    Language VARCHAR(50),
    Release_Date DATE
);

4. Screens Table

CREATE TABLE Screens (
    Screen_ID INT AUTO_INCREMENT PRIMARY KEY,
    Theatre_ID INT,
    Total_Seats INT NOT NULL,
    FOREIGN KEY (Theatre_ID) REFERENCES Theatres(Theatre_ID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

5. Seats Table

CREATE TABLE Seats (
    Seat_ID INT AUTO_INCREMENT PRIMARY KEY,
    Screen_ID INT,
    Seat_Number INT NOT NULL,
    Is_Booked BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (Screen_ID) REFERENCES Screens(Screen_ID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

6. Shows Table

CREATE TABLE Shows (
    Show_ID INT AUTO_INCREMENT PRIMARY KEY,
    Movie_ID INT,
    Screen_ID INT,
    Show_Time DATETIME NOT NULL,
    FOREIGN KEY (Movie_ID) REFERENCES Movies(Movie_ID)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (Screen_ID) REFERENCES Screens(Screen_ID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

7. Bookings Table

CREATE TABLE Bookings (
    Booking_ID INT AUTO_INCREMENT PRIMARY KEY,
    User_ID INT,
    Show_ID INT,
    Booking_Date DATETIME DEFAULT CURRENT_TIMESTAMP,
    Payment_Status ENUM('Pending', 'Completed', 'Failed') NOT NULL,
    FOREIGN KEY (User_ID) REFERENCES Users(User_ID)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (Show_ID) REFERENCES Shows(Show_ID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

8. Payments Table

CREATE TABLE Payments (
    Payment_ID INT AUTO_INCREMENT PRIMARY KEY,
    Booking_ID INT,
    Amount DECIMAL(10, 2) NOT NULL,
    Payment_Method ENUM('Credit_Card', 'Debit_Card', 'PayPal', 'UPI', 'Net_Banking'),
    Payment_Status ENUM('Success', 'Failure') NOT NULL,
    QR_Code VARCHAR(255),
    FOREIGN KEY (Booking_ID) REFERENCES Bookings(Booking_ID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE Theatre_Movies ( Theatre_ID INT, Movie_ID INT, FOREIGN KEY (Theatre_ID) REFERENCES Theatres(Theatre_ID), FOREIGN KEY (Movie_ID) REFERENCES Movies(Movie_ID), PRIMARY KEY (Theatre_ID, Movie_ID) );

[tharunkumarreddytummala]

@saiyaswanth0412 - Update Theater Model

[saiyaswanth0412]

CREATE TABLE Theatre_Movies ( Theatre_ID INT, Movie_ID INT, FOREIGN KEY (Theatre_ID) REFERENCES Theatres(Theatre_ID), FOREIGN KEY (Movie_ID) REFERENCES Movies(Movie_ID), PRIMARY KEY (Theatre_ID, Movie_ID) ); please add this as Theatre_Movies can show many to many relationship as one theatre can show multiple movies and single movie can be showed in multiple theatres so a junction table is needed for Theatre_Movies for many to many relationship the previous documentation is updated please check @tharunkumarreddytummala