Untitled
unknown
plain_text
a year ago
17 kB
4
Indexable
-- Drop queries to remove existing tables (if they exist)
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 order_plan_detail;
DROP TABLE IF EXISTS `order`;
DROP TABLE IF EXISTS `transaction`;
DROP TABLE IF EXISTS transfer;
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;
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) NOT NULL,
phone_number VARCHAR(20) NOT NULL,
address VARCHAR(255) NOT NULL,
date_of_birth DATE NOT NULL,
gender VARCHAR(10) NOT 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,
FOREIGN KEY (user_detail_id) REFERENCES user_detail(user_detail_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 NOT NULL,
name VARCHAR(50) NOT NULL,
logo_url VARCHAR(255) NOT NULL,
wallpaper_url VARCHAR(255) NOT NULL,
thumbnail_url VARCHAR(255) NOT NULL,
description text,
rating DECIMAL(10, 2) DEFAULT 0,
contact_number VARCHAR(20) NOT NULL,
contact_email VARCHAR(50) NOT NULL,
brand_status_key INT NOT NULL,
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 NOT NULL,
name VARCHAR(255) NOT NULL,
address VARCHAR(255) NOT NULL,
contact_number VARCHAR(20) NOT NULL,
logo_url VARCHAR(255) NOT NULL,
wallpaper_url VARCHAR(255) NOT NULL,
thumbnail_url VARCHAR(255) NOT NULL,
description text,
latitude DECIMAL(10,8) NOT NULL,
longitude DECIMAL(11,8) NOT NULL,
rating DECIMAL(10, 2) DEFAULT 0,
capacity INT NOT NULL,
area DECIMAL(10, 2),
city VARCHAR(255) DEFAULT 'Tất cả',
FOREIGN KEY (brand_id) REFERENCES brand(brand_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,
description VARCHAR(255),
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 NOT NULL,
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 NOT NULL,
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)
);
-- Transfer table to do the function related to transferring credit
CREATE TABLE IF NOT EXISTS transfer (
transfer_id INT AUTO_INCREMENT PRIMARY KEY,
sender_id INT NOT NULL,
receiver_id INT NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
transfer_date DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (sender_id) REFERENCES `user`(user_id),
FOREIGN KEY (receiver_id) REFERENCES `user`(user_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)
);
-- 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,
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 (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,
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)
);
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)
);
DELIMITER $$
CREATE TRIGGER update_gym_rating_avg
AFTER INSERT ON user_feedback
FOR EACH ROW
BEGIN
DECLARE dept_id INT;
DECLARE total, count INT;
SET dept_id = NEW.department_id;
SELECT IFNULL(SUM(rating),0), COUNT(*) INTO total, count
FROM user_feedback
WHERE department_id = dept_id;
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(3,1);
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