반응형
1. 기존 Users 테이블 수정
-- 기존 users 테이블 수정 (기본 인증 정보만 유지)
ALTER TABLE users
ADD COLUMN phone VARCHAR(20) NULL,
ADD COLUMN email_verified BOOLEAN DEFAULT FALSE,
ADD COLUMN phone_verified BOOLEAN DEFAULT FALSE,
ADD COLUMN status ENUM('ACTIVE', 'INACTIVE', 'SUSPENDED', 'PENDING') DEFAULT 'PENDING',
ADD COLUMN last_password_change TIMESTAMP NULL,
ADD COLUMN failed_login_attempts INT DEFAULT 0,
ADD COLUMN locked_until TIMESTAMP NULL,
ADD INDEX idx_users_status (status),
ADD INDEX idx_users_phone (phone);2. 사용자 프로필 상세 정보 테이블
-- 사용자 프로필 상세 정보 테이블
CREATE TABLE user_profiles (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT NOT NULL UNIQUE,
-- 개인 정보
first_name VARCHAR(50) NULL,
last_name VARCHAR(50) NULL,
nickname VARCHAR(50) NULL,
display_name VARCHAR(100) NULL,
bio TEXT NULL,
-- 생년월일 및 성별
birth_date DATE NULL,
gender ENUM('MALE', 'FEMALE', 'OTHER', 'PREFER_NOT_TO_SAY') NULL,
-- 국적 및 언어
nationality VARCHAR(50) NULL,
preferred_language VARCHAR(10) DEFAULT 'ko',
timezone VARCHAR(50) DEFAULT 'Asia/Seoul',
-- 프로필 이미지
profile_image_url VARCHAR(500) NULL,
profile_image_filename VARCHAR(255) NULL,
cover_image_url VARCHAR(500) NULL,
cover_image_filename VARCHAR(255) NULL,
-- 공개 설정
profile_visibility ENUM('PUBLIC', 'FRIENDS_ONLY', 'PRIVATE') DEFAULT 'PUBLIC',
show_email BOOLEAN DEFAULT FALSE,
show_phone BOOLEAN DEFAULT FALSE,
show_birth_date BOOLEAN DEFAULT FALSE,
-- 타임스탬프
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
INDEX idx_user_profiles_user_id (user_id),
INDEX idx_user_profiles_nickname (nickname),
INDEX idx_user_profiles_display_name (display_name),
COMMENT = '사용자 프로필 상세 정보 테이블'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
3. 사용자 주소 정보 테이블
-- 사용자 주소 정보 테이블 (다중 주소 지원)
CREATE TABLE user_addresses (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT NOT NULL,
-- 주소 타입
address_type ENUM('HOME', 'WORK', 'BILLING', 'SHIPPING', 'OTHER') NOT NULL,
is_default BOOLEAN DEFAULT FALSE,
-- 주소 정보
country VARCHAR(100) NOT NULL,
state_province VARCHAR(100) NULL,
city VARCHAR(100) NOT NULL,
district VARCHAR(100) NULL,
street_address TEXT NOT NULL,
postal_code VARCHAR(20) NULL,
-- 추가 정보
address_label VARCHAR(100) NULL, -- 사용자 지정 라벨 (예: '우리집', '회사')
recipient_name VARCHAR(100) NULL, -- 수령인 이름
recipient_phone VARCHAR(20) NULL,
-- 좌표 정보 (선택사항)
latitude DECIMAL(10, 8) NULL,
longitude DECIMAL(11, 8) NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
INDEX idx_user_addresses_user_id (user_id),
INDEX idx_user_addresses_type (address_type),
INDEX idx_user_addresses_default (user_id, is_default),
COMMENT = '사용자 주소 정보 테이블'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
4. 사용자 설정 테이블
-- 사용자 개별 설정 테이블
CREATE TABLE user_settings (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT NOT NULL,
-- 알림 설정
email_notifications BOOLEAN DEFAULT TRUE,
sms_notifications BOOLEAN DEFAULT TRUE,
push_notifications BOOLEAN DEFAULT TRUE,
marketing_emails BOOLEAN DEFAULT FALSE,
-- 보안 설정
two_factor_enabled BOOLEAN DEFAULT FALSE,
login_notifications BOOLEAN DEFAULT TRUE,
session_timeout_minutes INT DEFAULT 60,
-- UI/UX 설정
theme ENUM('LIGHT', 'DARK', 'SYSTEM') DEFAULT 'SYSTEM',
language VARCHAR(10) DEFAULT 'ko',
date_format VARCHAR(20) DEFAULT 'YYYY-MM-DD',
time_format VARCHAR(10) DEFAULT '24H',
-- 개인정보 설정
data_sharing_consent BOOLEAN DEFAULT FALSE,
analytics_tracking BOOLEAN DEFAULT TRUE,
personalized_ads BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
UNIQUE KEY uk_user_settings_user_id (user_id),
COMMENT = '사용자 개별 설정 테이블'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
5. 소셜 미디어 연결 테이블
-- 소셜 미디어 계정 연결 테이블
CREATE TABLE user_social_accounts (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT NOT NULL,
-- 소셜 플랫폼 정보
platform ENUM('GOOGLE', 'FACEBOOK', 'TWITTER', 'LINKEDIN', 'GITHUB', 'KAKAO', 'NAVER') NOT NULL,
social_id VARCHAR(255) NOT NULL, -- 소셜 플랫폼의 사용자 ID
social_email VARCHAR(255) NULL,
social_username VARCHAR(255) NULL,
-- 연결 상태
is_verified BOOLEAN DEFAULT FALSE,
is_primary BOOLEAN DEFAULT FALSE, -- 주 계정 여부
-- 추가 정보
access_token TEXT NULL,
refresh_token TEXT NULL,
token_expires_at TIMESTAMP NULL,
-- 프로필 정보
avatar_url VARCHAR(500) NULL,
profile_url VARCHAR(500) NULL,
connected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_used_at TIMESTAMP NULL,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
UNIQUE KEY uk_social_platform_id (platform, social_id),
INDEX idx_user_social_user_id (user_id),
INDEX idx_user_social_platform (platform),
COMMENT = '소셜 미디어 계정 연결 테이블'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
6. 사용자 파일 관리 테이블
-- 사용자 파일 관리 테이블
CREATE TABLE user_files (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT NOT NULL,
-- 파일 기본 정보
file_name VARCHAR(255) NOT NULL,
original_name VARCHAR(255) NOT NULL,
file_type ENUM('PROFILE_IMAGE', 'COVER_IMAGE', 'DOCUMENT', 'ATTACHMENT', 'OTHER') NOT NULL,
mime_type VARCHAR(100) NOT NULL,
file_size BIGINT NOT NULL, -- bytes
-- 파일 경로 및 URL
file_path VARCHAR(500) NOT NULL,
file_url VARCHAR(500) NULL,
thumbnail_url VARCHAR(500) NULL,
-- 파일 상태
upload_status ENUM('UPLOADING', 'COMPLETED', 'FAILED') DEFAULT 'UPLOADING',
is_public BOOLEAN DEFAULT FALSE,
-- 메타데이터
alt_text VARCHAR(255) NULL,
description TEXT NULL,
tags JSON NULL,
-- 이미지 전용 필드
width INT NULL,
height INT NULL,
uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
INDEX idx_user_files_user_id (user_id),
INDEX idx_user_files_type (file_type),
INDEX idx_user_files_status (upload_status),
COMMENT = '사용자 파일 관리 테이블'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
7. 사용자 활동 로그 테이블
-- 사용자 활동 로그 테이블
CREATE TABLE user_activity_logs (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT NOT NULL,
-- 활동 정보
activity_type ENUM('LOGIN', 'LOGOUT', 'PROFILE_UPDATE', 'PASSWORD_CHANGE', 'EMAIL_CHANGE',
'PHONE_CHANGE', 'ADDRESS_UPDATE', 'SETTINGS_CHANGE', 'FILE_UPLOAD',
'SOCIAL_CONNECT', 'SOCIAL_DISCONNECT', 'ACCOUNT_VERIFICATION', 'OTHER') NOT NULL,
activity_description TEXT NULL,
-- 세부 정보
changed_fields JSON NULL, -- 변경된 필드들
old_values JSON NULL, -- 이전 값들
new_values JSON NULL, -- 새로운 값들
-- 접속 정보
ip_address VARCHAR(45) NULL,
user_agent TEXT NULL,
device_type VARCHAR(50) NULL,
browser VARCHAR(100) NULL,
os VARCHAR(100) NULL,
location VARCHAR(255) NULL,
-- 결과
is_success BOOLEAN DEFAULT TRUE,
error_message TEXT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
INDEX idx_activity_logs_user_id (user_id),
INDEX idx_activity_logs_type (activity_type),
INDEX idx_activity_logs_created_at (created_at),
INDEX idx_activity_logs_user_type (user_id, activity_type),
COMMENT = '사용자 활동 로그 테이블'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
8. 사용자 선호도 테이블
-- 사용자 선호도 및 관심사 테이블
CREATE TABLE user_preferences (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT NOT NULL,
-- 선호도 카테고리
category VARCHAR(100) NOT NULL, -- 예: 'interests', 'skills', 'hobbies', 'dietary'
preference_key VARCHAR(100) NOT NULL,
preference_value VARCHAR(255) NOT NULL,
-- 가중치 및 우선순위
weight DECIMAL(3,2) DEFAULT 1.00, -- 0.00 ~ 1.00
priority INT DEFAULT 0,
-- 메타데이터
source ENUM('USER_INPUT', 'SYSTEM_INFERRED', 'IMPORTED', 'SURVEY') DEFAULT 'USER_INPUT',
confidence_score DECIMAL(3,2) DEFAULT 1.00,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
UNIQUE KEY uk_user_preferences (user_id, category, preference_key),
INDEX idx_user_preferences_user_id (user_id),
INDEX idx_user_preferences_category (category),
COMMENT = '사용자 선호도 및 관심사 테이블'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
9. 프로필 변경 히스토리 테이블
-- 프로필 변경 히스토리 테이블
CREATE TABLE profile_change_history (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT NOT NULL,
-- 변경 정보
table_name VARCHAR(100) NOT NULL, -- 변경된 테이블명
field_name VARCHAR(100) NOT NULL, -- 변경된 필드명
old_value TEXT NULL,
new_value TEXT NULL,
-- 변경 사유
change_reason VARCHAR(255) NULL,
changed_by ENUM('USER', 'ADMIN', 'SYSTEM') DEFAULT 'USER',
admin_user_id BIGINT NULL, -- 관리자가 변경한 경우
-- 승인 관련
requires_approval BOOLEAN DEFAULT FALSE,
approval_status ENUM('PENDING', 'APPROVED', 'REJECTED') DEFAULT 'APPROVED',
approved_by BIGINT NULL,
approved_at TIMESTAMP NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (admin_user_id) REFERENCES users(id) ON DELETE SET NULL,
FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL,
INDEX idx_profile_history_user_id (user_id),
INDEX idx_profile_history_table (table_name),
INDEX idx_profile_history_approval (approval_status),
COMMENT = '프로필 변경 히스토리 테이블'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
10. 초기 데이터 및 기본 설정
-- 사용자별 기본 프로필 및 설정 생성 트리거
DELIMITER //
CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
-- 기본 프로필 생성
INSERT INTO user_profiles (user_id, display_name)
VALUES (NEW.id, COALESCE(NEW.name, NEW.email));
-- 기본 설정 생성
INSERT INTO user_settings (user_id) VALUES (NEW.id);
END//
DELIMITER ;
-- 기본 선호도 카테고리 데이터
INSERT INTO user_preferences (user_id, category, preference_key, preference_value, source)
SELECT id, 'system', 'account_created', NOW(), 'SYSTEM'
FROM users WHERE id IN (1, 2, 3);
반응형