Database merge draft
unknown
plain_text
2 years ago
5.9 kB
7
Indexable
-- User table to store user information CREATE TABLE user ( user_id INT AUTO_INCREMENT PRIMARY KEY, user_account VARCHAR(250) CHARACTER SET utf8mb4 NOT NULL, user_password VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, user_create_time VARCHAR(20) NOT NULL, balance DECIMAL(10, 2) DEFAULT 0.0, user_deleted TINYINT(1) NOT NULL ); -- Role table to store user roles CREATE TABLE role ( role_id INT AUTO_INCREMENT PRIMARY KEY, role_name VARCHAR(20) CHARACTER SET utf8mb4 NOT NULL ); -- User Role table to store the relationship between users and roles CREATE TABLE user_role ( user_role_id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, role_id INT NOT NULL, FOREIGN KEY (user_id) REFERENCES user (user_id), FOREIGN KEY (role_id) REFERENCES role (role_id) ); -- Gym Department table to store gym department information CREATE TABLE gym_department ( gym_department_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL, address VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL, contact_number VARCHAR(20) CHARACTER SET utf8mb4 NOT NULL, logo_url VARCHAR(255) CHARACTER SET utf8mb4, opening_hours VARCHAR(255) CHARACTER SET utf8mb4, image_url VARCHAR(255) CHARACTER SET utf8mb4, description TEXT CHARACTER SET utf8mb4 ); -- Gymer table to store gymer information CREATE TABLE gymer ( gymer_id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, gym_id INT NOT NULL, image_url VARCHAR(255) CHARACTER SET utf8mb4, name VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL, contact_number VARCHAR(20) CHARACTER SET utf8mb4 NOT NULL, plan_id INT, check_in_time DATETIME, check_out_time DATETIME, FOREIGN KEY (user_id) REFERENCES user(user_id), FOREIGN KEY (gym_id) REFERENCES gym_department(gym_department_id), FOREIGN KEY (plan_id) REFERENCES gym_plan(plan_id) ); -- Shift table to store shift information CREATE TABLE shift ( shift_id INT PRIMARY KEY AUTO_INCREMENT, employee_id INT NOT NULL, start_time TIME NOT NULL, end_time TIME NOT NULL, FOREIGN KEY (employee_id) REFERENCES gym_employee(employee_id) ); -- Gym Plan table to store gym plan information CREATE TABLE gym_plan ( plan_id INT PRIMARY KEY AUTO_INCREMENT, gym_id INT NOT NULL, name VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL, description VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL, price DECIMAL(10, 2) NOT NULL, duration INT NOT NULL, validity INT NOT NULL, image_url VARCHAR(255) CHARACTER SET utf8mb4, FOREIGN KEY (gym_id) REFERENCES gym_department(gym_department_id) ); -- Gym Employee table to store gym employee information CREATE TABLE gym_employee ( employee_id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, gym_id INT NOT NULL, name VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL, contact_number VARCHAR(20) CHARACTER SET utf8mb4 NOT NULL, schedule VARCHAR(255) CHARACTER SET utf8mb4, image_url VARCHAR(255) CHARACTER SET utf8mb4, FOREIGN KEY (user_id) REFERENCES user(user_id), FOREIGN KEY (gym_id) REFERENCES gym_department(gym_department_id) ); -- User Booking table to store user booking information CREATE TABLE user_booking ( booking_id INT PRIMARY KEY AUTO_INCREMENT, gymer_id INT NOT NULL, shift_id INT NOT NULL, booking_time DATETIME NOT NULL, booking_status INT NOT NULL, FOREIGN KEY (gymer_id) REFERENCES gymer(gymer_id), FOREIGN KEY (shift_id) REFERENCES shift(shift_id) ); -- User Cart table to store user cart information CREATE TABLE user_cart ( cart_id INT PRIMARY KEY AUTO_INCREMENT, gymer_id INT NOT NULL, plan_id INT NOT NULL, quantity INT NOT NULL, FOREIGN KEY (gymer_id) REFERENCES gymer(gymer_id), FOREIGN KEY (plan_id) REFERENCES gym_plan(plan_id) ); -- User Wallet table to store user wallet information CREATE TABLE user_wallet ( wallet_id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, FOREIGN KEY (user_id) REFERENCES user(user_id) ); -- User Transaction table to store user transaction information CREATE TABLE user_transaction ( transaction_id INT PRIMARY KEY AUTO_INCREMENT, wallet_id INT NOT NULL, amount DECIMAL(10, 2) NOT NULL, transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (wallet_id) REFERENCES user_wallet(wallet_id) ); -- User Inventory table to store user inventory information CREATE TABLE user_inventory ( inventory_id INT PRIMARY KEY AUTO_INCREMENT, gymer_id INT NOT NULL, plan_id INT NOT NULL, FOREIGN KEY (gymer_id) REFERENCES gymer(gymer_id), FOREIGN KEY (plan_id) REFERENCES gym_plan(plan_id) ); -- History table to store user history information CREATE TABLE history ( history_id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, action_type VARCHAR(20) CHARACTER SET utf8mb4 NOT NULL, action_details TEXT CHARACTER SET utf8mb4, action_time DATETIME NOT NULL, FOREIGN KEY (user_id) REFERENCES user(user_id) ); -- User Feedback table to store user feedback information CREATE TABLE user_feedback ( feedback_id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, department_id INT NOT NULL, rating INT NOT NULL, comments TEXT CHARACTER SET utf8mb4, feedback_time DATETIME NOT NULL, feedback_status INT NOT NULL, FOREIGN KEY (user_id) REFERENCES user(user_id), FOREIGN KEY (department_id) REFERENCES gym_department(gym_department_id) );
Editor is loading...