CodeIgniter 4로 멀티보드 만들기 #3 — 테이블 설계도부터 그리기

지난 편에서 빈 프로젝트를 만들고 DB를 연결했습니다. php spark db:table --show 가 "Database has no tables!" 라고 대답하는 상태입니다.

이번 편에서는 그 빈 DB에 무엇이 들어갈지를 전부 그립니다. 테이블 여덟 개, 그 사이의 관계, 그리고 DB 정규화 이론과 다르게 간 이유입니다.

이번 편도 코드는 쓰지 않습니다. 마이그레이션은 다음 편부터입니다.

테이블을 한 번에 생성하는 이유

보통은 이렇게 합니다. 게시판을 만들 차례가 되면 boards 를 만들고, 댓글을 붙일 차례가 되면 comments 를 만듭니다. 필요할 때 필요한 만큼. 나쁜 방법이 아닙니다.

그런데 이 강좌는 그렇게 할 수 없습니다. 회차마다 태그를 찍기 때문입니다.

git checkout ep12 를 한 독자는 12편까지의 코드를 그대로 받습니다. 그런데 "아, comments 에 depth 컬럼이 필요하네" 하고 스키마를 고치면, 그 결정이 마이그레이션 파일까지 거슬러 올라갑니다. 이미 발행한 글과 태그를 전부 손봐야 합니다.

그래서 규칙을 하나 정합니다.

스키마는 지금 완성형으로 설계하고, 회차별로 필요한 테이블만 잘라서 마이그레이션한다. 뒤 회차에서 ALTER TABLE 로 컬럼을 덧붙이는 마이그레이션은 두 건만 허용한다.

허용하는 두 건은 posts.extra 와 boards.field_schema 입니다. 이 강좌의 주제 그 자체라서, 앞에서 미리 넣어두면 정작 그 편에서 보여줄 게 없어집니다. 그 둘 말고 다른 변경이 필요해지면 — 설계가 틀렸다는 뜻이니 기획서부터 고칩니다.

이런 제약이 답답해 보일 수 있는데, 실무에서도 사정은 비슷합니다. 운영 중인 서비스의 posts 테이블에 컬럼을 하나 추가하는 일은 로컬에서 마이그레이션 한 줄 돌리는 것과 전혀 다른 일이 됩니다. 처음에 한 번 제대로 그려두는 값어치는 그때 나옵니다.

전체 지도

users ──< posts >── boards
  │        │  │        │
  │        │  └──< attachments
  │        └──< comments (self-ref: parent_id)
  │        └──< post_likes
  └──< reports (polymorphic: post|comment)

ci_sessions (독립)

지도를 이렇게 놓고 보면 구조가 한 문장으로 정리됩니다. 축은 셋(users, boards, posts)이고, attachments 와 comments, post_likes 는 posts 에 매달려 있습니다. 예외가 둘입니다. reports 는 글과 댓글을 함께 가리키는 다형 참조라 대상 쪽에 FK 를 걸 수 없고, 그래서 그림에서도 신고한 사람(users) 아래에 놓았습니다 — 이 관계를 어떻게 다루는지는 나중에 다시 이야기 하겠습니다. ci_sessions 는 아무와도 연결되지 않는데, 프레임워크가 쓰는 저장소일 뿐 우리 도메인이 아니기 때문입니다.

여기서 1편의 결정이 눈에 보입니다. 게시판이 서른 개가 되어도 이 그림은 그대로입니다. 늘어나는 건 boards 테이블의 행이지 테이블이 아닙니다.

boards 가 컬럼이 많은 이유

boards 는 컬럼이 스무 개가 넘습니다. 처음 보면 과해 보입니다.

slug  name  description  skin  per_page  categories  field_schema
use_comment  use_file  use_secret  use_editor
max_files  max_file_size  allowed_ext
read_level  write_level  comment_level  download_level
sort_order  is_active  created_at  updated_at

이 테이블은 데이터를 담는 곳이 아니라 설정을 담는 곳입니다. 다른 프로젝트라면 config/board.php 같은 파일에 들어갔을 값들인데, 우리는 그걸 관리자가 화면에서 바꿀 수 있어야 하니 DB로 내려온 겁니다.

행 수를 생각해 보면 마음이 편해집니다. boards 는 많아야 수십 행입니다. 컬럼이 스물다섯 개든 마흔 개든 성능에 영향이 없고, 대신 게시판 하나를 읽으면 그 게시판에 관한 모든 것이 한 번에 손에 들어옵니다. 조인이 없습니다.

넓은 게 이득이 되는 흔치 않은 경우입니다. 각 컬럼이 왜 필요한지는 다음 편에서 하나씩 짚습니다.

DB 정규화 이론을 벗어난 세 군데

정규화는 기본값입니다. 벗어날 때는 이유가 있어야 하고, 그 이유는 적어둬야 합니다. 우리가 벗어나는 곳은 세 군데입니다.

boards.categories 와 boards.field_schema 를 JSON 으로

정석대로라면 테이블을 나눕니다.

board_categories (id, board_id, name, sort_order)
board_fields     (id, board_id, key, label, type, options, required, ...)

우리는 boards 안의 JSON 컬럼 두 개로 갑니다. 근거는 셋입니다.

항상 통째로 읽고 통째로 씁니다. "3번 게시판의 카테고리 중 두 번째 것만" 같은 조회를 할 일이 없습니다. 게시판을 열면 카테고리 전체가 필요하고, 관리자가 저장하면 전체가 새로 저장됩니다. 이런 데이터는 나눠 둘 이유가 약합니다.

순서가 의미를 가집니다. 폼에 칸이 나오는 순서, 카테고리가 보이는 순서가 곧 관리자가 정한 순서입니다. 테이블로 나누면 sort_order 컬럼을 두고 드래그로 순서를 바꿀 때마다 여러 행을 갱신해야 합니다. 배열은 그냥 순서대로 들어 있습니다.

게시판 설정은 캐시 대상입니다. BoardService 를 만들면서 게시판 설정을 캐시에 올립니다. 한 행을 읽어 캐시에 넣는 것과, 세 테이블을 조인해 조립한 뒤 캐시에 넣는 것은 코드 분량이 다릅니다.

반대 방향의 대가도 분명합니다. field_schema 의 type 에 오타가 들어가도 DB는 막아주지 않습니다. 테이블이었다면 ENUM 이 걸러줬을 겁니다. 그래서 19편에서 DTO 클래스를 만들어 애플리케이션 층에서 검증합니다. DB가 안 지켜주는 규칙은 코드가 지켜야 합니다.

posts.category 가 FK 가 아니다

posts.category 는 VARCHAR(50) 입니다. boards.categories 안의 한 값을 문자열로 그냥 들고 있습니다. JSON 배열 안의 원소를 FK 로 걸 방법이 없기도 하고, 걸 생각도 없습니다.

관리자가 카테고리 이름을 "질문" 에서 "문의" 로 바꾸면 어떻게 될까요. 기존 글은 category = '질문' 인 채로 남습니다. 이건 버그가 아니라 선택입니다. 카테고리를 바꿨다고 과거 글의 분류가 소급해서 바뀌는 편이 늘 옳은 것도 아니기 때문입니다. 게시판 관리 화면에서 "이름만 바꾸기 / 기존 글도 함께 옮기기" 를 선택하게 해서 해결합니다.

카운터 네 개를 왜 컬럼으로 들고 있는가

posts 에는 세는 컬럼이 넷 있습니다.

view_count  comment_count  like_count  file_count

comment_count 는 comments 를 세면 나오는 값입니다. like_count 는 post_likes 를, file_count 는 attachments 를 세면 나옵니다. 이미 있는 데이터를 중복해서 들고 있는 것이고, 정규화 관점에서는 그냥 틀린 설계입니다.

그런데 목록 화면을 떠올려 보면 사정이 달라집니다. 게시판 목록은 한 페이지에 스무 건이고, 각 줄에 댓글 수가 붙습니다.

-- 카운터가 없다면
SELECT p.*, (SELECT COUNT(*) FROM comments c WHERE c.post_id = p.id) AS comment_count
FROM posts p
WHERE p.board_id = 1 AND p.deleted_at IS NULL
ORDER BY p.is_notice DESC, p.id DESC
LIMIT 20;

목록을 한 번 열 때마다 comments 를 스무 번 셉니다. 좋아요 수와 첨부 수까지 보여주려면 예순 번이 됩니다. 조인과 GROUP BY 로 바꿔도 본질은 같습니다 — 화면에 스무 줄 뿌리자고 다른 테이블 세 개를 집계합니다.

카운터 컬럼이 있으면 이 전부가 사라집니다. 목록 쿼리는 posts 한 테이블만 봅니다.

그래서 무엇을 내주는가. 정합성입니다. 댓글을 지웠는데 comment_count 를 안 줄이는 코드 경로가 하나라도 있으면 숫자가 틀어집니다. 그리고 그런 경로는 반드시 생깁니다 — 관리자 일괄 삭제, 글 삭제에 딸린 댓글 정리, 트랜잭션 중간의 예외.

그래서 이 강좌는 카운터를 이렇게 다룹니다.

카운터는 캐시다. 원본은 언제나 comments, post_likes, attachments 쪽에 있다. 캐시는 언젠가 틀어진다. 틀어졌을 때 다시 세는 방법을 같이 만들어 둔다.

댓글을 붙일 때 증감 코드를 넣고, 같은 편에서 전체를 다시 세어 맞추는 spark 명령을 함께 만듭니다. 재계산 수단이 있으면 카운터는 안전한 최적화가 됩니다. 없으면 나중에 문제가 되기 때문 입니다.

view_count 는 성격이 조금 다릅니다. 셀 원본이 아예 없어서(조회 이력 테이블을 두지 않습니다) 이 컬럼이 곧 원본입니다. 대신 글을 열 때마다 UPDATE 가 날아가므로 새로고침 연타에 대한 방어가 필요한데, 나중에 다시 다룹니다.

인덱스도 지금 정한다

posts 에 인덱스 셋을 겁니다.

KEY idx_board_list    (board_id, deleted_at, is_notice, id)
KEY idx_board_created (board_id, created_at)
KEY idx_user          (user_id, id)

첫 번째가 중요합니다. 이 강좌에서 가장 자주 실행될 쿼리 하나를 위해 만든 인덱스입니다.

WHERE board_id = ? AND deleted_at IS NULL
ORDER BY is_notice DESC, id DESC

컬럼 순서가 쿼리 모양을 그대로 따라갑니다. 같음 비교로 범위를 좁히는 컬럼(board_id, deleted_at)을 앞에, 정렬에 쓰는 컬럼(is_notice, id)을 뒤에. 이 순서가 어긋나면 인덱스로 행을 찾고 나서 정렬을 따로 해야 합니다(Using filesort).

ORDER BY 는 DESC 인데 인덱스에는 DESC 를 안 붙였습니다. InnoDB 는 인덱스를 역방향으로도 읽을 수 있어서 오름차순 인덱스로 충분합니다. (초안에서는 DESC 인덱스를 넣었다가, 실제로 EXPLAIN 을 찍어보고 뺐습니다.)

idx_board_created 는 기간 조회용, idx_user 는 "내가 쓴 글" 용입니다.

인덱스를 이 시점에 확정하는 이유도 테이블을 한번에 생성하는 이유와 같습니다. InnoDB 는 보조 인덱스를 만드는 동안에도 읽기·쓰기를 받아주지만 공짜는 아닙니다. 테이블이 커진 뒤에 하면 DDL 시간과 디스크, 메타데이터 잠금 대기를 감수해야 합니다. 처음부터 CREATE TABLE 안에 들어 있으면 그런 일이 없습니다.

FULLTEXT 인덱스는 넣지 않습니다. 한글 검색을 나중에 다루는데, 결론부터 말하면 이 강좌의 본선 구현은 LIKE 입니다. FULLTEXT 가 한글에서 어떻게 어긋나는지는 그 편에서 실측으로 보여드립니다.

지금은 없는 컬럼 두 개

설계도에는 있지만 마이그레이션에는 들어가지 않는 컬럼이 둘 있습니다.

1편에서 세 번째 길을 고를 때 근거로 든 바로 그 두 컬럼입니다. 이게 없는 프로젝트는 "게시판을 여러 개 만들 수 있는 평범한 게시판" 입니다. extra 가 붙는 순간부터 플랫폼이 됩니다.

미루는 이유는 순전히 강좌의 순서 때문입니다. JSON 컬럼과 동적 폼은 이 강좌에서 가장 무거운 주제라, 라우팅·목록·상세·권한이 다 돌아가는 상태에서 시작해야 그 편에 집중할 수 있습니다.

그러니 진행하다가 "커스텀 필드는 언제 나오나" 싶을 때, 이 표를 떠올리시면 됩니다. 빠뜨린 게 아니라 순서입니다.

지울 때 어떻게 되는가

관계를 그렸으면 끊어질 때를 같이 정해야 합니다. 이걸 안 정해두면 나중에 "회원 탈퇴시켰더니 게시판 글이 통째로 사라졌다" 같은 사고가 납니다.

가운데 줄이 핵심입니다. posts.user_id 는 NULL 을 허용하는데, 이 NULL 이 두 가지를 동시에 뜻합니다 — 비회원이 쓴 글이거나, 작성자가 탈퇴한 글이거나. 두 경우 모두 화면에는 guest_name 이나 "탈퇴한 회원" 으로 표시하면 되므로 굳이 구분하지 않습니다. comments.user_id 도 같은 규칙을 따릅니다.

소프트 딜리트는 posts 와 comments 에만 겁니다. attachments 는 실제 파일을 함께 지워야 해서 되살릴 수 없고, 되살릴 수 없는 것에 deleted_at 을 두면 "지운 것 같은데 디스크는 안 줄어드는" 상태만 남습니다.

여기서 숙제 하나가 미리 보입니다. 글을 소프트 딜리트하면 그 글의 댓글은 어떻게 될까요? 그리고 게시판의 글 수 카운터는? — 카운터가 캐시라는 이야기가 여기서 다시 나옵니다.

확인하기

이번 편은 코드가 없으니 체크리스트도 질문으로 대신합니다. 다음 문장들에 답할 수 있으면 다음 편으로 넘어가셔도 좋습니다.

• 테이블 여덟 개의 이름과, 그중 축이 되는 셋을 말할 수 있다
• boards 의 컬럼이 스무 개가 넘는데도 괜찮은 이유를 설명할 수 있다
• comment_count 를 두면 무엇을 얻고 무엇을 내주는지 말할 수 있다
• idx_board_list 의 컬럼 순서가 왜 그 순서인지 설명할 수 있다
• 회원을 지웠을 때 그 사람의 글이 어떻게 되는지 안다

전체 스키마의 컬럼 단위 정의는 저장소의 CURRICULUM.md 3장에 있습니다. 다음 편부터는 이 문서를 옆에 두고 진행합니다.

마무리

두 편 연속으로 코드를 쓰지 않았습니다. 슬슬 답답하실 텐데, 이번 편에 그린 그림이 앞으로 서른네 편을 지탱합니다.

설계도를 미리 그리는 진짜 이득은 "나중에 안 고쳐도 된다" 가 아닙니다. 어차피 고칠 일은 생깁니다. 이득은 고칠 때 무엇이 함께 움직이는지 볼 수 있다는 데 있습니다. comments 에 컬럼 하나를 더할 때 그게 카운터와 소프트 딜리트와 목록 인덱스에 어떻게 닿는지를, 지도가 없으면 알 수 없습니다.

다음 회차에서는 드디어 마이그레이션을 씁니다. users 와 boards 두 개를 만들고, boards 의 컬럼 스무 개가 각각 왜 필요한지를 하나씩 짚습니다.