-- -------------------------------------------------------------------
-- 1. Reference tables without dependencies
-- -------------------------------------------------------------------
CREATE TABLE region (
region_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
code VARCHAR(20) NOT NULL UNIQUE,
name VARCHAR(50) NOT NULL
);
CREATE TABLE report_reason (
report_reason_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
code VARCHAR(50) NOT NULL UNIQUE,
description VARCHAR(255) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
CREATE TABLE university (
university_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
university_code CHAR(7) NOT NULL UNIQUE,
name VARCHAR(100) NOT NULL,
region_id INT NOT NULL,
address VARCHAR(255) NULL,
CONSTRAINT fk_university_region_id FOREIGN KEY (region_id) REFERENCES region (region_id)
);
-- -------------------------------------------------------------------
-- 2. users and photo (circular reference needs to be resolved)
-- Create the users table first, then add the photo reference FK later using ALTER
-- -------------------------------------------------------------------
CREATE TABLE users (
user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
kakao_oauth_id VARCHAR(255) NOT NULL UNIQUE,
profile_photo_id INT NULL, -- Add this FK later
nickname VARCHAR(100) NOT NULL,
position ENUM('VOCAL', 'GUITAR', 'KEYBOARD', 'BASS', 'DRUM', 'OTHER') NOT NULL,
university_id INT NOT NULL,
admin_role ENUM('ADMIN', 'USER') NOT NULL DEFAULT 'USER',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL,
-- CONSTRAINT fk_users_profile_photo_id FOREIGN KEY (profile_photo_id) REFERENCES photo (photo_id)
CONSTRAINT fk_users_university_id FOREIGN KEY (university_id) REFERENCES university (university_id)
);
CREATE TABLE photo (
photo_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
uploader_user_id INT NOT NULL,
photo_type ENUM('PROFILE', 'CLUB', 'PROMO') NOT NULL,
image_url VARCHAR(512) NOT NULL,
uploaded_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL,
CONSTRAINT fk_photo_uploader_user_id FOREIGN KEY (uploader_user_id) REFERENCES users (user_id)
);
-- Now add the FK constraint referencing photo to the users table
ALTER TABLE users
ADD CONSTRAINT fk_users_profile_photo_id FOREIGN KEY (profile_photo_id) REFERENCES photo (photo_id);
/* ------------------------------------------------------------------ */
/* 3. Users (FK to photo added later to break the cycle) */
/* ------------------------------------------------------------------ */
CREATE TABLE user_timetable (
user_timetable_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
name VARCHAR(100) NOT NULL,
user_timetable_data JSON NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL,
CONSTRAINT fk_user_timetable_user_id FOREIGN KEY (user_id) REFERENCES users (user_id)
);
/* ------------------------------------------------------------------ */
/* 4. Club & Team hierarchy */
/* ------------------------------------------------------------------ */
CREATE TABLE club (
club_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
chatroom_url VARCHAR(255) NULL,
description TEXT NULL,
instagram_id VARCHAR(50) NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL
);
CREATE TABLE club_university (
club_university_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
club_id INT NOT NULL,
university_id INT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY idx_club_university_club_id_university_id (club_id, university_id),
CONSTRAINT fk_club_university_club_id FOREIGN KEY (club_id) REFERENCES club (club_id),
CONSTRAINT fk_club_university_university_id FOREIGN KEY (university_id) REFERENCES university (university_id)
);
CREATE TABLE club_member (
club_member_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
club_id INT NOT NULL,
user_id INT NOT NULL,
role ENUM('REPRESENTATIVE', 'MEMBER') NOT NULL DEFAULT 'MEMBER',
joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY idx_club_member_club_id_user_id (club_id, user_id),
CONSTRAINT fk_club_member_club_id FOREIGN KEY (club_id) REFERENCES club (club_id),
CONSTRAINT fk_club_member_user_id FOREIGN KEY (user_id) REFERENCES users (user_id)
);
CREATE TABLE club_event (
club_event_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
club_id INT NOT NULL,
name VARCHAR(255) NOT NULL,
start_datetime DATETIME NOT NULL,
end_datetime DATETIME NOT NULL,
location VARCHAR(255) NULL,
address VARCHAR(255) NULL,
description TEXT NULL,
creator_user_id INT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL,
CONSTRAINT fk_club_event_club_id FOREIGN KEY (club_id) REFERENCES club (club_id),
CONSTRAINT fk_club_event_creator_user_id FOREIGN KEY (creator_user_id) REFERENCES users (user_id)
);
CREATE TABLE club_event_participant (
club_event_participant_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
club_event_id INT NOT NULL,
user_id INT NOT NULL,
UNIQUE KEY idx_club_event_participant_club_event_id_user_id (club_event_id, user_id),
CONSTRAINT fk_club_event_participant_club_event_id FOREIGN KEY (club_event_id) REFERENCES club_event (club_event_id),
CONSTRAINT fk_club_event_participant_user_id FOREIGN KEY (user_id) REFERENCES users (user_id)
);
CREATE TABLE club_photo (
photo_id INT NOT NULL PRIMARY KEY,
club_id INT NOT NULL,
description TEXT NULL,
is_pinned BOOLEAN NOT NULL DEFAULT false,
is_public BOOLEAN NOT NULL DEFAULT true,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL,
UNIQUE KEY idx_club_photo_club_id_photo_id (club_id, photo_id),
CONSTRAINT fk_club_photo_photo_id FOREIGN KEY (photo_id) REFERENCES photo (photo_id),
CONSTRAINT fk_club_photo_club_id FOREIGN KEY (club_id) REFERENCES club (club_id)
);
CREATE TABLE team (
team_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
club_id INT NOT NULL,
name VARCHAR(100) NOT NULL,
description TEXT NULL,
creator_user_id INT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL,
CONSTRAINT fk_team_club_id FOREIGN KEY (club_id) REFERENCES club (club_id),
CONSTRAINT fk_team_creator_user_id FOREIGN KEY (creator_user_id) REFERENCES users (user_id)
);
CREATE TABLE team_member (
team_member_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
team_id INT NOT NULL,
user_id INT NOT NULL,
joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY idx_team_member_team_id_user_id (team_id, user_id),
CONSTRAINT fk_team_member_team_id FOREIGN KEY (team_id) REFERENCES team (team_id),
CONSTRAINT fk_team_member_user_id FOREIGN KEY (user_id) REFERENCES users (user_id)
);
CREATE TABLE team_event (
team_event_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
team_id INT NOT NULL,
name VARCHAR(255) NOT NULL,
start_datetime DATETIME NOT NULL,
end_datetime DATETIME NOT NULL,
location VARCHAR(255) NULL,
address VARCHAR(255) NULL,
no_position ENUM('VOCAL', 'GUITAR', 'DRUM', 'NONE') NULL DEFAULT 'NONE',
description TEXT NULL,
creator_user_id INT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL,
CONSTRAINT fk_team_event_team_id FOREIGN KEY (team_id) REFERENCES team (team_id),
CONSTRAINT fk_team_event_creator_user_id FOREIGN KEY (creator_user_id) REFERENCES users (user_id)
);
CREATE TABLE team_event_participant (
team_event_participant_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
team_event_id INT NOT NULL,
user_id INT NOT NULL,
UNIQUE KEY idx_team_event_participant_team_event_id_user_id (team_event_id, user_id),
CONSTRAINT fk_team_event_participant_team_event_id FOREIGN KEY (team_event_id) REFERENCES team_event (team_event_id),
CONSTRAINT fk_team_event_participant_user_id FOREIGN KEY (user_id) REFERENCES users (user_id)
);
/* ------------------------------------------------------------------ */
/* 5. Poll / Vote */
/* ------------------------------------------------------------------ */
CREATE TABLE poll (
poll_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
club_id INT NOT NULL,
team_id INT NULL,
title VARCHAR(255) NOT NULL,
start_datetime DATETIME NOT NULL,
end_datetime DATETIME NOT NULL,
creator_user_id INT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL,
CONSTRAINT fk_poll_club_id FOREIGN KEY (club_id) REFERENCES club (club_id),
CONSTRAINT fk_poll_team_id FOREIGN KEY (team_id) REFERENCES team (team_id),
CONSTRAINT fk_poll_creator_user_id FOREIGN KEY (creator_user_id) REFERENCES users (user_id)
);
CREATE TABLE poll_song (
poll_song_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
poll_id INT NOT NULL,
song_name VARCHAR(255) NOT NULL,
artist_name VARCHAR(255) NULL,
youtube_url VARCHAR(255) NULL,
description TEXT NULL,
suggester_user_id INT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL,
CONSTRAINT fk_poll_song_poll_id FOREIGN KEY (poll_id) REFERENCES poll (poll_id),
CONSTRAINT fk_poll_song_suggester_user_id FOREIGN KEY (suggester_user_id) REFERENCES users (user_id)
);
CREATE TABLE vote (
vote_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
poll_song_id INT NOT NULL,
user_id INT NOT NULL,
voted_mark ENUM('LIKE', 'DISLIKE', 'CANT', 'HAJJ') NOT NULL,
voted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY idx_vote_poll_song_id_user_id (poll_song_id, user_id),
CONSTRAINT fk_vote_poll_song_id FOREIGN KEY (poll_song_id) REFERENCES poll_song (poll_song_id),
CONSTRAINT fk_vote_user_id FOREIGN KEY (user_id) REFERENCES users (user_id)
);
/* ------------------------------------------------------------------ */
/* 6. Promo & related entities */
/* ------------------------------------------------------------------ */
CREATE TABLE promo (
promo_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
club_id INT NOT NULL,
creator_user_id INT NOT NULL,
title VARCHAR(255) NOT NULL,
admission_fee DECIMAL(10, 2) NULL,
event_datetime DATETIME NULL,
location VARCHAR(255) NULL,
address VARCHAR(255) NULL,
description TEXT NULL,
status ENUM('UPCOMING', 'ONGOING', 'COMPLETED') NOT NULL DEFAULT 'UPCOMING',
view_count INT NOT NULL DEFAULT 0,
comment_count INT NOT NULL DEFAULT 0,
like_count INT NOT NULL DEFAULT 0,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL,
CONSTRAINT fk_promo_club_id FOREIGN KEY (club_id) REFERENCES club (club_id),
CONSTRAINT fk_promo_creator_user_id FOREIGN KEY (creator_user_id) REFERENCES users (user_id)
);
CREATE TABLE promo_like (
promo_like_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
promo_id INT NOT NULL,
user_id INT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY idx_promo_like_promo_id_user_id (promo_id, user_id),
CONSTRAINT fk_promo_like_promo_id FOREIGN KEY (promo_id) REFERENCES promo (promo_id),
CONSTRAINT fk_promo_like_user_id FOREIGN KEY (user_id) REFERENCES users (user_id)
);
CREATE TABLE promo_report (
promo_report_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
promo_id INT NOT NULL,
reporter_user_id INT NOT NULL,
report_reason_id INT NOT NULL,
description TEXT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY unique_promo_report (promo_id, reporter_user_id),
CONSTRAINT fk_promo_report_promo_id FOREIGN KEY (promo_id) REFERENCES promo (promo_id),
CONSTRAINT fk_promo_report_reporter_user_id FOREIGN KEY (reporter_user_id) REFERENCES users (user_id),
CONSTRAINT fk_promo_report_report_reason_id FOREIGN KEY (report_reason_id) REFERENCES report_reason (report_reason_id)
);
CREATE TABLE promo_comment (
promo_comment_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
promo_id INT NOT NULL,
creator_user_id INT NOT NULL,
description TEXT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL,
CONSTRAINT fk_promo_comment_promo_id FOREIGN KEY (promo_id) REFERENCES promo (promo_id),
CONSTRAINT fk_promo_comment_creator_user_id FOREIGN KEY (creator_user_id) REFERENCES users (user_id)
);
CREATE TABLE promo_comment_like (
promo_comment_like_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
promo_comment_id INT NOT NULL,
user_id INT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY idx_promo_comment_like_promo_comment_id_user_id (promo_comment_id, user_id),
CONSTRAINT fk_promo_comment_like_promo_comment_id FOREIGN KEY (promo_comment_id) REFERENCES promo_comment (promo_comment_id),
CONSTRAINT fk_promo_comment_like_user_id FOREIGN KEY (user_id) REFERENCES users (user_id)
);
CREATE TABLE promo_comment_report (
promo_comment_report_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
promo_comment_id INT NOT NULL,
reporter_user_id INT NOT NULL,
report_reason_id INT NOT NULL,
description TEXT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY unique_promo_comment_report (promo_comment_id, reporter_user_id),
CONSTRAINT fk_promo_comment_report_promo_comment_id FOREIGN KEY (promo_comment_id) REFERENCES promo_comment (promo_comment_id),
CONSTRAINT fk_promo_comment_report_reporter_user_id FOREIGN KEY (reporter_user_id) REFERENCES users (user_id),
CONSTRAINT fk_promo_comment_report_report_reason_id FOREIGN KEY (report_reason_id) REFERENCES report_reason (report_reason_id)
);
CREATE TABLE promo_photo (
photo_id INT NOT NULL PRIMARY KEY,
promo_id INT NOT NULL,
deleted_at TIMESTAMP NULL,
UNIQUE KEY idx_promo_photo_promo_id_photo_id (promo_id, photo_id),
CONSTRAINT fk_promo_photo_photo_id FOREIGN KEY (photo_id) REFERENCES photo (photo_id),
CONSTRAINT fk_promo_photo_promo_id FOREIGN KEY (promo_id) REFERENCES promo (promo_id)
);