728x90
Overview
Okky
2000년에 만들어진 국내 최대 개발자 지식공유 플랫폼이다.
erdcloud 사이트에 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): 아바타 태그의 IDtag_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): 게시글을 마지막으로 수정한 사용자의 IDselected_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 설계를 하는 개발자가 되고 싶다.
