[ERD] '도우미' DB 설계

Intro

'도우미'프로젝트는 코딩테스트 질문을 남길 수 있고, 퀴즈를 만들어 사람들이 풀게 끔하는 웹사이트이다.

1. 회원정보를 저장하는 user 테이블이 필요하다.
2. 코딩테스트 질문 글,퀴즈 글을 작성할 수 있는 test, quiz 테이블도 필요할 것이다.
3. 해당 글에 댓글을 달 수 있는 기능을 넣을 것이므로 comment테이블도 필요하다.
4. 퀴즈는 태그를 통해 '자바' 'html' '인터페이스' 등으로 검색할 수 있게 하기위해 tag 테이블이 필요하다.
   4-1 퀴즈 tag들은 크게 자바나 스프링 같은 type이 있고 type별로 세부특성을 가진다. 예를 들면 자바면 인터페이스, 예외처리, 람다식 등등으로 자세하게 나눌 것이다.
   4-2 tag테이블에는 tag들의 속성, 세부 특성을 저장하는 테이블이기 때문에, 퀴즈 글과 연결할 quiztag테이블도 필요하다.
5. 좋아요 기능을 넣을 것이기 때문에 likes테이블이 필요하다.

---

1. 발생한 문제점

처음에 내가 짰던 데이터 베이스 구조이다.

여기서 문제점이 여러가지 발생한다.

1. 댓글과 글 기능에 모두 좋아요 기능을 넣기 위해서는 따로 글과 댓글 각각에게 연결 시킬 likes 테이블을이 추가적으로 들어가야한다.

2. 댓글에 연결된 likes 테이블만 있고 갯수를 바로 조회하는 컬럼이 없어서 댓글마다 좋아요 갯수를 전부 counting해줘야한다.

3. quizimage의 사진 순서 문제이다. 블로그처럼 글을 작성하는 기능을 만들려면 사진의 순서가 필요한데 내가 짠 구조는 어떤 글에 사진이 저장되었는지만 저장하고 있다. 따라서, 어떤 사진이 먼저와야될 지 순서를 알 수 없는 문제가 생긴다.
   
   
   

이러한 문제점이 있기 때문에 해결방법을 생각해보았다.

---

2. 해결법 1

likes 테이블에 어떤 곳과 연결 시킬지 구분하는 type 컬럼 넣어주기

image에 순서를 보관할 order컬럼 넣어주기

likes 테이블이 추가로 들어가는 문제에 대한 해결책

• likes 테이블에 어떤 글이나 어떤 댓글인지 확인하는 type 컬럼을 추가한다.
• 좋아요 counting은 type과 type_id로 조회한다.
  • 예를 들면 type은 quizcomment고 type_id가 2라면 id가 2인 퀴즈 댓글의 좋아요 개수를 조회하는 것

counting 성능 저하 해결책

• 댓글과 글 테이블에 like 컬럼을 새로 생성하였다.
  • like컬럼은 좋아요가 1개라도 있는지 체크하는 컬럼이다.
  • 기본으로 저장할 때 좋아요가 1개라도 있으면 like컬럼은 true가 되고, 0개라면 false로 저장한다.
  • like컬럼이 true라면 likes 테이블에 counting을 하여 좋아요 갯수를 확인한다.

이미지 순서 정하기

https://congsong.tistory.com/68
같이 프로젝트를 진행하는 팀원 분께서 알려주신 toast api이다.
해당 api를 쓰면 따로 이미지를 저장하는 테이블이 필요 없을 것 같긴한데 일단 어디다 저장할 지 모르니 image테이블은 그대로 두었다.

• 사진의 순서를 저장할 order 컬럼을 넣어주었다.

해결법1 장점

• 테이블은 많지만 간단하다.

해결법1 단점

• likes테이블에 고유 값이 여러가지라 외래키를 만들지 못한다.
  • type에 올 수 있는 데이터는 quiz, test, quizcomment, testcomment가 있어서 연결하지 못한다.
  • type_id에 올 수 있는 데이터도 각각 테이블의 id이기 때문에 연결하지 못한다.
• 외래키를 만들지 못하기 때문에 삭제할때 cascading이 이루어지지 않는다.
  • 자동으로 삭제가 되지 앟아서 별도의 쿼리를 사용해야한다.

---

3. 해결법 2

테이블 수를 줄여서 likes 테이블 추가하기

사진 순서는 해결법 1과 같음

테이블 개수 줄이기

• quiz와 test의 공통부분을 post테이블로 만들어줬다.
  • quiz인지 test인지 구별하기 위해서 type 컬럼을 만들었다.
• quiz에는 tag기능과 answer기능, 이미지 첨부 기능이 더 있기 때문에 서브테이블로 따로 빼줬다.
  • tag,quiztag, answer , postimage 테이블 참고
• quizcomment, testcomment도 똑같이 합쳐comment테이블로 만들어주었다.
  • quiz댓글에는 가리기 기능 때문에 display를 넣어줘야되는데, test댓글에 경우 가리기 기능이 필요없다
    -기본 값을 true로 주고 quiz 댓글에만 가리기 기능을 활성화 시켜 false로 넣어주면 될 것 같아 공통 테이블에다가 display 컬럼을 넣어주었다.

likes 테이블 추가로 들어가는 문제

• likes의 기능이 테이블이 줄어들었기 때문에 추가로 만들어도 상관없을 것 같아 post와 comment부분에 각각 만들어 주었다.
• 데이터 무결성을 지켜야하기 때문에 user_id와 post_id 둘 다 primary key로 만들어주었다. (중복 데이터 못 들어감)

해결법2 장점

• 테이블 갯수가 줄어들었음.
• likes테이블이 외래키로 연결되어있기 때문에 글이나 댓글 데이터를 제거할 때 같이 cascading으로 제거된다.

해결법2 단점

• 테이블 갯수가 준 만큼 복잡해진다.

---

최종 결과물

최종 결과물은 해결법 2를 선택하였고 조금 수정을 하였다.

사진 이미지는 toast api를 사용하기로 결정해서(file을 다른 서버에 저장해놓고 markdown에서 바로 불러오는 방식) 사진 이미지 테이블은 삭제해주었다.

comment와 post테이블의 like 컬럼을 두지 않고, like 테이블을 하나로 두고 type 컬럼으로 구별할 수 있게 하였다. 또한 post_id, user_id, type의 데이터가 중복되지 않게 UNIQUE로 두었다.
그리고 counting하는 방식을 사용하기로 하였기 때문에 post와 comment에는 like 컬럼을 빼주었다.

comment에는 대댓글도 필요하기 때문에 parent_comment_id 컬럼을 추가하였다.
만약 댓글이면 parent_comment_id가 0이 되고, 대댓글이면 parent_comment_id는 해당 대댓글의 부모 댓글 id가 된다.

1. 사용자 테이블(user)

• id (BIGINT, PRIMARY KEY, AUTO_INCREMENT): 사용자 고유 번호
• userId VARCHAR(255): 사용자 아이디
• password VARCHAR(255): 사용자 비밀번호

2. 게시판 테이블(post)

• id (BIGINT, PRIMARY KEY, AUTO_INCREMENT): 게시판 고유 번호
• user_id BIGINT: 사용자 아이디
• type VARCHAR(255): 퀴즈, 코딩테스트 타입
• title VARCHAR(250) : 게시판 제목
• contents VARCHAR(3000) : 게시판 내용
• created_at DATETIME : 생성 날짜
• updated_at DATETIME : 수정 날짜

3. 좋아요 테이블(likes)

• id (INT, PRIMARYKEY, AUTO_INCREMENT): 좋아요 고유번호
• post_id (BIGINT, UNIQUE): 게시판 or 댓글 고유 번호
• user_id (BIGINT, FOREIGN KEY, UNIQUE): 사용자 아이디
• type VARCHAR(50): 댓글, 게시판인지 구별하는 타입

4. 정답 테이블(answer)

• post_id BIGINT: 게시판 아이디
• answer VARCHAR(3000): 게시판 정답

5. 댓글 테이블(comment)

• id (BIGINT, PRIMARY KEY, AUTO_INCREMENT): 댓글 고유 번호
• user_id BIGINT: 사용자 아이디
• post_id BIGINT: 게시물 아이디
• type VARCHAR(255): 퀴즈, 코딩테스트 타입
• title VARCHAR(250) : 게시판 제목
• contents VARCHAR(1500) : 게시판 내용
• created_at DATETIME : 생성 날짜
• updated_at DATETIME : 수정 날짜
• like INT(11) : 좋아요 수
• display TINYINT(4) : 공개, 비공개(display=0이라면 공개, display=1이면 비공개이다.)
• parent_comment_id: BIGINT : 부모 댓글 아이디 (대댓글일때 존재, 댓글일 경우 0)

6. 태그 테이블(tag)

• id BIGINT: 태그 고유번호
• type VARCHAR(50): 과목 이름
• title VARCHAR(50) : 세부 과목 이름

7. 태그-게시판 연결 테이블(quiztag)

• post_id BIGINT: 게시물 아이디
• tag_id BIGINT: 태그 아이디