[SECTION04. 게시판 스키마 직접 설계하기 (실습)]
학습 목표
- 요구사항을 분석해서 필요한 테이블을 스스로 도출할 수 있다.
- 테이블 간의 관계(1:N, N:M)를 파악하고 FK로 설계할 수 있다.
- 지금까지 배운 데이터 타입과 제약조건을 종합적으로 적용할 수 있다.
- 직접 작성한 SQL로 게시판 전체 스키마를 H2에 구축한다.
오늘 만들 게시판의 요구사항
다음 요구사항을 만족하는 게시판을 설계합니다.
- 회원은 이름, 이메일, 비밀번호, 가입일을 가진다. 이메일은 중복될 수 없다.
- 게시글은 제목, 내용, 조회수, 작성일을 가지며, 반드시 작성자(회원)가 있어야 한다.
- 게시글은 카테고리(예: 자유게시판, 공지사항, 질문)로 구분된다.
- 댓글은 내용, 작성일을 가지며, 특정 게시글과 특정 회원에 연결된다.
- 회원은 게시글에 좋아요를 누를 수 있다. 단, 같은 게시글에 좋아요는 한 번만 누를 수 있다.
요구사항 분석 — 어떤 테이블이 필요한가?
요구사항 문장에서 명사를 뽑아보면 테이블의 후보가 보입니다.
| 요구사항 속 명사 | 테이블 후보 |
|---|---|
| 회원 | member |
| 게시글 | board |
| 카테고리 | category |
| 댓글 | comment |
| 좋아요 | board_like |
각 테이블이 서로 어떤 관계인지 정리합니다.
| 관계 | 설명 |
|---|---|
| member : board = 1 : N | 한 회원은 여러 게시글을 쓸 수 있음 |
| category : board = 1 : N | 한 카테고리는 여러 게시글을 가질 수 있음 |
| board : comment = 1 : N | 한 게시글에 여러 댓글이 달릴 수 있음 |
| member : comment = 1 : N | 한 회원은 여러 댓글을 쓸 수 있음 |
| member : board (좋아요) = N : N | 한 회원은 여러 게시글에 좋아요를 누르고, 한 게시글은 여러 회원에게 좋아요를 받음 |
N:M(다대다) 관계는 테이블 하나로 표현할 수 없습니다. 중간에 연결 테이블(
board_like)을 두어 1:N + N:1 구조로 풀어내야 합니다. 이번 게시판에서는board_like가 바로 그 연결 테이블입니다.
텍스트로 나타내기
member (1) ----< (N) board
member (1) ----< (N) comment
category (1) ----< (N) board
board (1) ----< (N) comment
member (N) ----< board_like >---- (N) board코드를 작성하기 전에 이렇게 관계를 먼저 그려보는 습관이 중요합니다. 손으로 그리든, 종이에 쓰든, 표로 정리하든 상관없습니다. 설계를 먼저 끝내고 SQL은 그 다음입니다.
CREATE TABLE MEMBER (
member_id BIGINT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
password VARCHAR(255) NOT NULL,
email VARCHAR(100) NOT NULL,
created_at DATETIME NOT NULL
);
CREATE TABLE CATEGORY (
category_id BIGINT PRIMARY KEY,
category_name VARCHAR(50) NOT NULL
);
CREATE TABLE BOARD (
board_id BIGINT PRIMARY KEY,
member_id BIGINT NOT NULL,
category_id BIGINT NOT NULL,
title VARCHAR(200) NOT NULL,
content TEXT NOT NULL,
view_count INT DEFAULT 0,
created_at DATETIME NOT NULL,
FOREIGN KEY (member_id) REFERENCES MEMBER (member_id),
FOREIGN KEY (category_id) REFERENCES CATEGORY (category_id)
);
CREATE TABLE COMMENT (
comment_id BIGINT PRIMARY KEY,
board_id BIGINT NOT NULL,
member_id BIGINT NOT NULL,
content TEXT NOT NULL,
created_at DATETIME NOT NULL,
FOREIGN KEY (board_id) REFERENCES BOARD (board_id),
FOREIGN KEY (member_id) REFERENCES MEMBER (member_id)
);
CREATE TABLE BOARD_LIKE (
board_id BIGINT NOT NULL,
member_id BIGINT NOT NULL,
created_at DATETIME NOT NULL,
PRIMARY KEY (board_id, member_id),
FOREIGN KEY (board_id) REFERENCES BOARD (board_id),
FOREIGN KEY (member_id) REFERENCES MEMBER (member_id)
);컬럼까지 포함한 설계표
member
| 컬럼명 | 타입 | 제약조건 |
|---|---|---|
| id | BIGINT | PK, AUTO_INCREMENT |
| name | VARCHAR(50) | NOT NULL |
| VARCHAR(100) | NOT NULL, UNIQUE | |
| password | VARCHAR(100) | NOT NULL |
| created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP |
category
| 컬럼명 | 타입 | 제약조건 |
|---|---|---|
| id | BIGINT | PK, AUTO_INCREMENT |
| name | VARCHAR(50) | NOT NULL, UNIQUE |
board
| 컬럼명 | 타입 | 제약조건 |
|---|---|---|
| id | BIGINT | PK, AUTO_INCREMENT |
| title | VARCHAR(200) | NOT NULL |
| content | TEXT | NOT NULL |
| view_count | INT | DEFAULT 0 |
| member_id | BIGINT | NOT NULL, FK → member(id) |
| category_id | BIGINT | NOT NULL, FK → category(id) |
| created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP |
comment
| 컬럼명 | 타입 | 제약조건 |
|---|---|---|
| id | BIGINT | PK, AUTO_INCREMENT |
| content | VARCHAR(500) | NOT NULL |
| board_id | BIGINT | NOT NULL, FK → board(id) |
| member_id | BIGINT | NOT NULL, FK → member(id) |
| created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP |
board_like
| 컬럼명 | 타입 | 제약조건 |
|---|---|---|
| board_id | BIGINT | PK(복합), FK → board(id) |
| member_id | BIGINT | PK(복합), FK → member(id) |
| created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP |
정리
- 요구사항에서 명사를 뽑아내면 테이블 후보가 보인다.
- 테이블 간 관계는 1:N, N:M으로 분류하고, N:M은 연결 테이블로 풀어낸다.
- SQL을 작성하기 전에 ERD(관계도)와 컬럼 설계표를 먼저 만든다.
- 테이블 생성 순서는 참조되는 테이블(부모) → 참조하는 테이블(자식) 순이다.
SQL 연습 문제
1. 위 설계표를 참고하여 member 테이블을 생성하는 SQL을 작성하시오.
CREATE TABLE member (
id BIGINT AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
password VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT pk_member PRIMARY KEY (id),
CONSTRAINT uq_member_email UNIQUE (email)
);2. category 테이블을 생성하는 SQL을 작성하시오. (name은 중복 불가)
CREATE TABLE category (
id BIGINT AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
CONSTRAINT pk_category PRIMARY KEY (id),
CONSTRAINT uq_category_name UNIQUE (name)
);3. board 테이블을 생성하는 SQL을 작성하라. member와 category를 모두 FK로 참조해야 한다.
CREATE TABLE board (
id BIGINT AUTO_INCREMENT,
title VARCHAR(200) NOT NULL,
content TEXT NOT NULL,
view_count INT DEFAULT 0,
member_id BIGINT NOT NULL,
category_id BIGINT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT pk_board PRIMARY KEY (id),
CONSTRAINT fk_board_member FOREIGN KEY (member_id) REFERENCES member(id),
CONSTRAINT fk_board_category FOREIGN KEY (category_id) REFERENCES category(id)
);4. comment 테이블을 생성하는 SQL을 작성하라. board와 member를 모두 FK로 참조해야 한다.
CREATE TABLE comment (
id BIGINT AUTO_INCREMENT,
content VARCHAR(500) NOT NULL,
board_id BIGINT NOT NULL,
member_id BIGINT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT pk_comment PRIMARY KEY (id),
CONSTRAINT fk_comment_board FOREIGN KEY (board_id) REFERENCES board(id),
CONSTRAINT fk_comment_member FOREIGN KEY (member_id) REFERENCES member(id)
);5. board_like 테이블을 생성하는 SQL을 작성하시오. 단, 같은 회원이 같은 게시글에 좋아요를 중복으로 누를 수 없도록 복합키를 사용해야 한다.
CREATE TABLE board_like (
board_id BIGINT NOT NULL,
member_id BIGINT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT pk_board_like PRIMARY KEY (board_id, member_id),
CONSTRAINT fk_board_like_board FOREIGN KEY (board_id) REFERENCES board(id),
CONSTRAINT fk_board_like_member FOREIGN KEY (member_id) REFERENCES member(id)
);6. board 테이블에서 1번 게시글의 view_count를 1 증가시키는 SQL을 작성하시오.
UPDATE board
SET view_count = view_count + 1
WHERE id = 1;[SECTION05. DML — INSERT, UPDATE, DELETE]
- DML(Data Manipulation Language) = 테이블 안에 들어있는 데이터의 내용을 다루는 명령어
| 명령어 | 역할 |
|---|---|
| INSERT | 새로운 데이터(행) 추가 |
| UPDATE | 기존 데이터 수정 |
| DELETE | 기존 데이터 삭제 |
정리
- INSERT는 새로운 행을 추가하고, 컬럼명을 명시하는 방식을 권장한다.
- UPDATE는 기존 값을 기반으로 계산해서 수정할 수도 있다. (
view_count = view_count + 1) - UPDATE/DELETE에서 WHERE를 빠뜨리면 테이블 전체가 영향을 받으므로 항상 주의해야 한다.
- FK로 참조되고 있는 데이터는 참조하는 쪽을 먼저 삭제해야 한다.
- UPDATE/DELETE 전에 같은 조건으로 SELECT를 먼저 실행해서 확인하는 습관이 중요하다.
SQL 연습 문제
1. category 테이블에 '질문'이라는 이름의 카테고리를 등록하는 SQL을 작성하시오.
INSERT INTO category (name) VALUES ('질문');2. member 테이블에 이름 '한코딩', 이메일 'han@test.com', 비밀번호 '1234'인 회원을 등록하는 SQL을 작성하시오.
INSERT INTO member (name, email, password) VALUES ('한코딩', 'han@test.com', '1234');3. board 테이블에서 id가 2인 게시글의 제목을 '공지: 점검 안내'로 수정하는 SQL을 작성하시오.
UPDATE board
SET title = '공지: 점검 안내'
WHERE id = 2;4. board 테이블에서 id가 1인 게시글의 view_count를 5만큼 증가시키는 SQL을 작성하시오.
UPDATE board
SET view_count = view_count + 5
WHERE id = 1;5. member_id가 6인 회원이 작성한 모든 게시글의 category_id를 2로 변경하는 SQL을 작성하시오.
UPDATE board
SET category_id = 2
WHERE member_id = 6;6. comment 테이블에서 board_id가 3인 댓글을 모두 삭제하는 SQL을 작성하시오.
DELETE FROM comment
WHERE board_id = 3;7. board_id가 4인 게시글을 삭제하려고 한다. 이 게시글에는 댓글과 좋아요가 모두 달려있다. 에러 없이 삭제하기 위한 SQL 3줄을 순서대로 작성하시오.
DELETE FROM board_like WHERE board_id = 4;
DELETE FROM comment WHERE board_id = 4;
DELETE FROM board WHERE id = 4;