사용자의 프로필을 관리하는 테이블을 설계해줘

기존 JWT 로그인 시스템을 확장하여 사용자 프로필을 관리하는 포괄적인 테이블 구조를 설계해드리겠습니다.

사용자 프로필 관리 테이블 설계

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);

11. 유용한 뷰(View) 생성

-- 완전한 사용자 프로필 뷰
CREATE VIEW user_complete_profiles AS
SELECT 
    u.id,
    u.email,
    u.name,
    u.phone,
    u.status,
    u.created_date,
    u.last_login,

    -- 프로필 정보
    p.first_name,
    p.last_name,
    p.nickname,
    p.display_name,
    p.bio,
    p.birth_date,
    p.gender,
    p.profile_image_url,
    p.profile_visibility,

    -- 설정 정보
    s.theme,
    s.language,
    s.email_notifications,
    s.two_factor_enabled,

    -- 계산된 필드
    TIMESTAMPDIFF(YEAR, p.birth_date, CURDATE()) AS age,
    (SELECT COUNT(*) FROM user_addresses WHERE user_id = u.id) AS address_count,
    (SELECT COUNT(*) FROM user_social_accounts WHERE user_id = u.id) AS social_account_count

FROM users u
LEFT JOIN user_profiles p ON u.id = p.user_id
LEFT JOIN user_settings s ON u.id = s.user_id;

-- 활성 사용자 통계 뷰
CREATE VIEW user_activity_stats AS
SELECT 
    u.id,
    u.email,
    u.last_login,
    (SELECT COUNT(*) FROM user_activity_logs WHERE user_id = u.id AND activity_type = 'LOGIN' 
     AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)) AS login_count_30d,
    (SELECT MAX(created_at) FROM user_activity_logs WHERE user_id = u.id) AS last_activity,
    (SELECT COUNT(*) FROM user_activity_logs WHERE user_id = u.id 
     AND activity_type = 'PROFILE_UPDATE' AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)) AS profile_updates_7d
FROM users u;

12. 인덱스 최적화 쿼리

-- 성능 최적화를 위한 추가 인덱스
CREATE INDEX idx_users_email_status ON users(email, status);
CREATE INDEX idx_user_profiles_visibility ON user_profiles(profile_visibility);
CREATE INDEX idx_activity_logs_recent ON user_activity_logs(user_id, created_at DESC);
CREATE INDEX idx_files_user_type ON user_files(user_id, file_type);

-- 복합 인덱스
CREATE INDEX idx_addresses_user_default ON user_addresses(user_id, is_default, address_type);
CREATE INDEX idx_social_user_platform ON user_social_accounts(user_id, platform, is_verified);

테이블 관계도 요약

핵심 관계:

• users (1) ↔ (1) user_profiles
• users (1) ↔ (1) user_settings
• users (1) ↔ (N) user_addresses
• users (1) ↔ (N) user_social_accounts
• users (1) ↔ (N) user_files
• users (1) ↔ (N) user_activity_logs
• users (1) ↔ (N) user_preferences
• users (1) ↔ (N) profile_change_history

주요 특징

확장성:

• JSON 필드를 활용한 유연한 메타데이터 저장
• 다중 주소, 소셜 계정 지원
• 선호도 시스템으로 개인화 기능 지원

보안 및 감사:

• 모든 프로필 변경 사항 추적
• 활동 로그를 통한 보안 모니터링
• 승인 워크플로우 지원

성능 최적화:

• 적절한 인덱스 설계
• 뷰를 통한 자주 사용되는 쿼리 최적화
• 파티셔닝 고려 (대용량 로그 테이블)

이 설계를 통해 포괄적이고 확장 가능한 사용자 프로필 관리 시스템을 구축할 수 있습니다.