Untitled
unknown
plain_text
a year ago
26 kB
2
Indexable
-- Drop queries to remove existing tables (if they exist)
DROP TABLE IF EXISTS request_withdrawal_history;
DROP TABLE IF EXISTS credit_card;
DROP TABLE IF EXISTS check_in_history;
DROP TABLE IF EXISTS user_feedback;
DROP TABLE IF EXISTS item_inventory;
DROP TABLE IF EXISTS notification;
DROP TABLE IF EXISTS transfer_credit_history;
DROP TABLE IF EXISTS order_plan_detail;
DROP TABLE IF EXISTS transfer;
DROP TABLE IF EXISTS `order`;
DROP TABLE IF EXISTS `transaction`;
DROP TABLE IF EXISTS gymer_booking;
DROP TABLE IF EXISTS shift;
DROP TABLE IF EXISTS gym_department_plans;
DROP TABLE IF EXISTS gym_department_amenities;
DROP TABLE IF EXISTS gym_plan;
DROP TABLE IF EXISTS mst_kbn;
DROP TABLE IF EXISTS gym_department_services;
DROP TABLE IF EXISTS gym_department_schedule;
DROP TABLE IF EXISTS gym_department_albums;
DROP TABLE IF EXISTS gym_department_amenities;
DROP TABLE IF EXISTS gym_department_features;
DROP TABLE IF EXISTS features;
DROP TABLE IF EXISTS gym_department;
DROP TABLE IF EXISTS brand_albums;
DROP TABLE IF EXISTS brand_amenities;
DROP TABLE IF EXISTS brand;
DROP TABLE IF EXISTS user_role;
DROP TABLE IF EXISTS role;
DROP TABLE IF EXISTS wallet;
DROP TABLE IF EXISTS `user`;
DROP TABLE IF EXISTS user_detail;
DROP TABLE IF EXISTS become_a_partner_request_history;
CREATE TABLE IF NOT EXISTS user_detail (
user_detail_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
phone_number VARCHAR(20) NOT NULL,
address VARCHAR(255),
date_of_birth DATE NOT NULL,
gender VARCHAR(10) NOT NULL,
securityId varchar(12) null,
image_url TEXT NULL
);
-- User table to store user information
CREATE TABLE IF NOT EXISTS `user` (
user_id INT AUTO_INCREMENT PRIMARY KEY,
user_account VARCHAR(250) NOT NULL,
user_password VARCHAR(100) NOT NULL,
user_detail_id INT,
user_create_time VARCHAR(20) NOT NULL,
user_deleted TINYINT NOT NULL,
created_by INT NULL,
first_time boolean default 1,
in_department_id INT NULL,
FOREIGN KEY (user_detail_id) REFERENCES user_detail(user_detail_id),
CONSTRAINT user_FK FOREIGN KEY (created_by) REFERENCES `user`(user_id)
);
-- User Wallet table to store user wallet information
CREATE TABLE IF NOT EXISTS wallet (
wallet_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
balance DECIMAL(10, 2) DEFAULT 0.0,
FOREIGN KEY (user_id) REFERENCES user(user_id)
);
-- Role table to store user roles
CREATE TABLE IF NOT EXISTS role (
role_id INT AUTO_INCREMENT PRIMARY KEY,
role_name VARCHAR(20) NOT NULL
);
-- User Role table to store the relationship between users and roles
CREATE TABLE IF NOT EXISTS 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)
);
CREATE TABLE IF NOT EXISTS brand (
brand_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
name VARCHAR(100) NOT NULL,
logo_url VARCHAR(255),
wallpaper_url VARCHAR(255),
thumbnail_url VARCHAR(255),
description text,
rating DECIMAL(10, 2) DEFAULT 0,
contact_number VARCHAR(20),
contact_email VARCHAR(50),
brand_status_key INT NOT NULL,
money_percent INT DEFAULT 0 ,
first_time boolean default 1,
FOREIGN KEY (user_id) REFERENCES `user`(user_id)
);
CREATE TABLE IF NOT EXISTS brand_amenities (
amenitie_id INT AUTO_INCREMENT PRIMARY KEY,
brand_id INT NOT NULL,
photo_url VARCHAR(255) NOT NULL,
amenitie_name VARCHAR(50),
description text,
amenitie_status INT NOT NULL,
FOREIGN KEY (brand_id) REFERENCES brand(brand_id)
);
-- Gym Department table to store gym department information
CREATE TABLE IF NOT EXISTS gym_department (
gym_department_id INT AUTO_INCREMENT PRIMARY KEY,
gym_department_status_key INT NOT NULL,
brand_id INT NOT NULL,
user_id INT NULL,
name VARCHAR(255) NOT NULL,
address VARCHAR(255),
contact_number VARCHAR(20),
logo_url VARCHAR(255),
wallpaper_url VARCHAR(255),
thumbnail_url VARCHAR(255),
description text,
latitude DECIMAL(10,8),
longitude DECIMAL(11,8),
rating DECIMAL(10, 2) DEFAULT 0,
capacity INT,
area DECIMAL(10, 2),
city VARCHAR(255),
first_time boolean default 0,
FOREIGN KEY (brand_id) REFERENCES brand(brand_id),
FOREIGN KEY (user_id) REFERENCES `user`(user_id)
);
CREATE TABLE IF NOT EXISTS gym_department_albums (
id INT PRIMARY KEY AUTO_INCREMENT,
gym_department_id INT NOT NULL,
photo_url VARCHAR(255) NOT NULL,
FOREIGN KEY(gym_department_id) REFERENCES gym_department(gym_department_id)
);
CREATE TABLE IF NOT EXISTS gym_department_schedule (
id INT AUTO_INCREMENT PRIMARY KEY,
gym_department_id INT NOT NULL,
day VARCHAR(10) NOT NULL,
open_time VARCHAR(255),
close_time VARCHAR(255),
FOREIGN KEY (gym_department_id) REFERENCES gym_department(gym_department_id)
);
CREATE TABLE IF NOT EXISTS features (
feature_id INT AUTO_INCREMENT PRIMARY KEY,
feature_icon varchar(150),
feature_name VARCHAR(50),
feature_status INT NOT NULL
);
-- table name mst_kbn to store type,status of all tables
CREATE TABLE IF NOT EXISTS mst_kbn (
mst_kbn_id INT AUTO_INCREMENT PRIMARY KEY,
mst_kbn_name VARCHAR(50) NOT NULL,
mst_kbn_key INT NOT NULL,
mst_kbn_value VARCHAR(50) NOT NULL
);
-- Gym Plan table to store gym plan information
CREATE TABLE IF NOT EXISTS gym_plan (
plan_id INT AUTO_INCREMENT PRIMARY KEY,
brand_id INT NOT NULL,
gym_plan_key INT,
gym_plan_status_key INT NOT NULL,
gym_plan_type_key INT NOT NULL,
name VARCHAR(255) NOT NULL,
description text,
price DECIMAL(10, 2) ,
price_per_hours DECIMAL(10, 2) ,
plan_sold INT,
duration INT ,
plan_before_active_validity INT NOT NULL,
plan_after_active_validity INT NOT NULL,
FOREIGN KEY (brand_id) REFERENCES brand(brand_id)
);
CREATE TABLE IF NOT EXISTS gym_department_plans (
gym_department_id INT NOT NULL,
plan_id INT NOT NULL,
FOREIGN KEY (gym_department_id) REFERENCES gym_department(gym_department_id),
FOREIGN KEY (plan_id) REFERENCES gym_plan(plan_id),
PRIMARY KEY (gym_department_id, plan_id)
);
CREATE TABLE IF NOT EXISTS gym_department_amenities (
gym_department_id INT NOT NULL,
amenitie_id INT NOT NULL,
FOREIGN KEY (gym_department_id) REFERENCES gym_department(gym_department_id),
FOREIGN KEY (amenitie_id) REFERENCES brand_amenities(amenitie_id),
PRIMARY KEY (gym_department_id, amenitie_id)
);
CREATE TABLE IF NOT EXISTS gym_department_features (
gym_department_feature_id INT AUTO_INCREMENT PRIMARY KEY,
feature_id INT,
gym_department_id INT NOT NULL,
feature_status INT NOT NULL,
FOREIGN KEY (feature_id) REFERENCES features(feature_id),
FOREIGN KEY (gym_department_id) REFERENCES gym_department(gym_department_id)
);
-- Shift table to store shift information
CREATE TABLE IF NOT EXISTS shift (
shift_id INT AUTO_INCREMENT PRIMARY KEY,
gym_department_id INT NOT NULL,
start_time TIME NOT NULL,
end_time TIME NOT NULL,
FOREIGN KEY (gym_department_id) REFERENCES gym_department(gym_department_id)
);
-- Transaction table to do the function with transactions between credit and real money
CREATE TABLE IF NOT EXISTS `transaction` (
transaction_id INT AUTO_INCREMENT PRIMARY KEY,
wallet_id INT NOT NULL,
status VARCHAR(30) NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
transaction_date DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (wallet_id) REFERENCES wallet(wallet_id)
);
-- Order table to store order information
CREATE TABLE IF NOT EXISTS `order` (
order_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
order_create_time DATETIME NOT NULL,
order_status_key INT NOT NULL,
discount INT NOT NULL,
order_total_money DECIMAL(10, 2) NOT NULL,
order_note VARCHAR(500),
FOREIGN KEY (user_id) REFERENCES `user`(user_id)
);
-- Order Plan Detail table to store order plan details
CREATE TABLE IF NOT EXISTS order_plan_detail (
order_detail_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
name VARCHAR(255) NOT NULL,
quantity INT NOT NULL,
price_per_hours DECIMAL(10, 2) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
duration INT NOT NULL,
plan_before_active_validity INT NOT NULL,
plan_after_active_validity INT NOT NULL,
gym_department_id INT NOT NULL,
`plan_active_time` DATETIME DEFAULT NULL,
`item_status_key` INT NOT NULL,
`plan_expired_time` DATETIME DEFAULT NULL,
`description` TEXT,
use_status VARCHAR(100),
FOREIGN KEY (order_id) REFERENCES `order`(order_id),
FOREIGN KEY (gym_department_id) REFERENCES gym_department(gym_department_id)
);
-- Transfer table to do the function related to transferring credit history
CREATE TABLE IF NOT EXISTS transfer_credit_history (
transfer_id INT AUTO_INCREMENT PRIMARY KEY,
sender_id INT NOT NULL,
receiver_id INT NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
order_detail_id INT NOT NULL,
transfer_date DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (order_detail_id) REFERENCES order_plan_detail (order_detail_id),
FOREIGN KEY (sender_id) REFERENCES `user`(user_id),
FOREIGN KEY (receiver_id) REFERENCES `user`(user_id)
);
-- User Inventory table to store user inventory information
CREATE TABLE IF NOT EXISTS item_inventory (
inventory_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
plan_id INT NOT NULL,
plan_active_time DATETIME NOT NULL,
item_status_key INT NOT NULL,
plan_expired_time DATETIME NOT NULL,
FOREIGN KEY (user_id) REFERENCES `user`(user_id),
FOREIGN KEY (plan_id) REFERENCES gym_plan(plan_id)
);
-- User Feedback table to store user feedback information
CREATE TABLE IF NOT EXISTS user_feedback (
feedback_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
gym_plan_id INT,
department_id INT NOT NULL,
rating INT NOT NULL,
comments TEXT,
feedback_time DATETIME NOT NULL,
feedback_status INT NOT NULL,
FOREIGN KEY (user_id) REFERENCES `user`(user_id),
FOREIGN KEY (gym_plan_id) REFERENCES `gym_plan`(plan_id),
FOREIGN KEY (department_id) REFERENCES gym_department(gym_department_id)
);
CREATE TABLE IF NOT EXISTS notification (
notification_id INT auto_increment NOT NULL,
user_id_send INT NOT NULL,
user_id_receive INT NOT NULL,
message TEXT NOT NULL,
time_send DATETIME NOT NULL,
department_id INT NOT NULL,
order_detail_id INT NOT NULL,
message_type VARCHAR(100) NOT NULL,
status INT NOT NULL,
CONSTRAINT Notification_pk PRIMARY KEY (notification_id),
CONSTRAINT Notification_FK FOREIGN KEY (department_id) REFERENCES fitpass.gym_department(gym_department_id),
CONSTRAINT Notification_FK_1 FOREIGN KEY (user_id_send) REFERENCES fitpass.`user`(user_id),
CONSTRAINT Notification_FK_2 FOREIGN KEY (user_id_receive) REFERENCES fitpass.`user`(user_id),
CONSTRAINT Notification_FK_3 FOREIGN KEY (order_detail_id) REFERENCES fitpass.order_plan_detail(order_detail_id)
);
CREATE TABLE IF NOT EXISTS check_in_history (
check_in_history_id INT auto_increment NOT NULL,
order_detail_id INT NOT NULL,
status_key INT NOT NULL,
`check_in_time` DATETIME NOT NULL,
check_out_time DATETIME NULL,
total_credit DECIMAL(10,2) NULL,
emp_checkin_id INT NOT NULL,
feedback_id INT NULL,
CONSTRAINT check_in_history_pk PRIMARY KEY (check_in_history_id),
CONSTRAINT check_in_history_FK FOREIGN KEY (order_detail_id) REFERENCES fitpass.order_plan_detail(order_detail_id),
CONSTRAINT check_in_history_FK_1 FOREIGN KEY (emp_checkin_id) REFERENCES fitpass.`user`(user_id),
CONSTRAINT check_in_history_FK_2 FOREIGN KEY (feedback_id) REFERENCES fitpass.user_feedback(feedback_id)
);
-- bảng credit card
CREATE TABLE IF NOT EXISTS credit_card (
credit_card_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
card_owner_name VARCHAR(100) NOT NULL,
card_number varchar(25) NOT NULL,
status VARCHAR(30) NOT NULL,
bank_name VARCHAR(100) NOT NULL,
FOREIGN KEY (user_id) REFERENCES user(user_id)
);
-- Bảng lịch sử rút tiền
CREATE TABLE IF NOT EXISTS request_withdrawal_history (
request_withdrawal_history_id INT AUTO_INCREMENT PRIMARY KEY,
credit_card_id INT not null,
withdrawal_code VARCHAR(100) NOT NULL,
withdrawal_time timestamp,
amount_credit INT NOT NULL,
actual_money INT NOT NULL,
money_percent INT DEFAULT 0 ,
status VARCHAR(30) NOT NULL,
FOREIGN KEY (credit_card_id) REFERENCES credit_card(credit_card_id)
);
-- Bảng become a partner
CREATE TABLE IF NOT EXISTS become_a_partner_request_history (
become_a_partner_request_history_id INT AUTO_INCREMENT PRIMARY KEY,
brand_name VARCHAR(100) NOT NULL,
brand_owner_name VARCHAR(100) NOT NULL,
contact_number VARCHAR(20) NOT NULL,
address VARCHAR(150) NOT NULL,
web_url VARCHAR(250) ,
contact_email VARCHAR(100) NOT NULL,
send_request_time timestamp, -- thời gian gửi đơn
start_handle_request_time timestamp, -- thời gian bắt đầu xử lý
cancel_request_time timestamp,-- thời gian hủy yêu cầu
approve_request_time timestamp,-- thời gian xác nhận yêu cầu thành công
cancel_reason VARCHAR(350) ,
status VARCHAR(30) NOT NULL
);
DELIMITER $$
CREATE TRIGGER update_gym_rating_avg
AFTER INSERT ON user_feedback
FOR EACH ROW
BEGIN
DECLARE dept_id INT;
DECLARE total DECIMAL(10,2);
DECLARE count DECIMAL(10,2);
SET dept_id = NEW.department_id;
SELECT
IFNULL(SUM(CAST(rating AS DECIMAL(10,2))), 0),
COUNT(*)
INTO
total, count
FROM user_feedback
WHERE department_id = dept_id;
SET count = CAST(count AS DECIMAL(10,2));
SET total = CAST(total AS DECIMAL(10,2));
UPDATE gym_department
SET
rating = total / count
WHERE gym_department_id = dept_id;
END$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER update_brand_rating
AFTER UPDATE ON gym_department
FOR EACH ROW
BEGIN
DECLARE avg_rating DECIMAL(10,2);
SELECT AVG(rating) INTO avg_rating
FROM gym_department
WHERE brand_id = NEW.brand_id;
UPDATE brand
SET rating = avg_rating
WHERE brand_id = NEW.brand_id;
END$$
DELIMITER ;Leave a Comment