Untitled
unknown
plain_text
2 years ago
16 kB
12
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_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 NOT NULL,
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,
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),
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
);
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)
);
-- 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,
gym_department_id INT NOT NULL,
user_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 (gym_department_id) REFERENCES gym_department(gym_department_id),
foreign key (user_id)references user(user_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 ;
Editor is loading...