Fandom-K 데이터베이스 설계

본 미션은 가상의 서비스 "Fandom-K"의 와이어프레임을 바탕으로 진행되었습니다.

Fandom-K은 K-pop 아티스트와 팬을 이어주는 글로벌 조공 플랫폼입니다. 아직 서비스의 와이어프레임만 있을 뿐, 아직 개발을 시작하진 않았다고 가정해볼게요. 여러분은 Fandom-K의 데이터 인프라를 담당하는 데이터 엔지니어로써 해당 서비스의 DB를 구축해야합니다.
하단의 서비스 소개를 참고하여 서비스 전체의 ERD 다이어그램을 그려봅시다. 어떤 테이블이 필요할까요? 테이블간의 관계(식별/비식별 관계, 카디널리티, 컬럼의 PK와 FK 등)와 테이블 정규화를 고려하면서 작성해주세요.작성한 ERD 다이어그램을 기반으로 직접 MySQL에 구현해봅시다. "FandomK"라는 데이터베이스를 하나 생성하고, DDL 문 작성을 통해 실제 DB 환경을 구현해보세요. (테이블 내 데이터는 따로 구현하지 않아도 좋습니다.)MySQL Workbench (혹은 사용하고 있는 DB Tool)에서 구현한 데이터베이스의 ERD를 확인할 수 있어요. 1번에서 그린 ERD와 비교하며 구현이 제대로 되었는지 확인해보세요.

 

1. ERD 그리기

1) user

• Fandom-K에 가입한 유저들의 정보를 저장
• primary key: user_id
• 포함된 정보: 가입일, 생일, 성별, 직업 등
• 관계
  • 1:N 관계 >> credit_charge, favorite, jogong_log, chart_log

2) credit_charge

• 유저들의 크레딧 충전 정보를 저장
• primary key: charge_id
• 포함된 정보: 충전일자, 충전금액, 충전방식, 상태(대기, 취소, 승인 등), user_id(foreign key)
• 관계
  • N:1 관계 >> user

3) artist

• Fandom-K에 등록된 아이돌 멤버에 대한 정보 저장
• primary key: artist_id
• 포함된 정보: 이름, 그룹_id, 그룹명, 성별, 유형(솔로, 그룹 등), 사진
• 관계
  • 1 :N 관계 >> favorite, jogong, chart
  • 1:N 관계 >> jogong_log
  • N:1 관계 >> artist

4) favorite

• 유저가 '관심 있는 아이돌'에 추가한 정보 저장
• primary key: favor_id
• 포함된 정보: 등록일자, artist_id(foreing key), user_id(foreign key)
• 관계
  • N:1 관계 >> user, artist

5) jogong

• Fandom-K에 등록된 조공 서비스에 대한 정보 저장
• primary key: j_id
• 포함된 정보: 생성일, 목표 금액, 마감일자, 조공이미지, artist_id(foreign key)
• 관계
  • 1:N 관계 >> jogong_log
  • N:1 관계 >> artist

6) jogong_log

• 유저들이 조공에 참여한 기록 저장
• primary key: id
• 포함된 정보: 조공일, 조공금액, platform_id(foreign key), user_id (foreign key) , j_id(foreign key)
• 관계
  
  • N:1 관계 >> jogong, user_id, platform_id

 

7) chart

• Fandom-K에 등록된 '투표'에 대한 정보 저장
• primary key: chart_id
• 포함된 정보: 시작일, 마감일, 득표수, 차트 유형(월간 투표, 일간 투표 등), artist_id(foreign key)
• 관계
  • 1:N 관계 >> chart_log
  • N:1 관계 >> artist_id

8) chart_log

• 유저들이 투표에 참여한 기록 저장
• primary key: id
• 포함된 정보: 투표일, platform_id(foreign key), user_id (foreign key) , artist_id(foreign key), chart_id(foreign key)
• 관계
  
  • N:1 관계 >> chart_id, user_id, artist_id, platform_id

9) platform

• 유저들이 접속한 기기에 대한 정보 저장
• primary key: platform_id(PC, mobile, tablet 등)
• 관계
  • 1:N 관계 >> jogong_log, chart_log

 

2. DDL 작성

-- 빅쿼리로 작성했습니다.
-- 데이터셋 생성
CREATE SCHEMA IF NOT EXISTS `da-first-445105.Fandom_K`
OPTIONS(
  location="US"  -- 지역 설정 (예: US, ASIA 등)
);

--테이블 생성
CREATE TABLE Fandom_K.user(
  user_id STRING NOT NULL,
  start_date DATETIME NOT NULL,
  birthday DATETIME,
  gender STRING NOT NULL,
  job STRING NOT NULL);

CREATE TABLE Fandom_K.artist(
  artist_id STRING NOT NULL,
  artist_name STRING NOT NULL,
  group_id STRING,
  group_name STRING,
  enroll_date DATETIME NOT NULL,
  artist_type STRING NOT NULL,
  gender STRING NOT NULL,
  photo STRING
);

CREATE TABLE Fandom_K.favorite(
  favor_id STRING NOT NULL,
  favor_date DATETIME,
  user_id STRING,
  artist_id STRING
);

CREATE TABLE Fandom_K.credit_charge(
  charge_id STRING NOT NULL,
  user_id STRING,
  charge_date DATETIME,
  charge_amount INT NOT NULL,
  charge_method STRING NOT NULL,
  status STRING NOT NULL
);

CREATE TABLE Fandom_K.platform(
  platform_id STRING NOT NULL
);

CREATE TABLE Fandom_K.jogong(
  j_id STRING NOT NULL,
  start_date DATETIME NOT NULL,
  end_date DATETIME NOT NULL,
  goal_amount INT NOT NULL,
  image STRING NOT NULL,
  artist_id STRING
);

CREATE TABLE Fandom_K.jogong_log(
  id STRING NOT NULL,
  donation_date DATETIME NOT NULL,
  credit_amount INT NOT NULL,
  j_id STRING,
  user_id STRING,
  platform_id STRING
);

CREATE TABLE Fandom_K.chart(
  chart_id STRING NOT NULL,
  start_date DATETIME NOT NULL,
  end_date DATETIME NOT NULL,
  chart_type STRING NOT NULL,
  artist_id STRING,
  vote_count INT NOT NULL
);

CREATE TABLE Fandom_K.chart_log(
  id STRING NOT NULL,
  chart_id STRING,
  artist_id STRING,
  platform_id STRING,
  user_id STRING,
  vote_date DATETIME NOT NULL
);

 

3. Bigquery에서 스키마 확인하기