설계

[설계] Okky ERD 분석하기

mint* 2024. 4. 26. 21:00
728x90

Overview

Okky

2000년에 만들어진 국내 최대 개발자 지식공유 플랫폼이다.
erdcloud 사이트에 Okky ERD가 공유되어 있어 학습 목적으로 분석해보려고 한다.

Okky ERD


https://www.erdcloud.com/d/PK2Ae7d4asTRqHpHx

 

OKKY

okky erd

www.erdcloud.com

 
그 전에 왜 존재하는 erd를 분석하게 되었는지 설명해보고자 한다.
 

ERD 분석 이유

ERD는 Entity Relationship Diagram의 약자로 Entity들의 관계를 나타낸 도표이다.
설계된 ERD 분석을 통해 데이터베이스 설계와 시스템 아키텍처에 대한 이해도를 높일 수 있다.
 
잘 설계된 ERD는 개발 과정에서 큰 도움이 되는데, 데이터베이스 구조를 명확하게 이해할 수 있어 개발의 효율성이 올라간다.
또한 성능 최적화를 위한 인덱스, 정규화, 쿼리 최적화 등의 기법을 적용할 수 있다.
 
ERD 분석은 설계가 구현이 바탕이 되기 때문에 중요하다고 생각한다.
유명한 커뮤니티 사이트인 Okky 사이트의 ERD를 분석하여 데이터베이스 설계와 시스템 아키텍처에 대한 이해도를 높이고자 시작하였다.
+ 프로젝트 ERD 설계전 공부겸 !

해당 ERD에서는 대부분의 테이블이 대리키를 사용하였다. 대리키를 사용하면 자연키 변경 문제에서 자유롭다.

 

사용되는 컬럼 타입

해당 ERD에서 컬럼 타입은 주로 6가지를 사용한다.

  • varchar(255) : 중간 길이 문자열 저장
    • 이메일 주소, url, 파일 경로
  • varchar(20) : 짧은 길이 문자열
    • 사용자 이름, 닉네임
  • bigint : 큰 정수값, 기본키나 대리키
  • int : 작은(일반적인) 정수 값
  • bit : Boolean 값, true/false
    • int 타입을 사용하지 않고 bit를 사용하면 저장 공간을 더 효율적으로 사용할 수 있다.
  • datetime : 날짜 + 시간 정보 ex) YYYY-MM-DD HH:MM:SS

 

사용자 테이블

    • 크게 user, avatar, person으로 분리하여 수직 파티셔닝을 수행한다.
    • 사용자 인증시에는 user 테이블만 액세스 하면 된다.
    • 프로필 이미지 등 변경이 잦은 경우 avatar 테이블만 업데이트하면 되므로 효율적이다.
avatar 테이블은 사용자의 프로필을 저장하므로 업데이트가 자주 발생한다.
반면에 user 테이블은 사용자의 기본 정보를 저장하므로 업데이트 빈도가 낮다.

 

user : 사용자 정보 + 인증 정보

  • id (bigint) : 사용자의 고유 식별자로 사용되는 대리 키
  • FK
    • avatar_id(bigint) : 사용자의 프로필 정보 참조
    • person_id(bigint) : 사용자의 개인정보 참조
    • version(bigint) : Optimistic Locking을 위한 버전 정보
      데이터의 동시 수정을 제어하기 위해 레코드의 버전을 저장한다.
  • flag
    • account_expired(bit) : 계정 만료 여부
    • account_locked(bit) : 계정 잠금 여부
    • enabled (bit) : 계정 활성화 여부
    • withdraw(bit) : 계정 탈퇴 여부
  • ip
    • create_ip(varchar) : 계정 생성시 사용된 ip를 저장하여 보안/감사에서 사용
    • last_update_ip(varchar) : 마지막 계정정보 업데이트시 사용된 IP 주소
  • date
    • date_created(datetime) : 사용자 계정 생성 일시
    • date_withdraw(datetime) : 사용자 계정 탈퇴 일시
    • last_pasword_changed (datetime): 마지막으로 비밀번호 변경된 일시
  • password
    • password(varchar) : 비밀번호
    • password_expired(bit) : 비밀번호 만료 여부
    • username(varchar) : 사용자 로그인 id

 

avatar : 사용자 프로필 정보

  • id (bigint): 아바타의 고유 식별자로 사용되는 대리 키
  • version (bigint): 낙관적 잠금을 위한 버전 정보를 저장
  • profile
    • activity_point (int): 사용자의 활동 점수
    • nickname (varchar): 사용자의 닉네임
  • flag
    • official (bit): 공식 계정 여부를 나타내는 플래그
  • picture
    • picture (varchar): 사용자의 프로필 사진 경로
    • picture_type (int): 프로필 사진의 타입

 

follow : 사용자 팔로우, avatar 간 다대다 조인 테이블

  • follower_id (bigint): 팔로우하는 사용자의 아바타 ID를 나타내는 외래 키
  • following_id (bigint): 팔로우되는 사용자의 아바타 ID를 나타내는 외래 키

 

avatar_tag : 사용자 프로필 태그 다대다 조인 테이블

  • avatar_tags_id (bigint): 아바타 태그의 ID
  • tag_id (bigint): 태그의 ID

 

person : 사용자 개인정보

  • id (bigint): 개인 정보의 고유 식별자로 사용되는 대리 키
  • version (bigint): 낙관적 잠금을 위한 버전 정보 저장
  • FK
    • company_id (bigint): 사용자가 속한 회사의 id를 참조하는 외래 키
    • resume_id (bigint): 사용자의 이력서 정보를 참조하는 외래 키
  • date
    • date_created (datetime): 개인 정보가 생성된 일시를 저장
    • last_updated (datetime): 마지막으로 개인 정보가 업데이트된 일시를 저장
  • flag
    • dm_allowed (bit): 다이렉트 메시지 수신 허용 여부를 나타내는 플래그
  • info
    • email (varchar): 사용자의 이메일 주소를 저장
    • full_name (varchar): 사용자의 실명을 저장
    • homepage_url (varchar): 사용자의 홈페이지 URL을 저장

 

role : 사용자 역할

  • id (bigint): 역할의 고유 식별자로 사용되는 대리 키
  • version (bigint): 낙관적 잠금을 위한 버전 정보 저장
  • authority (varchar): 역할의 권한을 나타내는 문자열을 저장 ex) "ROLE_ADMIN", "ROLE_USER"

 

user_role : 사용자 - 역할 다대다 조인 테이블

  • 복합 기본키 (user_id, role_id ) 사용

 

logged_in : 로그인 정보

  • id (bigint): 로그인 정보의 고유 식별자로 사용되는 대리 키
  • FK
    • user_id (bigint): user 테이블의 id를 참조하는 외래 키, 로그인한 사용자
  • version (bigint): 낙관적 잠금을 위한 버전 정보 저장
  • date_created (datetime): 로그인한 일시
  • remote_addr (varchar): 로그인 시 사용된 IP 주소 저장

 

oauth_id : OAuth 정보

  • id (bigint): OAuth 정보의 고유 식별자로 사용되는 대리 키
  • FK
    • user_id (bigint): user 테이블의 id를 참조하는 외래 키, OAuth로 인증한 사용자
  • version (bigint): 낙관적 잠금을 위한 버전 정보 저장
  • access_token (varchar): OAuth 인증 후 발급받은 액세스 토큰
  • provider (varchar): OAuth 제공자의 이름을 저장 ex) "google", "facebook"

 

managed_user : 관리자 정보

  • id (bigint): 관리자 정보의 고유 식별자로 사용되는 대리 키
  • FK
    • user_id (bigint): user 테이블의 id를 참조하는 외래 키, 관리자 유저
  • version (bigint): 낙관적 잠금을 위한 버전 정보 저장

 

confirm_email : 이메일 인증 정보

  • id (bigint): 이메일 인증 정보의 고유 식별자로 사용되는 대리 키
  • FK
    • user_id (bigint): user 테이블의 id를 참조하는 외래 키로, 이메일 인증 대상 사용자
  • version (bigint): 낙관적 잠금을 위한 버전 정보
  • date_expired (datetime): 이메일 인증 링크의 만료 일시
  • email (varchar): 인증 대상 이메일 주소
  • secured_key (varchar): 이메일 인증 시 사용되는 보안 키

 

테이블 관계

  • user는 avatar와 person 테이블과 일대일 관계
  • user와 role은 다대다 관계
  • user는 logged_in, oauth_id, managed_user, confirm_email과 일대다 관계
  • avatar은 주로 다른 도메인(notification, article)등과 관계를 맺는다.
    • 사용자의 프로필이 다양한 활동과 연결되어 있기 때문에 avatar 테이블 중심으로 관계를 설정한다.

 

인사이트

  • 수직 파티셔닝
    • 사용자 테이블을 user, avatar, person 테이블로 분리하여 데이터 접근시 효율성이 향상된다.
  • 사용자 역할(role) 권한 분리
    • user_role 테이블은 사용자와 역할 간에 다대다 관계를 가진다. 유저는 여러 역할을 가질 수 있다.
  • 로그인
    • logged_in은 로그인 기록을 저장하고, oauthid 테이블은 외부 인증 정보를 저장하며 confirm_email은 이메일 인증 정보를 저장한다.
  • 관리자
    • managed_user 테이블을 따로 생성하여 추적할 수 있다.

 

게시글 관련 테이블

article : 게시글 메타 정보

 

  • id (bigint): 게시글의 고유 식별자
  • FK
    • category_id (varchar): 게시글이 속한 카테고리의 ID,category 테이블의 code를 참조하는 외래 키
    • author_id (bigint): 게시글 작성자의 ID, user 테이블의 id를 참조하는 외래 키
    • content_id (bigint): 게시글 내용의 ID, content 테이블의 id를 참조하는 외래 키
    • last_editor_id (bigint): 게시글을 마지막으로 수정한 사용자의 ID
    • selected_note_id (bigint): 채택된 댓글의 ID
  • version (bigint): 낙관적 잠금을 위한 버전 정보 저장
  • user
    • a_nick_name (varchar): 게시글 작성자의 익명 닉네임
    • create_ip (varchar): 게시글 작성 시 사용된 IP 주소
  • flag
    • anonymity (bit): 게시글의 익명성 여부
    • choice (bit): 게시글의 선택 여부
    • enabled (bit): 게시글의 활성화 여부
    • is_recruit (bit): 게시글이 채용 공고인지 여부
  • datetime
    • date_created (datetime): 게시글 작성일시
    • last_updated (datetime): 게시글 최종 수정일시
  • article_info
    • note_count (int): 게시글에 달린 댓글 수
    • scrap_count (int): 게시글의 스크랩 수
    • tag_string (varchar): 게시글에 붙은 태그 문자열
    • title (varchar): 게시글의 제목
    • view_count (int): 게시글 조회수
    • vote_count (int): 게시글 추천 수

 

category : 카테고리 정보

  • PK
    • code (varchar): 카테고리의 고유 코드
  • FK
    • parent_id (varchar): 상위 카테고리의 ID
  • version (bigint): 카테고리의 버전 정보
  • flag
    • anonymity (bit): 카테고리의 익명성 허용 여부
    • enabled (bit): 카테고리의 활성화 여부
    • last_updated (datetime): 카테고리 최종 수정일시
    • isurl (bit): 카테고리가 URL 형식인지 여부
    • require_tag (bit): 카테고리에서 태그 필수 여부
    • use_evaluate (bit): 카테고리에서 평가 기능 사용 여부
    • use_note (bit): 카테고리에서 댓글 기능 사용 여부
    • use_opinion (bit): 카테고리에서 의견 기능 사용 여부
    • use_tag (bit): 카테고리에서 태그 기능 사용 여부
    • writable (bit): 카테고리에서 글쓰기 가능 여부
    • write_by_external_link (bit): 카테고리에서 외부 링크를 통한 글쓰기 가능 여부
  • datetime
    • date_created (datetime): 카테고리 생성일시
  • category_info
    • default_label (varchar): 카테고리의 기본 레이블
    • external_link (varchar): 카테고리의 외부 링크
    • label_code (varchar): 카테고리의 레이블 코드
    • url (varchar): 카테고리의 URL입니다.
  • view
    • icon_css_names (varchar): 카테고리 아이콘의 CSS 클래스명
    • level (int): 카테고리의 계층 레벨
    • sort_order (int): 카테고리의 정렬 순서

 

article_tag : 게시글 - 태그 다대다 조인 테이블

  • 복합 기본키 (article_tags_id, tag_id ) 사용

 

scrap : 게시글 스크랩 정보

  • 복합 기본키 (avatar_id, article_id ) 사용
  • version(bigint) : Optimitic Lock 구현시 사용
  • date_created(datetime) : 스크랩 생성 일시

 

테이블 관계

  • article 테이블은 category 테이블과 일대 다 관계
  • article_tag는 게시글과 태그 간 다대다 관계

 

인사이트

  • article 테이블의 note_count, scrap_count, view_count, vote_count 을 통해 게시글 활성화 정도를 확인할 수 있다.
  • category 테이블의 level과 parent_id를 통해 카테고리의 계층 구조를 구현한다. 카테고리 트리 메뉴를 구현할 수 있다.

 

자주 변경되는 데이터 캐싱, 배치 처리

  • view_count와 같이 실시간으로 자주 변경되는 데이터는 DB에 부하를 줄 수 있다.
  • 굳이 실시간으로 반영하지 않아도 되는 경우 애플리케이션 레벨에서 캐시(Redis)를 사용하여 조회수를 관리하고, 일정 시간 간격으로 DB에 업데이트하는것이 좋다.
    • 게시글 id- 조회수를 key-value로 하여 캐시에 저장한다.
    • 조회 수를 표시할 때는 캐시에서 값을 읽어와 DB 부하를 줄인다.
    • 캐시에 저장된 조회수 데이터를 주기적으로 데이터베이스에 업데이트하는 배치 작업을 수행한다. (일정 시간 간격으로 수행)
    • 배치 작업 수행시 UPDATE 대신 INSERT 쿼리를 사용해 조회수를 증가시킬 수 있다.
      (UPDATE는 경합 문제가 발생할 수 있어서 조회수 증가 테이블을 따로 만들어서 INSERT한 뒤 데이터를 집계하여 DB를 갱신한다)

 

article (게시글 메타 정보)와 content(게시글 내용)을 분리해서 저장하는 이유

  • article 테이블로부터 content 테이블을 분리하여 SRP 원칙을 따른다.
    • article 테이블 : 게시글 메타 데이터 저장
    • content : 게시글 실제 내용 저장
  • 데이터 일관성 유지
    • 게시글 내용의 변경이 잦은 경우 content 테이블만 업데이트 하면 된다.
  • 성능 최적화
    • 게시글 목록 조회시 대부분 메타 데이터만 필요하기 때문에 article 테이블만 조회하면 되어서 쿼리 성능이 향상된다.
    • 게시글 내용을 조회할 때는 content 테이블을 조인해서 가져오면 된다. 필요한 경우에만 내용을 로드할 수 있어 효율적이다.
  • 확장성, 유연성
    • 내용의 구조를 변경할 경우 content 테이블만 수정하면 된다.
  • 용량 최적화
    • content는 용량이 크므로 별도로 관리하면 필요에 따라 다른 스토리지에 저장하거나 압축 등의 기술을 적용할 수 있다. good

 

게시글, 댓글 및 의견 내용(content) 관련 테이블

content : 게시글, 댓글, 의견 내용 정보

 

 

  • id (bigint): 내용의 고유 식별자
  • FK
    • article_id (bigint): 게시글의 ID를 참조하는 외래 키
    • author_id (bigint): 작성자의 ID를 참조하는 외래 키
    • last_editor_id (bigint): 마지막으로 내용을 수정한 사용자의 ID를 참조하는 외래 키
  • version (bigint): 내용의 버전 정보
  • a_nick_name (varchar): 익명 작성자의 닉네임
  • flag
    • anonymity (bit): 익명 여부
    • selected (bit): 내용이 선택되었는지 여부
  • create_ip (varchar): 내용 작성 시 사용된 IP 주소
  • datetime
    • date_created (datetime): 내용 작성일시
    • last_updated (datetime): 내용 최종 수정일시
  • info
    • text (longtext): 내용의 텍스트
    • text_type (int): 내용의 텍스트 유형
    • type (int): 내용의 유형
    • vote_count (int): 내용에 대한 투표 수

 

file : 파일

  • id (bigint): 파일의 고유 식별자
  • version (bigint): 파일의 버전 정보
  • attach_type (varchar): 파일의 첨부 유형
  • byte_size (int): 파일의 바이트 크기
  • height (int): 이미지 파일의 높이
  • name (varchar): 파일의 이름
  • org_name (varchar): 파일의 원본 이름
  • type (varchar): 파일의 유형
  • width (int): 이미지 파일의 너비

 

content_file : 게시글 - 파일 다대다 조인 테이블

  • 복합 기본키 (content_files_id, file_id ) 사용

content_vote : 게시글 - 투표 다대다 조인 테이블

  • id (bigint): 투표의 고유 식별자
  • FK
    • article_id (bigint): 게시글의 ID를 참조하는 외래 키
    • voter_id (bigint): 투표자의 ID를 참조하는 외래 키
    • content_id (bigint): 내용의 ID를 참조하는 외래 키
  • info
    • date_created (datetime): 투표 일시
    • point (int): 투표 점수

 

opinion : 의견(댓글) 정보

  • id (bigint): 의견의 고유 식별자
  • FK
    • content_id (bigint): 내용의 ID를 참조하는 외래 키
    • author_id (bigint): 작성자의 ID를 참조하는 외래 키
  • version (bigint): 의견의 버전 정보
  • info
    • comment (longtext): 의견의 텍스트
    • vote_count (int): 의견에 대한 투표 수
  • datetime
    • date_created (datetime): 의견 작성일시
    • last_updated (datetime): 의견 최종 수정일시

 

change_log : 게시글 변경 로그

게시글, 내용 등의 변경 이력을 추적하기 위한 용도로 사용된다.

  • id (bigint): 변경 로그의 고유 식별자
  • FK
    • article_id (bigint): 변경된 게시글의 ID를 참조하는 외래 키
    • avatar_id (bigint): 변경을 수행한 사용자의 아바타 ID를 참조하는 외래 키
    • content_id (bigint): 변경된 내용의 ID를 참조하는 외래 키
  • version (bigint): 변경 로그의 버전 정보
  • datetime
    • date_created (datetime): 변경 로그의 생성일시
  • info
    • md5 (varchar): 변경된 내용의 MD5 해시값
    • patch (longtext): 변경 내용의 패치(diff) 정보
    • revision (int): 변경의 리비전 번호
    • type (varchar): 변경의 유형 (예: 생성, 수정, 삭제 등)

 

테이블 관계

  • content 테이블과 article 테이블(게시글 메타정보)은 일대다 관계
    • 한 게시글은 여러개의 content를 가질 수 있다.
  • content 테이블과 file 테이블은 다대다 매핑 (content_file)
  • content 테이블과 vote 테이블은 다대다 매핑 (content_vote)
  • content 테이블과 opinion 테이블은 일대다 관계
    • 한 게시글 내용에 여러개의 댓글이 달릴 수 있다.

 

인사이트

  • 익명 작성 기능
    • content 테이블의 anonymity 컬럼과 a_nick_name 컬럼
  • 파일 타입
    • content 테이블의 type과 text_type

 

Aggregation(집약) : 데이터 모델링

  • 게시글 관련 테이블
    • article : 게시글의 메타 데이터 저장, 관리
    • content : 게시글의 실제 내용 저장
게시글의 내용이 변경되어도 메타 데이터인 article에 영향을 주지 않는다.

 

  • `article`이 아닌 content 테이블을 중심으로 file, content_vote, opinion 테이블이 관련된 이유
    • file, vote, opinion은 게시글의 내용과 직접적으로 관련되어 있으므로 content 테이블을 중심으로 관계를 설정하는 것이 데이터의 응집력을 높일 수 있다.
    • 게시글의 내용과 관련된 데이터를 한 곳에서 관리할 수 있어 데이터 일관성을 유지하기 쉽고, 조회 및 조작이 용이하다.

 

태그 테이블

tag : 태그 정보

  • id (bigint): 태그의 고유 식별자
  • date_created (datetime): 태그의 생성일시
  • description (varchar): 태그에 대한 설명
  • name (varchar): 태그의 이름
  • tagged_count (int): 해당 태그가 사용된 횟수

 

tag_similar_text : 유사한 태그 텍스트 저장

    • id (bigint): 유사 태그 텍스트의 고유 식별자
    • FK
      • tag_id (bigint): 태그의 ID를 참조하는 외래 키
    • version (bigint): 유사 태그 텍스트의 버전 정보
    • text (varchar): 유사한 태그 텍스트
목적 : 태그의 동의어나 유사어 관리 가능

 

기타 테이블

notification : 알림 정보

  • id (bigint): 알림의 고유 식별자
  • FK
    • article_id (bigint): 알림과 관련된 게시글의 ID를 참조하는 외래 키
    • sender_id (bigint): 알림을 발송한 사용자의 ID를 참조하는 외래 키
    • receiver_id (bigint): 알림을 받은 사용자의 ID를 참조하는 외래 키
    • content_id (bigint): 알림과 관련된 내용의 ID를 참조하는 외래 키
  • version (bigint): 알림의 버전 정보
  • datetime
    • date_created (datetime): 알림의 생성일시
    • last_updated (datetime): 알림의 최종 수정일시
  • type (varchar): 알림의 유형 (예: 댓글, 추천, 태그 등)

 
 

ERD 분석 후기

Okky 사이트의 ERD를 분석하면서 커뮤니티 사이트에서 사용하는 Entity들과 그 Entity들의 관계를 파악하고 구조화하는 방법을 배울 수 있어서 좋았다.
인덱스에 대한 정보는 ERD에서 확인하지 못해서 아쉬웠지만, 수직 파티셔닝와 Aggregation 등의 개념을 접할 수 있어서 좋았다.

수직 파티셔닝을 통해 테이블을 분할하여 성능 최적화를 수행하고,
Aggregation을 지켜 도메인간의 관계를 설정하는 방법을 알 수 있었다.

 
앞으로도 DB 설계 사례를 분석하면서 탄탄하게 DB 설계를 하는 개발자가 되고 싶다.


728x90