[스프린트 미션] Fandom-K 서비스 ERD 설계와 데이터베이스 구현

이번 포스팅은 코드잇 스프린트 데이터 애널리스트 트랙 미션 15의 일환으로, 가상의 서비스 "Fandom-K"를 기반으로 데이터베이스 설계와 구현 과정을 실습한 내용을 정리한 것입니다.

Fandom-K는 K-pop 아티스트와 팬을 연결하는 글로벌 조공 플랫폼입니다. 아직 서비스는 개발되지 않았으며, 와이어프레임(Wireframe)만 존재하는 상태에서, 데이터 인프라 설계를 담당하는 데이터 엔지니어의 역할을 맡아 데이터베이스를 설계하고 MySQL을 활용해 구현하는 과정을 진행했습니다. 🎵

---

0. 미션 개요

이번 미션은 데이터 모델링과 데이터베이스 구축 실습에 중점을 둡니다. 아래와 같은 단계를 통해 데이터베이스 설계와 구현을 체험했습니다.

1. ERD 다이어그램 설계

• 서비스 소개를 기반으로 필요한 테이블을 정의하고 관계(식별/비식별 관계, 카디널리티, PK, FK 등)를 설정합니다.
• 정규화를 고려하며 ERD(Entity-Relationship Diagram)를 작성합니다.

2. MySQL 데이터베이스 구현

• 설계한 ERD 다이어그램을 기반으로 MySQL에서 "FandomK" 데이터베이스를 생성합니다.
• DDL(Data Definition Language) 문을 작성하여 실제 데이터베이스 구조를 구현합니다.

3. ERD 다이어그램 검증

• MySQL Workbench를 활용해 구현된 데이터베이스의 ERD를 시각적으로 확인합니다.
• 설계한 다이어그램과 비교하여 구현의 정확성을 검증합니다.

이번 미션을 통해 데이터 모델링 전 과정을 이해하고, 실제 데이터베이스 설계 및 구현 역량을 키울 수 있었습니다.

---

1. ERD 다이어그램 설계

 

1-1. 테이블 구성을 위한 브레인스토밍 🧠

Fandom-K 서비스의 주요 기능과 데이터 흐름을 고려하여 테이블 구성을 위한 브레인스토밍을 진행했습니다.
초기 단계에서는 가능한 많은 아이디어를 도출한 뒤, 우선순위를 조정하거나 통합할 수 있도록 방향을 설정했습니다.

---

1. Users (유저 정보)

유저의 계정 정보 및 상태를 저장하는 기본 테이블.

2. Credits (크레딧 관리)

유저가 보유한 크레딧 및 충전/사용 내역 관리.

3. Artists (아티스트 정보)

아티스트의 이름, 소속사, 카테고리와 같은 정보를 저장.

4. Campaigns (후원 캠페인 정보)

팬들이 진행하는 후원 캠페인의 데이터 관리.

5. Donations (후원 기록)

팬들의 후원 활동 기록.

6. Votes (투표 기록)

팬들이 아티스트에게 투표한 데이터를 관리.

7. Followed_Artists (팔로우 정보)

유저가 팔로우한 아티스트 목록을 기록.

8. Notifications (알림 메시지)

팬들에게 전달되는 알림 메시지를 저장.

9. Transactions (충전 기록)

유저가 크레딧을 충전한 내역 관리.

10. Categories (아티스트 카테고리)

아티스트를 특정 카테고리로 분류.

11. Ranks (투표 순위)

아티스트의 순위 데이터를 관리.

12. Achievements (활동 달성 이력)

팬들이 달성한 활동 내역을 기록.

13. User_Logs (활동 로그)

유저가 서비스 내에서 수행한 활동 기록.

14. Admins (관리자 정보)

서비스 관리자의 정보를 저장하며, 권한 설정을 지원.

15. System_Logs (시스템 로그)

관리자의 작업 및 주요 시스템 이벤트 기록.

16. Service_Settings (서비스 설정)

크레딧 단가, 후원 정책 등 전반적인 서비스 설정값 관리.

17. Reports (신고 기록)

부적절한 콘텐츠나 활동을 신고한 내역을 저장.

18. User_Feedback (유저 피드백)

유저의 서비스 개선 요청이나 문제점 기록.

19. Banned_Users (차단된 유저)

차단된 유저와 차단 사유, 기간 등을 관리.

---

브레인스토밍 과정에서 고민한 사항

1. 크레딧의 활용 방식
   • 유저가 크레딧을 어떻게 충전하고 사용할지, 미션 수행이나 이벤트를 통한 제공이 가능하지 않을까?
2. 캠페인과 아티스트의 관계
   • 한 아티스트가 여러 캠페인에 참여하거나, 하나의 캠페인이 여러 아티스트와 연관될 가능성.

---

1-2. 테이블 및 컬럼 정의 📋

앞서 브레인스토밍에서 도출한 다양한 아이디어를 바탕으로 최종적으로 11개의 테이블을 구성하였습니다. 이 테이블들은 서비스의 핵심 기능, 사용자 경험 강화 그리고 서비스 운영의 3가지 영역을 효과적으로 지원할 수 있도록 설계되었습니다.

최종적으로 선택된 테이블은 아래와 같으며, 각 테이블 간의 관계를 명확히 정의하여 ERD(Entity-Relationship Diagram) 설계의 기반이 되었습니다.

---

최종 구성된 테이블 목록 및 영역

• 총 테이블 수: 11개
• 핵심 기능: Users, Credits, Artists, Campaigns, Donations, Votes
• 사용자 경험 강화: Followed_Artists, Notifications, Ranks
• 서비스 운영: Admins, System_Logs

---

최종 테이블 구성

1. Users (유저 정보)

• 컬럼:
  • user_id (PK): 유저 고유 ID.
  • username: 유저 이름.
  • email: 유저 이메일 주소.
  • password: 유저 비밀번호.
  • role: 유저 역할.
  • created_at: 계정 생성 날짜.

2. Credits (크레딧 관리)

• 컬럼:
  • credit_id (PK): 크레딧 관리 ID.
  • user_id (FK): Users 테이블과 연결.
  • total_credit: 보유한 총 크레딧.
  • updated_at: 마지막 크레딧 업데이트 날짜.

3. Artists (아티스트 정보)

• 컬럼:
  • artist_id (PK): 아티스트 고유 ID.
  • name: 아티스트 이름.
  • group_name: 그룹.
  • agency: 소속사.
  • created_at: 아티스트 등록 날짜.

4. Campaigns (후원 캠페인 정보)

• 컬럼:
  • campaign_id (PK): 캠페인 고유 ID.
  • artist_id (FK): Artists 테이블과 연결.
  • title: 캠페인 제목.
  • description: 캠페인 설명.
  • target_amount: 목표 금액.
  • current_amount: 현재 모금된 금액.
  • start_date: 캠페인 시작 날짜.
  • end_date: 캠페인 종료 날짜.
  • status: 캠페인 상태 (진행 중, 완료, 취소).

5. Donations (후원 기록)

• 컬럼:
  • donation_id (PK): 후원 기록 고유 ID.
  • user_id (FK): Users 테이블과 연결.
  • campaign_id (FK): Campaigns 테이블과 연결.
  • donation_amount: 후원 금액.
  • donation_date: 후원 날짜.

6. Votes (투표 기록)

• 컬럼:
  • vote_id (PK): 투표 고유 ID.
  • user_id (FK): Users 테이블과 연결.
  • artist_id (FK): Artists 테이블과 연결.
  • vote_count: 투표 횟수.
  • vote_date: 투표 날짜.

7. Followed_Artists (팔로우 정보)

• 컬럼:
  • follow_id (PK): 팔로우 고유 ID.
  • user_id (FK): Users 테이블과 연결.
  • artist_id (FK): Artists 테이블과 연결.
  • followed_at: 팔로우 시작 날짜.

8. Notifications (알림 메시지)

• 컬럼:
  • notification_id (PK): 알림 메시지 고유 ID.
  • user_id (FK): Users 테이블과 연결.
  • message: 알림 메시지 내용.
  • is_read: 읽음 여부.
  • created_at: 알림 생성 날짜.

9. Ranks (투표 순위)

• 컬럼:
  • rank_id (PK): 순위 고유 ID.
  • artist_id (FK): Artists 테이블과 연결.
  • month: 순위 기준 월.
  • ranking: 순위.
  • total_votes: 해당 월의 총 투표 수.

10. Admins (관리자 정보)

• 컬럼:
  • admin_id (PK): 관리자 고유 ID.
  • username: 관리자 이름.
  • email: 관리자 이메일.
  • password: 관리자 비밀번호.
  • role: 관리자 역할.
  • created_at: 관리자 계정 생성 날짜.

11. System_Logs (시스템 로그)

• 컬럼:
  • log_id (PK): 로그 고유 ID.
  • admin_id (FK): Admins 테이블과 연결.
  • action: 수행된 작업.
  • action_date: 작업 날짜.
  • details: 작업 세부 내용.

---

1-3. 테이블 간의 관계 정의

최종적으로 설계된 11개의 테이블은 아래와 같이 서로 연결됩니다.

각 테이블은 데이터의 흐름과 관계를 명확히 정의하여 서비스의 주요 기능을 제공할 수 있습니다.

---

Users (유저 정보)

• Credits (1:1): 유저는 하나의 크레딧 계정을 가짐.
• Admins (1:1): 유저는 관리자로 지정될 수 있음.
• Donations, Votes, Followed_Artists, Notifications (1): 유저는 다양한 활동 데이터를 생성.

Artists (아티스트 정보)

• Campaigns (1): 아티스트는 여러 캠페인을 가질 수 있음.
• Votes, Ranks, Followed_Artists (1): 아티스트는 팬들의 투표 및 팔로우 데이터와 연결.

Campaigns (후원 캠페인)

• Artists (N:1): 캠페인은 특정 아티스트와 연결.
• Donations, Notifications (1): 캠페인은 후원 기록과 알림 데이터와 연결.

Votes, Donations, Followed_Artists (활동 기록)

• Users, Artists (N:1): 각 활동은 특정 유저와 아티스트와 연결.

Admins & System_Logs (서비스 운영)

• Admins (1:1): 관리자는 유저와 연결.
• System_Logs (1): 관리자의 작업 내역을 기록.

---

1-4. 설계된 ERD 다이어그램 🗂️

아래는 앞서 브레인스토밍한 테이블과 테이블 간의 관계를 바탕으로 설계한 ERD(Entity-Relationship Diagram)입니다.

 

ERD 다이어그램 주요 포인트:

1. Users와 연결된 관계:
   • 유저 데이터를 중심으로 크레딧, 투표, 후원, 알림 등 주요 활동 데이터를 관리합니다.
2. Artists와 관련된 관계:
   • 아티스트는 캠페인, 투표, 순위 데이터와 연결되며, 팬들과의 주요 상호작용을 지원합니다.
3. 운영 관리 테이블:
   • Admins와 System_Logs 테이블은 서비스 운영 및 관리자 활동 데이터를 기록합니다.

---

2.MySQL 데이터베이스 구현

설계한 ERD를 기반으로 MySQL에서 Fandom-K 데이터베이스를 구현하였습니다. 데이터베이스는 서비스의 주요 기능을 효과적으로 지원하기 위해 총 11개의 테이블로 구성되었으며, 각 테이블은 DDL(Data Definition Language) 문을 사용해 정의했습니다.

 

-- 데이터베이스 생성
CREATE DATABASE FandomK;

-- 데이터베이스 사용
USE FandomK;

-- Users 테이블 생성
CREATE TABLE Users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('user', 'admin') DEFAULT 'user',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Credits 테이블 생성
CREATE TABLE Credits (
    credit_id INT AUTO_INCREMENT PRIMARY KEY, -- 고유 ID
    user_id INT, -- 유저 ID
    total_credit DECIMAL(10, 2) DEFAULT 0.00, -- 크레딧 총액
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 업데이트 시간
    FOREIGN KEY (user_id) REFERENCES Users(user_id) -- 유저와 연결
);

-- Artists 테이블 생성
CREATE TABLE Artists (
    artist_id INT AUTO_INCREMENT PRIMARY KEY, -- 아티스트 ID
    name VARCHAR(100), -- 아티스트 이름
    group_name VARCHAR(50), -- 그룹 이름
    agency VARCHAR(100), -- 소속사 이름
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP -- 등록 시간
);

-- Campaigns 테이블 생성
CREATE TABLE Campaigns (
    campaign_id INT AUTO_INCREMENT PRIMARY KEY, -- 캠페인 ID
    artist_id INT, -- 대상 아티스트 ID
    title VARCHAR(150), -- 캠페인 제목
    description TEXT, -- 캠페인 설명
    target_amount DECIMAL(10, 2), -- 목표 금액
    current_amount DECIMAL(10, 2) DEFAULT 0.00, -- 현재 모금액
    start_date DATE, -- 시작 날짜
    end_date DATE, -- 종료 날짜
    status ENUM('ongoing', 'completed', 'cancelled') DEFAULT 'ongoing', -- 상태
    FOREIGN KEY (artist_id) REFERENCES Artists(artist_id) -- 아티스트와 연결
);

-- Donations 테이블 생성
CREATE TABLE Donations (
    donation_id INT AUTO_INCREMENT PRIMARY KEY, -- 후원 ID
    user_id INT, -- 유저 ID
    campaign_id INT, -- 캠페인 ID
    donation_amount DECIMAL(10, 2), -- 후원 금액
    donation_date DATETIME DEFAULT CURRENT_TIMESTAMP, -- 후원 날짜
    FOREIGN KEY (user_id) REFERENCES Users(user_id), -- 유저와 연결
    FOREIGN KEY (campaign_id) REFERENCES Campaigns(campaign_id) -- 캠페인과 연결
);

-- Votes 테이블 생성
CREATE TABLE Votes (
    vote_id INT AUTO_INCREMENT PRIMARY KEY, -- 투표 ID
    user_id INT, -- 유저 ID
    artist_id INT, -- 아티스트 ID
    vote_count INT, -- 투표 횟수
    vote_date DATETIME DEFAULT CURRENT_TIMESTAMP, -- 투표 날짜
    FOREIGN KEY (user_id) REFERENCES Users(user_id), -- 유저와 연결
    FOREIGN KEY (artist_id) REFERENCES Artists(artist_id) -- 아티스트와 연결
);

-- Followed_Artists 테이블 생성
CREATE TABLE Followed_Artists (
    follow_id INT AUTO_INCREMENT PRIMARY KEY, -- 팔로우 ID
    user_id INT, -- 유저 ID
    artist_id INT, -- 아티스트 ID
    followed_at DATETIME DEFAULT CURRENT_TIMESTAMP, -- 팔로우 시간
    FOREIGN KEY (user_id) REFERENCES Users(user_id), -- 유저와 연결
    FOREIGN KEY (artist_id) REFERENCES Artists(artist_id) -- 아티스트와 연결
);

-- Notifications 테이블 생성
CREATE TABLE Notifications (
    notification_id INT AUTO_INCREMENT PRIMARY KEY, -- 알림 ID
    user_id INT, -- 유저 ID
    message TEXT, -- 알림 메시지
    is_read BOOLEAN DEFAULT FALSE, -- 읽었는지 여부
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP, -- 생성 시간
    FOREIGN KEY (user_id) REFERENCES Users(user_id) -- 유저와 연결
);

-- Ranks 테이블 생성
CREATE TABLE Ranks (
    rank_id INT AUTO_INCREMENT PRIMARY KEY, -- 순위 ID
    artist_id INT, -- 아티스트 ID
    month DATE NOT NULL, -- 월별 기준 날짜 (해당 월의 첫 날)
    ranking INT NOT NULL, -- 순위
    total_votes INT NOT NULL, -- 총 투표 수
    FOREIGN KEY (artist_id) REFERENCES Artists(artist_id) -- 아티스트와 연결
);

-- Admins 테이블 생성
CREATE TABLE Admins (
    admin_id INT AUTO_INCREMENT PRIMARY KEY, -- 관리자 ID
    user_id INT, -- 유저 ID
    username VARCHAR(50), -- 관리자 이름
    email VARCHAR(100) UNIQUE, -- 관리자 이메일
    password VARCHAR(255), -- 비밀번호
    role ENUM('super_admin', 'content_admin') DEFAULT 'content_admin', -- 역할
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP, -- 생성 시간
    FOREIGN KEY (user_id) REFERENCES Users(user_id) -- 유저와 연결
);

-- System_Logs 테이블 생성
CREATE TABLE System_Logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY, -- 로그 ID
    admin_id INT, -- 관리자 ID
    action VARCHAR(255), -- 작업 내용
    action_date DATETIME DEFAULT CURRENT_TIMESTAMP, -- 작업 날짜
    details TEXT, -- 작업 세부 내용
    FOREIGN KEY (admin_id) REFERENCES Admins(admin_id) -- 관리자와 연결
);

---

3. MySQL 데이터베이스 구현

---

4.설계된 ERD 다이어그램과 MySQL에서 구현한 데이터베이스와의 비교

비교 및 공통점

1. 구조의 유사성
   • 설계된 ERD와 MySQL에서 생성된 ERD는 모든 주요 테이블(Users, Credits, Artists 등)이 동일하게 구성되었습니다.
   • 각 테이블의 외래 키 관계(FK) 또한 정확히 일치하며, 데이터 무결성을 보장합니다.
2. 데이터 흐름
   • 두 다이어그램 모두 서비스의 주요 데이터 흐름(유저 → 후원/투표 → 캠페인/아티스트 → 관리자 로그)을 중심으로 설계되었습니다.
   • 외래 키 관계를 통해 각 테이블이 적절히 연결되어 있으며, 데이터의 흐름과 관리가 체계적입니다.
3. 테이블 간 관계 표현
   • 설계된 ERD와 구현된 ERD 모두 1관계를 명확히 표현하였고, Users, Artists, Admins가 중심에 배치되어 전체적인 데이터 연결을 이끌고 있습니다.

---

특징 및 차이점

1. 시각적 레이아웃
   • 설계된 ERD에서는 테이블 간 연결이 더 직관적으로 배치되었으며, 관계를 명확히 보여주기 위한 레이아웃이 적용되었습니다.
   • MySQL Workbench의 ERD는 자동 배치된 형태로, 레이아웃이 설계와 약간 다르지만 관계와 데이터 흐름은 동일합니다.
2. 데이터 타입 및 제약 조건
   • MySQL ERD는 구현된 데이터 타입(예: DECIMAL, VARCHAR)과 제약 조건(예: UNIQUE, DEFAULT)이 명시적으로 포함되어 있어 실제 데이터베이스 환경에서의 제약을 더 잘 반영합니다.
3. 세부 필드 정보
   • MySQL ERD에서는 각 컬럼의 속성(NOT NULL, AUTO_INCREMENT 등)이 명확히 드러나며, 이를 통해 설계된 테이블이 올바르게 구현되었음을 확인할 수 있습니다.

---

이번 구현을 통해 설계 단계에서 작성한 ERD 다이어그램이 MySQL에서 올바르게 반영되었음을 확인할 수 있었습니다.
테이블 구조와 관계가 정확히 구현되었으며, 서비스의 주요 기능을 지원하기 위한 데이터베이스로서 준비가 완료되었습니다. 🚀