ERD 설계 - 100-hours-a-week/5-yeosa-wiki GitHub Wiki

온기 서비스 ERD

ERD 클라우드 : 바로가기

온기 (9)

주요 변경사항

25.04.17. 피드백 반영 25.04.19. ERD 설계 문서화 25.05.28. ERD 수정(테이블 추가) : 테이블 13~15

1. User(유저)

필드명	타입	제약조건	설명
id	bigint	PK, NOT NULL	온기 서비스 유저 아이디
provider	varchar(50)	NOT NULL	OAuth 제공자 이름 (kakao 등)
provider_id	varchar(20)	NOT NULL	OAuth 제공자의 고유 유저 ID
nickname	varchar(30)	NOT NULL	사용자 닉네임 (최대 30자)
profile_image	varchar(512)	NOT NULL	기본값 : 기본 프로필 이미지(카톡 프로필이미지가 없을 경우 / 카톡 프로필이미지 있으면 그거 씀)
email	varchar(320)	NOT NULL	OAuth를 통해 제공받는 이메일
created_at	timestamp	NOT NULL
updated_at	timestamp	NOT NULL
status	varchar(20)	NOT NULL, 기본값: `ACTIVE`	회원이 탈퇴하면 `RESIGNED`, 기본값은 `ACTIVE`의 enum class
제약조건	-	UNIQUE(provider, provider_id)	소셜 로그인 유일 식별자 조합

현재 서비스는 kakao OAuth만 구현하지만 향후 다른 OAuth를 적용할 것을 고려(google, github 등)하여 provider 관련 정보를 같은 테이블에 저장
이메일은 공식문서 참고(rfc2821상 이메일은 최대 320자)

2. Follow(팔로우)

필드명	타입	제약조건	설명
id	bigint	PK, NOT NULL, Auto Increment	팔로우 ID
follower_id	bigint	FK → User.id, NOT NULL	팔로우를 건 유저 ID
following_id	bigint	FK → User.id, NOT NULL	팔로우 당한 유저 ID
created_at	timestamp	NOT NULL	팔로우 시각
제약조건	-	UNIQUE (follower_id, following_id)	유저 간 중복 팔로우 금지

유저가 유저 스스로를 다대다 매핑하는 형태, 그 중간을 Follow로 매핑
created_at 컬럼으로 팔로우를 언제 걸었는지 확인가능
팔로우/언팔로우는 액션이 빈번할 것으로 예상되므로 Redis Set 사용을 통한 부하 분산 예정

index

UNIQUE INDEX uq_follow ON Follow (follower_id, following_id)
- 동일 유저 중복 팔로우 방지
INDEX idx_follow_following_id ON Follow (following_id)
- 특정 유저를 팔로잉중인 유저 목록 빠르게 조회
INDEX idx_follow_follower_id ON Follow (follower_id)
- 특정 유저의 팔로워 목록 빠르게 조회

3. Notification(알림)

필드명	타입	제약조건	설명
id	bigint	PK, NOT NULL, Auto Increment	알림 ID
user_id	bigint	FK → User.id, NOT NULL	알림 받을 유저 ID
actor_id	bigint	FK → User.id, NOT NULL	알림을 유발한 유저 ID
type	varchar(20)	NOT NULL	알림 타입 (`ALBUM_INVITE`, `ALBUM_TRANSFER`, `FEED_LIKE`, `FEED_COMMENT`, `FOLLOW`)
ref_id	bigint	NOT NULL	관련 리소스 ID (앨범 or 피드 등)
is_read	bool	NOT NULL, 기본값: false	읽음 여부
created_at	timestamp	NOT NULL	생성 시각

ALBUM_INVITE : 앨범에 초대되었을 때, ALBUM_TRANSFER : 앨범의 OWNER 권한을 받았을 때, FEED_LIKE : 피드에 좋아요가 달렸을 때, FEED_COMMENT: 피드에 댓글이 달렸을 때, FOLLOW : 누군가가 팔로우를 걸었을 때
ref_id : 앨범 아이디, 피드 아이디 등 알람이 발생한 리소스의 아이디
메세지는 type enum class에서 동적으로 생성할 예정으로 컬럼으로 두지 않음

index

INDEX idx_notification_user_id ON Notification (user_id)
- 유저가 받은 알림 빠르게 조회
INDEX idx_notification_is_read ON Notification (is_read)
- 유저가 안읽은 알림 빠르게 조회

4. UserAlbum(유저-앨범 관계)

필드명	타입	제약조건	설명
id	bigint	PK, NOT NULL	유저-앨범 관계 ID
user_id	bigint	FK → User.id	유저 ID
album_id	bigint	FK → Album.id	앨범 ID
role	varchar(20)	NOT NULL	앨범 권한 (OWNER, NORMAL)
제약조건	-	UNIQUE(user_id, album_id)	유저가 같은 앨범에 중복 참여 불가

앨범 생성자는 OWNER, 앨범에 초대받은 사용자는 NORMAL 권한을 가짐
각 앨범에 대한 유저들의 권한을 각기 다르게 설정하기 위해 유저와 앨범 사이에 테이블을 추가하여 다대다 관계 구현
탈퇴할 경우 OWNER 권한은 자동으로 앨범 내 공동사용자 중 한명에게 이양(한명 선정기준 : UserAlbum.findbyId(앨범아이디) 시 본인을 제외한 가장 첫번째 값)
OWNER권한은 앨범 내 NORMAL 권한 보유자에게 넘겨줄 수 있음. 넘겨주면 넘겨준 사람은 OWNER->NORMAL로 권한 변경

index

UNIQUE INDEX idx_user_album_unique ON UserAlbum (user_id, album_id)
- 같은 유저가 동일 엘범에 중복으로 참여 못하게 설정
INDEX idx_user_album_album_id ON UserAlbum (album_id)
- 앨범별로 참여중인 유저 빠르게 조회

5. Album(앨범)

필드명	타입	제약조건	설명
id	bigint	PK, NOT NULL	앨범 ID
thumbnail_id	bigint	FK → Picture.id	썸네일 사진 ID
album_name	varchar(12)	NOT NULL	앨범 이름
created_at	timestamp	NOT NULL	생성일
deleted_at	timestamp	NULL	삭제일

soft delete 전략을 위해 deleted_at 도입, deleted_at에 timestamp가 있으면 논리적으로 데이터를 숨김
앨범을 삭제하면 연관관계의 사진들도 삭제된다.

index

INDEX idx_album_thumbnail_id ON Album (thumbnail_id)
- 앨범 썸네일 빠르게 조회
INDEX idx_album_is_deleted ON Album (deleted_at)
- 삭제되지 않은 앨범 빠르게 조회

6. Picture(사진)

필드명	타입	제약조건	설명
id	bigint	PK, NOT NULL	사진 ID
album_id	bigint	FK → Album.id	앨범 ID
place_id	bigint	FK → Place.id	장소 ID
user_id	bigint	FK → User.id	사진을 올린 유저 ID
image_url	varchar(512)	NOT NULL	S3 이미지 저장 URL
tag	varchar(8)	NULL	AI 이미지 태그
is_duplicated	bool	기본값: false	중복 사진 여부
is_shaky	bool	기본값: false	흔들린 사진 여부
quality_score	float	NULL	사진 퀄리티 점수
created_at	timestamp	NOT NULL	사진 생성일
latitude	double	NULL	위도
longitude	double	NULL	경도
deleted_at	timestamp	NULL	삭제일

soft delete 전략을 위해 deleted_at 도입, deleted_at에 timestamp가 있으면 논리적으로 데이터를 숨김
사진 생성일과 위도, 경도는 사진 메타데이터에서 추출, 메타데이터가 없을 수도 있으니 null 허용
사진 메타데이터랑 장소를 분리하여 다대일 매핑을 한 이유 : Place를 별도로 추출해서 유저 대시보드에서 통계성 태그 제공 가능, 향후 장소 검색 시 유사한 피드 추천 가능
image_url은 프론트에서 백으로 반환하는 값, S3에 저장하는 url을 반환하므로 varchar(512)
피드가 삭제되도 사진은 삭제되지 않는다.
앨범이 삭제되면 사진도 삭제된다.

index

INDEX idx_picture_album_id ON Picture (album_id)
- 앨범별 사진 목록 빠르게 조회
INDEX idx_picture_user_id ON Picture (user_id)
- 유저가 업로드한 사진 빠르게 조회
INDEX idx_picture_place_id ON Picture (place_id)
- 장소별로 사진 조회
INDEX idx_picture_deleted_at ON Picture (deleted_at)
- 삭제 안된 사진들 빠르게 조회

7. Place(장소)

필드명	타입	제약조건	설명
id	bigint	PK, NOT NULL, Auto Increment	장소 ID
city	varchar(15)	NULL	시/도 명
district	varchar(15)	NULL	구/군 명
town	varchar(15)	NULL	동/읍/면 명
제약조건	-	UNIQUE(sido, gu, dong)	한 지역은 한번만 등록되어야 함

우리나라에서 가장 긴 지역명이 13자리임. 만일을 대비하여 제한을 15자로 설정
시, 구, 동은 picture의 latitude, longitude를 kakaomap api를 사용하여 변환한 region1~3을 사용
사진의 메타데이터가 없을 수도 있고, kakaomap api 특성 상 바다에서 찍은 경우 응답 결과에 아무것도 시현되지 않으므로(documents.length == 0) 해당 상황 반영하여 null 허용

index

UNIQUE INDEX idx_place_unique ON Place (city, district, town)
- 중복된 장소 저장 방지

8. Feed(피드)

필드명	타입	제약조건	설명
id	bigint	PK, NOT NULL	피드 ID
user_id	bigint	FK → User.id	작성 유저 ID
thumbnail_id	bigint	FK → Picture.id	썸네일 사진 ID
album_id	bigint	FK → Album.id	앨범 ID
content	text	NOT NULL	피드 내용
created_at	timestamp	NOT NULL	작성일
updated_at	timestamp	NOT NULL	수정일
deleted_at	timestamp	NULL	삭제일

soft delete 전략을 위해 deleted_at 도입, deleted_at에 timestamp가 있으면 논리적으로 데이터를 숨김
피드는 삭제되면 피드-사진 관계는 자동 삭제되지만 사진은 삭제되지 않는다.

index

*INDEX idx_feed_user_id ON Feed (user_id)

유저가 작성한 피드 빠르게 조회 *INDEX idx_feed_thumbnail_id ON Feed (thumbnail_id)
썸네일 빠르게 조회 *INDEX idx_feed_deleted_at ON Feed (deleted_at)
삭제되지 않은 피드만 빠르게 조회

9. FeedPicture(피드-사진 관계)

필드명	타입	제약조건	설명
id	bigint	PK, NOT NULL	피드-사진 ID
picture_id	bigint	FK → Picture.id	사진 ID
feed_id	bigint	FK → Feed.id	피드 ID
제약조건	-	UNIQUE(picture_id, feed_id)	하나의 피드에 동일 사진이 여러번 등록되면 안됨

피드가 삭제되면 피드-사진 관계는 cascade로 삭제된다.

index

UNIQUE INDEX idx_feed_picture_unique ON FeedPicture (picture_id, feed_id)
- 피드에 동일한 사진이 등록되지 않도록 방지
INDEX idx_feed_picture_feed_id ON FeedPicture (feed_id)
- 피드별로 등록된 사진을 빠르게 조회

10. FeedLike(좋아요)

필드명	타입	제약조건	설명
id	bigint	PK, NOT NULL, Auto Increment	좋아요 ID
user_id	bigint	FK → User.id, NOT NULL	좋아요 누른 유저 ID
feed_id	bigint	FK → Feed.id, NOT NULL	좋아요 대상 피드 ID
created_at	timestamp	NOT NULL	좋아요 누른 시각
제약조건	-	UNIQUE (user_id, feed_id)	유저가 동일 피드에 중복 좋아요 금지

한 유저는 한 피드에 한번만 좋아요을 누를 수 있음을 제약조건에 명시
액션이 빈번할 것으로 예상되므로 향후 Redis에서 Set 통한 부하 분산 예정

index

UNIQUE INDEX idx_feedlike_unique ON FeedLike (user_id, feed_id)
- 유저가 같은 피드에 여러번 좋아요 못누르게 방지
INDEX idx_feedlike_feed_id ON FeedLike (feed_id)
- 피드별 좋아요 개수 빠르게 집계

11. Comment(댓글)

필드명	타입	제약조건	설명
id	bigint	PK, NOT NULL, Auto Increment	댓글 ID
feed_id	bigint	FK → Feed.id, NOT NULL	피드 ID
user_id	bigint	FK → User.id, NOT NULL	작성자 유저 ID
content	text	NOT NULL	댓글 내용
created_at	timestamp	NOT NULL	작성 시각
updated_at	timestamp	NOT NULL	수정 시각
deleted_at	timestamp	NULL	삭제일

soft delete 전략을 위해 deleted_at 도입, deleted_at에 timestamp가 있으면 논리적으로 데이터를 숨김

index

INDEX idx_comment_feed_id ON Comment (feed_id)
- 피드에 달린 댓글 리스트 빠르게 조회
INDEX idx_comment_is_deleted ON Comment (is_deleted)
- 삭제되지 않은 댓글만 빠르게 조회

12. UserOAuthToken(유저 OAuth 토큰관리)

필드명	타입	제약조건	설명
id	BIGINT	PK, AUTO_INCREMENT
user_id	BIGINT	FK → User.id	user와 1:N 매핑
provider	VARCHAR(50)	NOT NULL	OAuth 제공 서비스
access_token	TEXT	NOT NULL	카카오 access token
refresh_token	TEXT	NOT NULL	카카오 refresh token
access_token_expires_at	TIMESTAMP	NOT NULL	access token 만료 시각
refresh_token_expires_at	TIMESTAMP	NULL	refresh token 만료 시각
created_at	TIMESTAMP	DEFAULT now()	최초 저장 시각
updated_at	TIMESTAMP	DEFAULT now() ON UPDATE now()	업데이트 시각

OAuth token 테이블 도입 이유 : 카카오맵 로컬 api 사용을 위해(좌표를 행정구역으로 변환)
refresh_token_expires_at는 kakao의 경우 not null이지만 다른 oauth 도입 시 null이 될 수도 있으므로 확장성 고려 null 허용
- 구글 없음, 깃허브는 아예 refresh token 자체가 없음

index

INDEX idx_oauth_user_id ON UserOAuthToken (user_id)
- 유저별로 발급된 oauth 토큰 빠르게 조회

13. FaceCluster(얼굴 클러스터)

필드명	타입	제약조건	설명
id	BIGINT	PK, AUTO_INCREMENT	클러스터 고유 아이디
representitive_picture	BIGINT	FK → Picture.id	대표 사진 아이디
cluster_name	VARCHAR(20)	NOT NULL	클러스터 이름 (수정 가능)
bboxX1	INT	NOT NULL	얼굴 bbox 좌상단 X좌표
bboxY1	INT	NOT NULL	얼굴 bbox 좌상단 Y좌표
bboxX2	INT	NOT NULL	얼굴 bbox 우하단 X좌표
bboxY2	INT	NOT NULL	얼굴 bbox 우하단 Y좌표
created_at	TIMESTAMP	DEFAULT now()	클러스터 생성 시각

bbox : 대표사진의 경우 프론트가 확대해서 보여줘야 하므로 확대할 수 있는 좌표를 bbox로 반환

14. PictureFaceCluster(사진-클러스터 매핑)

필드명	타입	제약조건	설명
id	BIGINT	PK, AUTO_INCREMENT	사진-클러스터 고유 아이디
picture_id	BIGINT	FK → Picture.id	연결된 사진 고유 아이디
face_cluster_id	BIGINT	FK → FaceCluster.id	연결된 얼굴 클러스터 고유 아이디

사진과 클러스터 정보를 N:M으로 매핑하기 위한 중간 테이블

index

INDEX idx_picture_face_cluster_picture_id ON PictureFaceCluster (picture_id)
INDEX idx_picture_face_cluster_cluster_id ON PictureFaceCluster (face_cluster_id)

15. Concept(앨범 컨셉)

필드명	타입	제약조건	설명
id	BIGINT	PK, AUTO_INCREMENT	컨셉 고유 아이디
album_id	BIGINT	FK → Album.id	소속된 앨범의 고유 아이디
concept	VARCHAR(8)	NULL	앨범에 속한 컨셉 이름 (중복 허용)

index

INDEX idx_concept_album_id ON Concept (album_id)

중점사항

soft delete 전략 채택 이유

유저의 데이터 복구 요청 시 복구 가능
데이터 삭제 시 연관관계 데이터를 건드리지 않음으로서 연관성 유지 가능
is_deleted(bool)과 deleted_at(timestamp) 중 택일
- 처음에는 둘 다 도입, 그런데 둘 다 soft delete를 위해 도입한 컬럼이어서 중복성 발생
- 둘 다 테이블에 가져가는 것은 불필요하다고 판단, is_deleted 제거

Notification 테이블을 Feed, Follow, Album과 연관관계를 설정하지 않은 이유

향후 추가기능이 생겨 새로운 연관관계를 설정해야 할 경우 DDL을 재설정 불필요(enum class만 수정하면 됨)
알림 종류가 많아질 경우 수정 용이

soft delete 전략 수립 중 고려사항

삭제된 데이터들을 계속 가지고 있을 필요가 있나? 하는 의문 발생
현재까지 아이디어 : 스케쥴링을 통해 일정 주기가 지나면 is_delete가 true인 데이터를 hard delete(14일 정도 길게 설정)

피드백 반영사항(4.17.)

REFRESH TOKEN : Redis로 사용 예정
- REFRESH TOKEN 도입 이유 : ACCESS TOKEN 탈취 시 발생되는 보안문제(악성유저가 마음대로 서비스 이용 가능)를 대응하기 위해
- REFRESH TOKEN의 저장위치에 대한 고민
  - DB에 넣으면 Table 타입으로 영구적인 저장이 가능하다는 장점이 있음, 하지만 디스크 기반이라 IO가 느림
  - Redis는 메모리 기반이어서 IO가 굉장히 빠르고 TTL 자동계산 가능하여 간단함. 하지만 RDB에 비해서는 안정성이 떨어짐(장애상황시, 휘발성 특징 등)
  - access token은 보안을 위해 유효기간을 굉장히 짧게 가져갈 예정이다.(5분) 그러니 refresh token 저장소에 대한 접근이 많을 것으로 예상됨
  - 결론적으로 IO가 빠르고 TTL 자동계산이 용이한 Redis에 refresh token 저장 예정
- Place 테이블의 sido, gu, dong -> city, district, town 변환 완료
- Place 검색 시 User에서 조인되는 테이블이 너무 많은 문제
  - Place와 Picture를 별도로 1:N 설정해서 Place까지 발생하는 조인 수 감소시킴

reference

JWT official docs