🎯 요구사항 분석

• 마이페이지 - User (개인정보, 회원가입, 로그인)
• 게시물 - 검색 (User, Post ← Tag), 해시태그, 유저태그, 위치표시, 이미지
• 메인페이지(타임라인에 보여질 Post)
• 디엠 - 채팅방 목록(카카오톡 채팅방이랑 유사하게 관리)
• 스토리 - 스토리 언급, 위치태그, 사진, 내용, 레이아웃
• 스토리 하이라이트 - 마이페이지에서 관리
• 알림
• (릴스)

ERD 설계

User(사용자)

• userId
• username (실명)
• nickname (사용자 아이디)
• password
• profileImgUrl
• totalFollower (팔로워 수)
• totalFollow (팔로우 수)
• birth (생년월일)
• website → null
• intro → null
• email
• phone
• gender
• status (상태 - 휴면/탈퇴/활성)

*좋아요를 User 테이블에 넣어두고 관계 매핑을 두 번 이상으로 할 것인가

Media(사진,동영상)

• mediaId
• mediaUrl (사진, 영상 원본 링크)

Story(스토리)

• storyId (PK, 스토리 번호)
• storyImgUrl (text)
• content
• layout
• isHighlight (하이라이트 여부)
• placeTag (FK)
• hashTag (FK)
• isPrivate (친한친구 모드)
• createdAt *수정은 불가능하므로 필요X

Post(게시물)

• postId
• postImgUrl
• order
• content
• createdAt
• updatedAt
• writer (FK, User)

→ 좋아요 수, 댓글 수, 해시태그 여부, 위치태그 여부

DM(채팅)

• roomId
• dialog (대화내용) → 사진, 문서 파일 등 모두 관리 어떻게?

→ 대화방에 참여중인 유저 목록, 최근에 도착한 메시지 (알림)

카카오톡 채팅방 관리와 유사하게
→ 게시물 공유, 사용자 프로필 공유 등의 기능

PostLike(게시물 좋아요)

• likeId
• postId(FK)
• userId(FK)

PostComment(게시물 댓글)

• commentId
• content (내용)
• commentOrder(댓글 순서)
• isDeleted(삭제여부)
• createdAt
• updatedAt

대댓글도 고려 → 자기자신과 일대일 관계 (부모댓글번호; parent_comment)

StoryLike(스토리 좋아요)

• likeId
• postUser(FK)
• replyUser(FK)

StoryReply(스토리 답변)

• commentId(PK)
• commentDepth (대댓글의 개수 → 트리구조로?)
• StoryEmoji(스토리 반응)
• dmId (dm.roomId를 FK로)
• postUser(FK)
• replyUser(FK)

Reels(릴스)

• reelsId(PK)
• videoContent → text(url로 처리?)

HashTag(해시태그)

• tagId
• tagName(태그명) → VARCHAR *글자수제한하기
• postId(FK) *게시물에서 태그를 사용한 경우
• userId(FK) *사용자가 스토리에서 태그를 사용한 경우

PlaceTag(장소태그)

• tagId
• place(장소명)
• address(위치좌표) → text, null *실제 장소가 아닌 경우도 있음
• postId(FK) *게시물에서 태그를 사용한 경우
• userId(FK) *사용자가 스토리에서 태그를 사용한 경우

Alert(알림)

• alertId(알림번호)
• content(내용)
• alertType(알림종류)
• time(시간)
• isChecked(확인여부)

UserTag(유저태그)

• tagId
• userId

Quick DBD 를 이용한 ERD 설계

User
-
userId PK int
nickname UNIQUE varchar(20)
password varchar(25)
username varchar(20)  
profileImgUrl text
totalFollower int
totalFollow int
birth varchar(20)
website NULL text
email NULL varchar(50)
phone NULL varchar(30)
gender int
status int

Story
-
storyId PK int
storyImg NULL int FK >-< Media.mediaId
userId int FK >- User.userId
content text
layout NULL int
isPrivate int
isHighLight int
createdAt timestamp
placeTag NULL int FK >- PlaceTag.tagId
hashTag NULL int FK >-< HashTag.tagId
userTag NULL int FK >-< UserTag.tagUser

StoryReply
-
replyId PK int
storyId int FK >- Story.storyId
dmRoom int FK >- DM.roomId
storyEmoji int
postUser int FK >- User.userId
replyUser int FK >- User.userId

StoryHighlight
-
highlightId PK int
thumbnail text
storyId int FK >-< Story.storyId
userId int FK >- User.userId

StoryHeart
- 
userId int FK >- User.userId
storyId int FK >- Story.storyId

Media
-
mediaId PK int
mediaUrl text

Post
- 
postId PK int
postImg int FK >-< Media.mediaId
postImgUrl text
order int
createdAt timestamp
updatedAt timestamp
writer int FK >- User.userId
placeTag NULL varchar(50) FK >- PlaceTag.tagId
hashTag NULL varchar(50) FK >-< HashTag.tagId
userTag NULL int FK >-< UserTag.tagUser
totalLikes int
totalComments int

PostLike
- 
postId int FK >- Post.postId
userId int FK >- User.userId

PostComment
-
commentId PK int
content text
commentOrder int
commentDepth int
isDeleted int
createdAt timestamp
updatedAt timestamp
writer int FK >- User.userId

PostCommentLike
-
postId int FK >- Post.postId
userId int FK >- User.userId

DM
- 
roomId PK int
dialog text
chatUser int FK >-< User.userId

HashTag
- 
tagId PK int
tagName UNIQUE varchar(50)

PlaceTag
-
tagId PK int
place varchar(50)
address NULL text

UserTag
-
userId int FK - User.userId
tagUser int FK >- User.userId

Alert
-
alertId PK int
content varchar(50)
alertType int
time timestamp
isChecked int
userId int FK >- User.userId

Reels
-
reelsId PK int
writer FK >- User.userId
videoContent text

---

Query문 작성하기

서비스 주요 기능

1. 로그인
2. 마이페이지 (내 프로필 보기)
3. 게시물 좋아요 개수 조회
4. 게시물 좋아요 목록 조회
5. 게시물 위치 조회 (위치태그)
6. 게시물 작성 / 스토리 작성
7. 게시물 수정 / 스토리 수정
8. 게시물 삭제 / 스토리 삭제
9. 해시태그 검색하기
10. 자신이 태그된 게시물 조회
11. 게시물 업로드 시간 조회
12. 스토리 하이라이트 목록 조회

더미데이터 생성

User

desc User;
INSERT INTO User(userId, nickname, password, username, profileImgUrl, totalFollower, totalFollow, birth, website, email, phone, gender, status)
VALUES(NULL, "abc_def", "1234567", "도레미", "url", 0, 0, "2000년 1월 1일", NULL, NULL, "010-1234-5678", 0, 0); 
INSERT INTO User(userId, nickname, password, username, profileImgUrl, totalFollower, totalFollow, birth, website, email, phone, gender, status)
VALUES(NULL, "jj_qwe", "1234567", "안녕", "url", 0, 0, "2000년 1월 1일", NULL, NULL, "010-1234-5678", 0, 0); 
INSERT INTO User(userId, nickname, password, username, profileImgUrl, totalFollower, totalFollow, birth, website, email, phone, gender, status)
VALUES(NULL, "ccc_gf", "1234567", "행복해", "url", 0, 0, "2000년 1월 1일", NULL, NULL, "010-1234-5678", 0, 0); 

Post

desc Post;
INSERT INTO Post(postId, postImg, postImgUrl, createdAt, updatedAt, writer, placeTag, hashTag, userTag, totalLikes, totalComments)
VALUES(NULL, 1, "img_link", now(), now(), 1, NULL, NULL, NULL, 0, 0);
INSERT INTO Post(postId, postImg, postImgUrl, createdAt, updatedAt, writer, placeTag, hashTag, userTag, totalLikes, totalComments)
VALUES(NULL, 1, "img_link2", now(), now(), 1, NULL, NULL, NULL, 0, 0);
INSERT INTO Post(postId, postImg, postImgUrl, createdAt, updatedAt, writer, placeTag, hashTag, userTag, totalLikes, totalComments)
VALUES(NULL, 1, "img_link3", now(), now(), 3, NULL, NULL, 1, 0, 0);

• Post3 - User1이 작성
• Post4 - User1이 작성
• Post5 - User3이 작성 + User1 태그

DELIMITER $$
DROP PROCEDURE IF EXISTS loopInsert$$

CREATE PROCEDURE loopInsert()
BEGIN
    DECLARE i INT DEFAULT 1;

    WHILE i <= 500 DO
        INSERT INTO Post (postId, postImg, postImgUrl, createdAt, updatedAt, writer, placeTag, hashTag, userTag, totalLikes, totalComments)
          VALUES(NULL, 1, concat('image',i), now(), now(), 1, NULL, NULL, NULL, 0, 0);
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER $$

CALL loopInsert;

Story

desc Story;
INSERT INTO Story(storyId, storyImg, userId, content, layout, isPrivate, isHighLight, createdAt, placeTag, hashTag, userTag) 
VALUES (NULL, 1, 1, "Hello!", NULL, 0, 0, now(), 1, NULL, NULL);
INSERT INTO Story(storyId, storyImg, userId, content, layout, isPrivate, isHighLight, createdAt, placeTag, hashTag, userTag) 
VALUES (NULL, 1, 2, "Hello!", NULL, 0, 0, now(), 1, NULL, 1);
INSERT INTO Story(storyId, storyImg, userId, content, layout, isPrivate, isHighLight, createdAt, placeTag, hashTag, userTag) 
VALUES (NULL, 1, 2, "Hello!", NULL, 0, 0, now(), 1, NULL, 1);

• Story1 - User1이 작성
• Story2 - User2이 작성
• Story3 - User2이 작성

HashTag

desc HashTag;
INSERT INTO HashTag (tagId, tagName)
VALUES (NULL, "#해시태그")
INSERT INTO HashTag (tagId, tagName)
VALUES (NULL, "#테스트")

UserTag

desc UserTag;
INSERT INTO UserTag(userId, tagUser)
VALUES(3, 1);

Media

desc Media;
INSERT INTO Media(mediaId, mediaUrl)
VALUES(1, "https://www.naver.com");

PostLike

desc PostLike;
INSERT INTO PostLike(postId, userId)
VALUES (3, 1);
INSERT INTO PostLike(postId, userId)
VALUES (3, 2);
INSERT INTO PostLike(postId, userId)
VALUES (3, 3);
INSERT INTO PostLike(postId, userId)
VALUES (4, 1);

• User1이 Post3을 좋아함
• User2이 Post3을 좋아함
• User3이 Post3을 좋아함
• User1이 Post4을 좋아함

*좋아요 순으로 게시물 정렬해보기

PlaceTag

INSERT INTO PlaceTag(tagId, place, address)
VALUES (NULL, "서울시(Seoul)", "용산구 한남동");

StoryHighlight

desc StoryHighlight

쿼리문

1. 로그인 성공 시 유저 마이페이지로 넘어오기

   # 실제 서비스에서는 닉네임, 전화번호, 이메일주소 모두 로그인 시 아이디로 사용가능하다.
   # 아이디와 비밀번호와 모두 일치한다면 로그인 성공으로 처리하고 마이페이지로 넘어간다.
   SELECT nickname, username, profileImgUrl, totalFollower, totalFollow 
   FROM User
   WHERE (nickname='abc_def' OR phone='010-6410-6452' OR email=?) AND password='1234567';

   

2. 게시물 좋아요 목록 조회 - Post3의 좋아요 목록을 조회하고 totalLikes를 1증가시킨다.

   # where문으로 Post3의 좋아요 목록을 조회한다.
   SELECT *
   FROM Post LEFT OUTER JOIN PostLike
   ON Post.postId = PostLike.postId
   WHERE Post.postId=3;

3. 게시물 좋아요 개수 조회 - Post3의 좋아요 개수를 조회하고 Post의 totalLikes 필드 값을 수정한다.

   # 집계함수를 사용하여 게시물의 좋아요 개수를 구한다.
   # Post의 좋아요 개수인 totalLikes를 목록 개수로 설정한다.
   SELECT COUNT(*)
   FROM Post LEFT OUTER JOIN PostLike
   ON Post.postId = PostLike.postId
   WHERE Post.postId=3;

4. 게시물 위치 조회

   SELECT place, address, postId FROM Post
   RIGHT OUTER JOIN PlaceTag
   ON Post.placeTag = PlaceTag.tagId
   WHERE Post.postId=3;

5. 게시물 / 스토리 수정 - 게시물에서 해시태그를 추가한다.

   UPDATE Post SET HashTag=1
   WHERE Post.postId=?;

6. 게시물 / 스토리 삭제 - 더 나중에(최근에) 올린 스토리부터 삭제할 수 있다.

   # 2개의 스토리를 게시한 사용자2가 최근에 업로드한 스토리를 삭제한다.
   DELETE FROM Story
   WHERE Story.userId=2
   ORDER BY createdAt DESC LIMIT 1;   # 내림차순 정렬 후 가장 위에 위치한 레코드를 삭제

7. 해시태그로 게시물 검색하기 (”#테스트”라는 태그가 있는 게시물 목록 조회)

   SELECT postId, postImg, postImgUrl, tagName from Post
   LEFT OUTER JOIN HashTag
   ON Post.hashTag = HashTag.tagId
   WHERE HashTag.tagId=2

8. 자신이 태그된 게시물 조회

   SELECT postId, postImg, postImgUrl, userTag from Post
   LEFT OUTER JOIN UserTag
   ON Post.userTag = UserTag.tagUser AND Post.writer != UserTag.userId
   WHERE Post.writer = 3

9. 게시물 업로드 시간 조회

   # 사용자1이 작성한 게시물의 업로드 시간 목록 조회
   SELECT postId, createdAt from Post
   LEFT OUTER JOIN User
   ON Post.writer = User.userId
   WHERE User.userId=1

10. 스토리 하이라이트 목록 조회

    # 사용자1의 하이라이트 목록에 스토리1,2,3이 있는 경우