지난 API 분석 글에서 작성했던 내용들을 바탕으로 DB를 설계해보았다.

UML 다이어그램 툴

예전에 UML을 그릴 때 자주 사용했던 툴로는 StarUML이나 draw.io(현재는 app.diagrams.net)을 사용했었는데, 둘 모두 GUI 기반이라 그런지 사용법은 쉬웠지만 수정할 내용이 많을 때는 번거로운 경우가 있었다. 그러다 코드 기반으로 UML을 그릴 수 있는 온라인 툴을 알게 되었다.

dbdiagram.io

팀원 공유, 서버 백업, sql 및 이미지 파일로 import/export 등 필요한 기능들은 웬만하면 다 있고 사용 후 결과물의 비주얼도 맘에 들어 돌고돌아 결국 이 툴을 사용하게 되었다. 아쉬운 점이라면 테이블 위치 자동정렬 기능이 없느니만도 못하고 테이블 간 간격 및 위치를 미세하게 조정하는 점이 좀 불편하다는 것인데, 핵심적인건 아니라 괜찮은 것 같다.

초기 모델링

사실 프로젝트 시작하면서 대략적으로 만들어두긴 했었지만 막상 본격적으로 조사 및 설계를 해보니 맞지 않는 부분이 많아 달라진 부분이 많다. 당시에는 소셜 로그인에 대해서 염두해두지 않았었고, 랭킹 기능도 적용되어 있지 않은 상태였다.

현재 모델링

막상 개발하다보면 생각하지 못했던 부분들이 나오고 놓친 것이 있을 수 있기 때문에 감히 최종이라는 말은 못 쓰겠지만, 나름 깔끔하게 설계한 것 같다. 생각했던 것보다 Contents(영화, TV 프로그램 등의 정보) 부분의 덩치가 커져서 이 부분은 나중에 칼질이 들어갈 수도 있을 것 같다.

UML

UML 코드

위에서 언급했듯이 내가 UML을 그릴 때 사용했던 툴은 코드로 작성하고, 후에 테이블 위치들만 GUI에서 조정해주었다. 맨 위부터 테이블들의 상세 스펙을, 아래쪽에는 테이블 간의 관계를 명시해주었다.

Table User {
  user_id integer [primary key]

  email varchar
  user_nickname varchar
  gender varchar
  age_range integer
  profile_image varchar
  role varchar
  auth_provider varchar
  social_id varchar
  refresh_token varchar
}

/*
 * Movie / TV Program 상위 테이블
 * ===============================================
 */
Table Contents {
  contents_id integer [primary key]
  contents_type integer [primary key]

  translated_title varchar
  origianl_title varchar
  origianl_language varchar
  tagline varchar
  overview varchar
  poster_path varchar
  production_countries varchar
  certifications varchar [note: "시청 등급"]

  review_cnt integer [note: "작품 리뷰 수"]
  article_cnt integer [note: "작품 게시글 수"]
  imad_score float [note: "IMAD 자체 평점"]
}

/*
 * Movie 관련 테이블
 * ===============================================
 */
Table MovieData {
  movie_id integer [primary key]

  release_date date [note: "국내 개봉일 기준"]
  status integer [note: "개봉 여부"]
  runtime integer
}

/*
 * TV Program 관련 테이블
 * ===============================================
 */
Table TvProgramData {
  tv_program_id integer [primary key]

  first_air_date date
  last_air_date date
  number_of_episodes integer
  number_of_seasons integer
}

Table Networks [note: "방송사, OTT 등"] { 
  networks_id integer [primary key]

  networks_name varchar
  logo_path varchar
  origin_country varchar
}

Table Broadcaster {
  tv_program_id integer [primary key]
  networks_id integer [primary key]
}

Table Season {
  season_id integer [primary key]

  name varchar
  air_date date
  episode_count integer
  overview varchar
  poster_path varchar
  season_number integer
}

Table SeasonCollection {
  tv_program_id integer [primary key]
  season_id integer [primary key]
}


/*
 * 배우 및 스태프 관련 테이블
 * ===============================================
 */
 Table Crew {
  crew_id integer [primary key]

  original_name varchar
  translated_name varchar
  gender integer
  known_for_department varchar [note: "유명분야"]
  profile_path varchar
 }

 Table Actor {
  actor_id integer [primary key]

  original_name varchar
  translated_name varchar
  gender integer
  known_for_department varchar [note: "유명분야"]
  profile_path varchar
 }

 Table ContentsCrew {
  contents_id integer [primary key]
  contents_type integer [primary key]
  crew_id integer [primary key]
  
  department varchar [note: "Ex) Writing"]
  job varchar [note: "Ex) Story"]
  character varchar [note: "스태프가 연기도 겸한 경우"]
 }

 Table ContentsActor {
  contents_id integer [primary key]
  contents_type integer [primary key]
  actor_id integer [primary key]

  character varchar [note: "배역명"]
 }


/*
 * 장르 관련 테이블
 * ===============================================
 */
Table Genre {
  genre_id integer [primary key]

  name varchar
}

Table UserPreferredGenre {
  genre_id integer [primary key]
  user_id integer [primary key]

  rate integer [note: "장르 선호점수"]
}

Table ContentsGenre {
  genre_id integer [primary key]
  contents_id integer [primary key]
  contents_type integer [primary key]
}


/*
 * 리뷰 관련 테이블
 * ===============================================
 */
Table Review {
  review_id integer [primary key]

  user_id integer
  contents_id integer
  contents_type integer

  title varchar
  content varchar
  rate float [note: "작품 평점"]
  is_spoiler boolean

  like integer
  dislike integer
  created_at datetime
  modified_at datetime
}


/*
 * 게시글 관련 테이블
 * ===============================================
 */
Table Article {
  article_id integer [primary key]

  user_id integer
  contents_id integer
  contents_type integer

  category integer
  title varchar
  content varchar
  is_spoiler boolean

  view_cnt integer
  comment_cnt integer
  like integer
  dislike integer
  created_at datetime
  modified_at datetime
}

Table Comment {
  comment_id integer [primary key]
  
  user_id integer
  article_id integer

  content text
  comment_order integer
  created_at datetime
  modified_at datetime
}


/*
 * 리뷰 및 게시글 추천/비추천 관련 테이블
 * ===============================================
 * 추천이면 +1, 비추천이면 -1, 아무 상태도 아니면 0이므로 해당 데이터 삭제
 */
Table ReviewLike {
  user_id integer [primary key]
  review_id integer [primary key]

  like_status integer [note: "추천이면 +1, 비추천이면 -1, 아무 상태도 아니면 해당 데이터 삭제"]
}

Table ArticleLike {
  user_id integer [primary key]
  article_id integer [primary key]

  like_status integer [note: "추천이면 +1, 비추천이면 -1, 아무 상태도 아니면 해당 데이터 삭제"]
}


/*
 * 북마크 관련 테이블
 * ===============================================
 * 추천이면 +1, 비추천이면 -1, 아무 상태도 아니면 0이므로 해당 데이터 삭제
 */
 Table ArticleBookmark {
  user_id integer [primary key]
  article_id integer [primary key]

  bookmark_status integer
 }

 Table ContentsBookmark {
  user_id integer [primary key]
  contents_id integer [primary key]

  bookmark_status integer
 }


/*
 * 랭킹 관련 테이블
 * ===============================================
 */
Table AllTimeScore {
  contents_id integer [primary key]
  contents_type integer [primary key]

  score integer
}

Table AllTimeRanking {
  contents_id integer [primary key]
  contents_type integer [primary key]

  rank integer
  rank_changed integer
}

Table WeeklyScore {
  contents_id integer [primary key]
  contents_type integer [primary key]

  score integer
}

Table WeeklyRanking {
  contents_id integer [primary key]
  contents_type integer [primary key]

  rank integer
  rank_changed integer
}



/*
 * 참조 관계
 * ===============================================
 */
// Contents : Movie / TV Program
Ref: Contents.contents_id < MovieData.movie_id

Ref: Contents.contents_id < TvProgramData.tv_program_id
Ref: TvProgramData.tv_program_id < Broadcaster.tv_program_id
Ref: Networks.networks_id < Broadcaster.networks_id

Ref: TvProgramData.tv_program_id < SeasonCollection.tv_program_id
Ref: Season.season_id < SeasonCollection.season_id


// Contents : Crew / Actor
Ref: Contents.contents_id < ContentsCrew.contents_id
Ref: Contents.contents_type < ContentsCrew.contents_type
Ref: Crew.crew_id < ContentsCrew.crew_id

Ref: Contents.contents_id < ContentsActor.contents_id
Ref: Contents.contents_type < ContentsActor.contents_type
Ref: Actor.actor_id < ContentsActor.actor_id


// Genre : User / Contents
Ref: Genre.genre_id < UserPreferredGenre.genre_id
Ref: User.user_id < UserPreferredGenre.user_id

Ref: Genre.genre_id < ContentsGenre.genre_id
Ref: Contents.contents_id < ContentsGenre.contents_id
Ref: Contents.contents_type < ContentsGenre.contents_type


// Review : User / Contents
Ref: User.user_id < Review.user_id
Ref: Contents.contents_id < Review.contents_id
Ref: Contents.contents_type < Review.contents_type


// Article : Comment
Ref: User.user_id < Article.user_id
Ref: Contents.contents_id < Article.contents_id
Ref: Contents.contents_type < Article.contents_type

Ref: User.user_id < Comment.user_id
Ref: Article.article_id < Comment.article_id


// Contetns : Score / Ranking
Ref: Contents.contents_id < AllTimeScore.contents_id
Ref: Contents.contents_type < AllTimeScore.contents_type

Ref: Contents.contents_id < AllTimeRanking.contents_id
Ref: Contents.contents_type < AllTimeRanking.contents_type

Ref: Contents.contents_id < WeeklyScore.contents_id
Ref: Contents.contents_type < WeeklyScore.contents_type

Ref: Contents.contents_id < WeeklyRanking.contents_id
Ref: Contents.contents_type < WeeklyRanking.contents_type


// Like : Review / Article
Ref: User.user_id < ReviewLike.user_id
Ref: Review.review_id < ReviewLike.review_id

Ref: User.user_id < ArticleLike.user_id
Ref: Article.article_id < ArticleLike.article_id


// Bookmark : Aritlce / Contents / User
Ref: User.user_id < ArticleBookmark.user_id
Ref: Article.article_id < ArticleBookmark.article_id

Ref: User.user_id < ContentsBookmark.user_id
Ref: Contents.contents_id < ContentsBookmark.contents_id

User

사실 이 부분은 설계보다 구현이 먼저 되어있던 파트다. 클라이언트 측 테스트를 위해서 본격적인 설계 및 구현 전에 선행적으로 수행되었어야 했던 부분이다 보니 이렇게 된 것 같다. 그렇지만 결과적으로는 많은 시행착오를 겪으면서 초기 구상했던 내용과 많이 달라져서, 설계를 먼저 했다고 해도 많이 달려졌을 것 같긴하다...

어쨌든간에 User 테이블은 말 그대로 회원정보를 담고 있으며 이메일, 닉네임, 비밀번호, 성별, 연령대 등 기본적인 정보들을 가지고 있다. 그 외에 눈에 띄는 칼럼들이 있는데, 각 역할은 다음과 같다.

• Role
  • 회원가입에서 이메일/비밀번호 정보를 입력하고 최초 로그인 시, 성별, 연령대, 선호 장르 등 추가 정보를 입력해야 한다.
  • 이 때 추가 정보를 입력해야만 서비스를 이용할 수 있도록 설계해두었기 때문에 이를 구분할 수 있는 플래그 값이다. (만약 정보 제공을 원하지 않는다면 건너뛰기를 선택할 수 있다!)
  • 프로그램 내부에서는 GUEST / USER로 구분한다.
• Auth Provider / Social Id
  • 네이버, 카카오, 구글 등 소셜 로그인 시 정보를 제공해주는 업체(provider)에 대한 정보이다.
  • 내부적으로 NAVER, KAKAO, GOOGLE, IMAD(자체 회원가입) 등의 값이 들어간다.
• Refresh Token
  • IMAD는 네이티브 모바일 어플리케이션 서비스이기 때문에 사용자 인증 방식으로 세션/쿠키가 아니라 JWT를 사용한다.
  • JWT의 access token이 만료되었을 때 새로운 유효한 토큰을 발급받기 위해서 refresh 토큰을 사용한다. 이 값을 DB에 저장해두었다가 유저가 요청했을 때 해당 값과 비교하여 처리해준다.

Contents

설계할 때 가장 많이 고민했고, 덩치가 제일 커진 파트이다. TMDB에서는 컨텐츠(여기서는 TV, 드라마, 다큐, 애니메이션 등 모든 영상 작품들을 컨텐츠라고 칭하겠다)를 크게 Movie와 TV Program으로 분류한다.

IMAD의 DB 스키마 관점에서 봤을 때 이 둘은 동일한 계층에 있기에 하나의 테이블에 담고 싶었다. 그렇지만 서로 중복되지 않는 고유한 데이터의 종류가 많다보니 하나의 테이블에서 관리하려면 비어있는 칼럼이 많게 되었다. 그래서 공통된 부분만 묶어서 Contents에 정보를 담고, 나머지 고유한 것들은 따로 분리하게 되었다.

프로젝트의 규모와 예상 사용자 수에 비하면 호들갑을 떤 느낌도 없지 않아 있지만 추후 확장성이나 데이터 관리 측면에서 봤을 때는 좋을 것 같다.

참고로 Movie와 TV Program은 ID 값이 겹치는 경우가 있어 기본키로 contents_id와 contents_type을 함께 지정해주었다.

Actor / Crew

배우와 스태프(감독, 작가, 연출, PD 등)과 관련된 정보들을 담고 있다. Contents와 Actor/Crew는 M:N 관계를 가지기 때문에 중간에 연결 테이블을 만들어줘서 1:N 관계로 풀어주었다. JPA 단에서 다대다 관계를 매핑할 수 있기는 하지만 중간에 연결 테이블이 단순 연결 뿐만 아니라 다른 값이 추가로 들어갈 수 있기 때문에 연결 테이블을 엔티티로 승격시켜주는게 좋다. 자세한 내용은 아래 링크를 참고하자.

[JPA] @ManyToMany, 다대다[N:M] 관계

character는 배역명을 뜻하며, 한 사람이 배우/감독/작가 등을 다 소화하는 경우가 있어 ContentsCrew 테이블에도 넣게 되었다. ContentsCrew에서 department는 "Writing" 같은 정보를 담고 있는 컬럼이고 job은 "Writing" 부서 내의 "Story" 역할 같은 값을 가진다.

Genre

한 작품에는 장르가 여러 개이고, 유저가 선호하는 장르 또한 두 개 이상일 수 있기 때문에 다대가 관계를 가정했다. 그래서 각각 연결테이블을 만들어주었는데, UserPreferredGenere의 경우 추후 선호 장르를 기반으로 작품을 추천해주기 위해 rate 칼럼을 넣어주었다. 이 rate는 작품 찜이나 게시글, 작품 추천 등을 합산할까 생각 중이다.

Review / Article / Comment + Like, Bookmark

IMAD에서 작성할 수 있는 글은 리뷰와 게시글 두 가지가 있다. 리뷰는 작품에 대한 감상을 적고 평점을 매길 수 있다. 게시글은 작품의 질문, 토론 등 원하는 주제로 자유롭게 글을 작성할 수 있으며 댓글이 달릴 수 있다. 둘 모두 추천 / 비추천을 받을 수 있고 spoiler 플래그가 true이면 preview에서 모자이크 처리되어 보여진다.

Like와 Bookmark 정보 모두 위에서처럼 다대다 관계를 일대다 관계로 풀어내기 위해 연결 테이블을 두었다.

마치며

도메인 / DB / 유즈 케이스 등의 과정을 분리해서 단계별로 수행해나가고 싶었다. 하지만 막상 팀 프로젝트를 진행하다보니 이전 과정으로 되돌아가 내용을 갈아엎고 작성해두었던 문서가 충돌하는 등 여러 문제가 있어서 결국엔 한 번에 뭉뜽그려서 하게 되었다. 지금이야 규모가 그렇게 크지 않으니 크게 문제가 와닿지는 않았지만 팀과 프로젝트의 덩치가 커지면 분명 이런 방식에는 문제가 사방에서 터질 것이다. 개인 수준에서는 그런 경험을 하기 쉽지 않아서 아쉽지만 지금이라도 조금씩 실천해 나가보자. 취업도 얼른 하고...