[Node.js / Express 5] Database 설계 기초

Study/Node.JS

[Node.js / Express 5] Database 설계 기초

the.Dev.Cat 2026. 3. 25. 12:50

Database 설계 기초

SQL vs NoSQL

관계형 데이터베이스(SQL)와 비관계형 데이터베이스(NoSQL)의 차이는 단순히 문법이 아니다. 데이터를 어떤 방식으로 저장하고 조회하느냐의 차이다.

구분	SQL (관계형)	NoSQL (비관계형)
스키마	사전 정의 필수	유연 (없거나 동적)
데이터 구조	테이블 (행/열)	key-value, document, graph 등
관계 표현	외래키(FK)	중첩 구조 또는 참조
일관성	강함 (ACID 보장)	최종 일관성 (BASE: Basically Available, Soft state, Eventually consistent)
확장 방식	수직 확장 (Scale Up)	수평 확장 (Scale Out)
트랜잭션	완전 지원	제한적
조인	JOIN으로 테이블 결합	조인 미지원, 애플리케이션 레벨에서 처리
적합한 데이터	정형, 관계 복잡할 때	반정형/비정형, 대용량
사용 사례	금융, 결제, ERP, 회원 관리	캐싱(Redis), 로그 수집, 실시간 채팅, IoT

선택 기준은 간단하다. 금융/결제처럼 데이터 정확도가 핵심이거나 테이블 간 관계가 복잡하다면 SQL이 적합하다. 실시간 대용량 처리나 스키마가 자주 변경되는 경우, 또는 캐싱(Redis)이나 로그 저장에는 NoSQL이 더 맞다.

MySQL vs PostgreSQL vs Oracle

	MySQL	PostgreSQL	Oracle
비용	무료 (오픈소스)	무료 (오픈소스)	유료 (매우 비쌈)
조인 방식	NL Join만	NL/Hash/Sort 등	다양한 방식
UPDATE 성능	빠름	느림 (삭제+재삽입 방식)	빠름
복잡한 쿼리	약함	강함	매우 강함
JSON 지원	기본	강력 (jsonb)	지원
주 사용처	웹 앱, 스타트업	대규모 DB, 분석	대기업 시스템

이 시리즈는 MySQL 기준으로 진행한다.

데이터 모델링 4단계

데이터 모델링은 실제 SQL을 작성하기 전에 데이터 구조를 설계하는 과정이다. 4단계로 진행한다.

업무 파악 — 어떤 데이터가 필요한지 분석한다. PM, 기획자와 소통하는 단계다.
개념적 모델링 — 데이터 간 관계를 ERD로 구상한다. 엔티티와 관계만 표현하며 타입은 명시하지 않는다.
논리적 모델링 — 개념도에 데이터 타입, 키를 명시한다. 정규화를 적용하는 단계다.
물리적 모델링 — 실제 SQL로 테이블을 생성한다. CREATE TABLE을 작성한다.

ERD (Entity-Relationship Diagram)

ERD는 데이터 모델을 시각적으로 표현하는 다이어그램이다.

ERD 실전 도구

ERD를 직접 그릴 수 있는 도구는 여러 가지가 있다.

도구	특징	비용
ERDCloud	웹 기반, 한국어 지원, 팀 공유 가능, SQL 자동 생성	무료
dbdiagram.io	코드 기반 ERD 작성, DBML 문법, 깔끔한 UI	무료 (개인)
MySQL Workbench	MySQL 공식 도구, Forward/Reverse Engineering 지원	무료

팀 프로젝트에서 빠르게 공유하고 싶다면 ERDCloud가 편하고, 코드처럼 버전 관리를 하고 싶다면 dbdiagram.io가 적합하다. MySQL Workbench는 실제 DB에서 ERD를 역추출(Reverse Engineering)할 수 있어서 기존 DB를 시각화할 때 유용하다.

Crow's Foot 표기법

기호	의미	예시
`─\|─`	정확히 1 (필수)	회원은 반드시 1개의 계정
`─O─`	0 또는 1 (선택)	회원은 프로필이 없을 수도 있음
`─<─`	여러 개 (Many)	카테고리에 책 여러 권
`─\|<─`	1 이상 (필수 다수)	주문에는 반드시 1개 이상 상품
`─O<─`	0 이상 (선택 다수)	회원에게 0개 이상 대여 이력

관계 유형

-- 1:1 관계
MEMBER ─|──|─ MEMBER_PROFILE
회원 1명 <-> 프로필 정확히 1개

-- 1:N 관계 (가장 흔한 패턴)
CATEGORY ─|──O<─ BOOK
카테고리 1개 <- 책 0개 이상

-- N:M 관계 (매핑 테이블 필수)
MEMBER ─|──O<─ MEMBER_BOOK ─>O──|─ BOOK
회원 여러 명 <-> 책 여러 권

식별 관계 vs 비식별 관계

식별 관계 (실선): 자식 테이블이 부모의 PK를 자신의 PK로 사용한다. 예를 들어 주문상세 테이블에서 (order_id, product_id) 복합 PK를 사용하는 경우다.

비식별 관계 (점선): 부모의 PK를 일반 FK로만 사용한다. 실무에서 대부분의 관계는 비식별이다. 예를 들어 book 테이블에 member_id FK를 두는 방식이다.

테이블 설계 실무 규칙

네이밍 규칙

테이블명 : member, book, book_likes    -- 소문자 + snake_case
칼럼명   : created_at, user_id         -- 소문자 + snake_case
PK 칼럼  : id                          -- book_id, member_id 아님

PK 설계

id BIGINT AUTO_INCREMENT PRIMARY KEY

INT의 최대값은 약 21억이다. 서비스가 커지면 부족해질 수 있으므로 약 922경까지 수용하는 BIGINT를 사용한다.

시간 칼럼

모든 테이블에 created_at과 updated_at을 추가한다.

created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
           ON UPDATE CURRENT_TIMESTAMP(6)

DATETIME(6)의 (6)은 소수점 6자리, 즉 마이크로초까지 저장한다는 뜻이다. 초 단위만 저장하면 동시에 생성된 행들의 최신순 정렬이 불가능해진다. 1초에 여러 건의 데이터가 삽입되는 상황에서 created_at으로 정렬이 제대로 작동하려면 마이크로초까지 필요하다.

CHAR vs VARCHAR vs TEXT

타입	저장 방식	최대 길이	인덱스	적합한 용도
`CHAR(n)`	고정 길이 (항상 n바이트 차지)	255	가능	국가코드(`KR`), 성별(`M`/`F`)
`VARCHAR(n)`	가변 길이 (실제 데이터 + 1~2바이트)	65,535	가능	이름, 이메일, URL
`TEXT`	가변 길이 (별도 저장 영역)	65,535	불가 (prefix 인덱스만 가능)	본문, 설명 등 긴 텍스트

CHAR는 길이가 일정한 값에 쓴다. 2글자짜리 국가코드를 VARCHAR(2)로 선언해도 작동하지만, CHAR(2)가 저장/비교 시 더 효율적이다. TEXT는 전체 칼럼에 대한 인덱스를 걸 수 없으므로, 검색이 필요한 칼럼이라면 VARCHAR를 사용하는 것이 낫다.

MySQL 주요 데이터 타입

데이터 종류	권장 타입	설명
짧은 문자열	`VARCHAR(n)`	최대 n글자, 유니코드 기준
긴 텍스트	`TEXT`	길이 제한 없음
정수 (PK, FK)	`BIGINT`	대용량 서비스 대비
불리언	`TINYINT(1)`	0/1로 관리
날짜+시간	`DATETIME(6)`	마이크로초까지 저장
상태값	`VARCHAR(15)`	'ACTIVE', 'INACTIVE'처럼 문자열로
가격, 금액	`DECIMAL(10, 2)`	부동소수점 오차 방지

인덱스 기본 개념

인덱스는 테이블의 특정 칼럼에 대한 검색 속도를 높이는 자료구조다. MySQL의 InnoDB 엔진은 기본적으로 B-Tree 구조의 인덱스를 사용한다. B-Tree는 데이터가 정렬된 트리 구조로, 전체 테이블을 순회하지 않고도 원하는 행을 빠르게 찾을 수 있다.

PK 칼럼에는 자동으로 인덱스가 생성된다(클러스터드 인덱스). UNIQUE 제약 조건이 걸린 칼럼에도 자동으로 인덱스가 만들어진다. 그 외에 WHERE, JOIN, ORDER BY에서 자주 사용하는 칼럼에는 직접 인덱스를 추가하는 것이 좋다.

CREATE INDEX idx_member_email ON member(email);

CREATE INDEX idx_book_category ON book(category_id);

CREATE INDEX idx_notification_member_read ON notification(member_id, is_read);

인덱스는 SELECT 성능을 크게 개선하지만, INSERT와 UPDATE 시에는 인덱스도 함께 갱신해야 하므로 쓰기 성능이 저하된다. 모든 칼럼에 무조건 인덱스를 거는 것이 아니라, 조회 패턴을 분석한 뒤 필요한 곳에만 추가해야 한다.

연관관계 설계 패턴

1:1 관계

1:1 관계에서 FK는 주 테이블이 아닌 종속 테이블에 배치한다. 회원(member)과 프로필(member_profile)의 관계라면 FK는 member_profile에 둔다. FK 칼럼에 UNIQUE 제약 조건을 걸어야 1:1이 보장된다. UNIQUE가 없으면 1:N이 되어버린다.

CREATE TABLE member_profile (
  id        BIGINT AUTO_INCREMENT PRIMARY KEY,
  member_id BIGINT NOT NULL,
  bio       VARCHAR(500),
  avatar    VARCHAR(255),
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
             ON UPDATE CURRENT_TIMESTAMP(6),
  UNIQUE KEY uk_member_id (member_id),
  FOREIGN KEY (member_id) REFERENCES member(id)
);

1:N 관계

FK는 항상 N쪽 테이블에 배치한다. 카테고리(1)와 책(N)의 관계라면 FK는 book 테이블에 있다.

CREATE TABLE category (
  id   BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
);

CREATE TABLE book (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  category_id BIGINT NOT NULL,
  title       VARCHAR(100) NOT NULL,
  description TEXT,
  created_at  DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at  DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
              ON UPDATE CURRENT_TIMESTAMP(6),
  FOREIGN KEY (category_id) REFERENCES category(id)
);

N:M 관계 — 매핑 테이블 필수

N:M 관계는 직접 FK를 걸 수 없다. 반드시 중간 매핑 테이블을 만들어야 한다. 매핑 테이블은 양쪽의 PK를 FK로 가지며, 각각과 1:N 관계를 형성한다.

CREATE TABLE member_book (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  member_id   BIGINT NOT NULL,
  book_id     BIGINT NOT NULL,
  rental_date DATETIME(6) NOT NULL,
  return_date DATETIME(6),
  created_at  DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  FOREIGN KEY (member_id) REFERENCES member(id),
  FOREIGN KEY (book_id)   REFERENCES book(id)
);

CREATE TABLE book_likes (
  id         BIGINT AUTO_INCREMENT PRIMARY KEY,
  member_id  BIGINT NOT NULL,
  book_id    BIGINT NOT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  FOREIGN KEY (member_id) REFERENCES member(id),
  FOREIGN KEY (book_id)   REFERENCES book(id)
);

Soft Delete vs Hard Delete

실무에서는 Hard Delete(실제 삭제)보다 Soft Delete(상태 변경)를 권장한다.

	Hard Delete	Soft Delete
복구 가능 여부	불가	가능
집계 쿼리 영향	데이터가 사라져 집계 깨짐	데이터 보존, 조건으로 제외
추가 기능 대응	어려움	`WHERE deleted_at IS NULL`로 쉽게 대응
HTTP 메서드	DELETE	PATCH

Soft Delete를 구현하는 방식은 크게 두 가지다. status 칼럼으로 관리하는 방식과 deleted_at 칼럼으로 관리하는 방식이다.

status 칼럼 방식은 상태가 여러 개(ACTIVE, INACTIVE, SUSPENDED 등)일 때 적합하다.

CREATE TABLE member (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(20) NOT NULL,
  email         VARCHAR(50) NOT NULL,
  status        VARCHAR(15) NOT NULL DEFAULT 'ACTIVE',
  inactive_date DATETIME(6),
  created_at    DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at    DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
                ON UPDATE CURRENT_TIMESTAMP(6)
);

UPDATE member
SET status = 'INACTIVE', inactive_date = NOW(6)
WHERE id = 1;

DELETE FROM member
WHERE status = 'INACTIVE'
  AND inactive_date < DATE_SUB(NOW(), INTERVAL 7 DAY);

deleted_at 칼럼 방식은 단순히 "삭제 여부"만 판단하면 될 때 더 깔끔하다. deleted_at이 NULL이면 활성 데이터, 값이 있으면 삭제된 데이터다.

CREATE TABLE post (
  id         BIGINT AUTO_INCREMENT PRIMARY KEY,
  member_id  BIGINT NOT NULL,
  title      VARCHAR(200) NOT NULL,
  content    TEXT NOT NULL,
  deleted_at DATETIME(6) NULL DEFAULT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
             ON UPDATE CURRENT_TIMESTAMP(6),
  FOREIGN KEY (member_id) REFERENCES member(id)
);

UPDATE post SET deleted_at = NOW(6) WHERE id = 1;

SELECT * FROM post WHERE deleted_at IS NULL;

DELETE FROM post
WHERE deleted_at IS NOT NULL
  AND deleted_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

deleted_at 패턴의 장점은 삭제 시점이 기록된다는 것이다. 별도의 status 칼럼 없이도 WHERE deleted_at IS NULL 한 줄로 활성 데이터만 조회할 수 있다. Prisma에서도 미들웨어로 자동 필터링을 구현할 수 있어서 ORM과 궁합이 좋다.

CREATE TABLE 실전 예시

실무에서 자주 쓰는 제약 조건들을 포함한 테이블 생성 예시다. UNIQUE KEY, DATETIME(6), ON UPDATE CURRENT_TIMESTAMP을 실제로 어떻게 조합하는지 확인한다.

CREATE TABLE member (
  id         BIGINT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(20)  NOT NULL,
  email      VARCHAR(100) NOT NULL,
  nickname   VARCHAR(30)  NOT NULL,
  phone      CHAR(11),
  status     VARCHAR(15)  NOT NULL DEFAULT 'ACTIVE',
  deleted_at DATETIME(6)  NULL DEFAULT NULL,
  created_at DATETIME(6)  NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6)  NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
             ON UPDATE CURRENT_TIMESTAMP(6),
  UNIQUE KEY uk_email (email),
  UNIQUE KEY uk_nickname (nickname)
);

CREATE TABLE store (
  id         BIGINT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(50) NOT NULL,
  address    VARCHAR(200) NOT NULL,
  region_id  BIGINT NOT NULL,
  score      DECIMAL(2, 1) NOT NULL DEFAULT 0.0,
  deleted_at DATETIME(6) NULL DEFAULT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
             ON UPDATE CURRENT_TIMESTAMP(6),
  FOREIGN KEY (region_id) REFERENCES region(id)
);

CREATE TABLE review (
  id         BIGINT AUTO_INCREMENT PRIMARY KEY,
  member_id  BIGINT NOT NULL,
  store_id   BIGINT NOT NULL,
  content    TEXT NOT NULL,
  score      DECIMAL(2, 1) NOT NULL,
  deleted_at DATETIME(6) NULL DEFAULT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
             ON UPDATE CURRENT_TIMESTAMP(6),
  FOREIGN KEY (member_id) REFERENCES member(id),
  FOREIGN KEY (store_id) REFERENCES store(id)
);

UNIQUE KEY는 해당 칼럼의 값이 테이블 전체에서 중복되지 않도록 보장한다. email이나 nickname처럼 사용자마다 고유해야 하는 값에 건다. ON UPDATE CURRENT_TIMESTAMP(6)은 행이 수정될 때 updated_at을 자동으로 현재 시간으로 갱신한다. 애플리케이션 코드에서 직접 시간을 넣지 않아도 된다.

알림 테이블 설계 패턴

알림 기능을 설계할 때 자주 쓰이는 패턴 3가지가 있다.

패턴	구조	장점	단점	추천 상황
슈퍼/서브타입	공통 테이블 + 종류별 분리	정규화, 속성 명확	JOIN 필요	속성 차이가 클 때
단일 테이블 + dtype	하나의 테이블, dtype 칼럼	단순, 조회 쉬움	NULL 칼럼 증가	대부분의 경우
테이블 완전 분리	종류별 별도 테이블	명확, 불필요 칼럼 없음	전체 조회 시 UNION 필요	종류별 독립 필요

실무에서 가장 많이 쓰는 방식은 단일 테이블 + dtype이다.

CREATE TABLE notification (
  id        BIGINT AUTO_INCREMENT PRIMARY KEY,
  member_id BIGINT NOT NULL,
  dtype     VARCHAR(15) NOT NULL,
  target_id BIGINT,
  is_read   TINYINT(1) DEFAULT 0,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  FOREIGN KEY (member_id) REFERENCES member(id)
);

dtype에는 'NOTICE', 'MARKETING', 'RENTAL_ALERT'같은 값이 들어간다. target_id는 연결된 엔티티의 id다.

집계 데이터 설계 전략

좋아요 수를 관리할 때 book 테이블에 likes 칼럼을 두고 +1/-1로 관리하는 방식이 있다. 직관적이지만 "차단 사용자의 좋아요를 제외하고 집계해달라"는 요구사항이 추가되면 대응이 불가능해진다.

올바른 방법은 매핑 테이블(book_likes)에서 COUNT 쿼리로 직접 집계하는 것이다.

SELECT COUNT(*) AS likes_count
FROM book_likes
WHERE book_id = 1;

SELECT COUNT(*) AS likes_count
FROM book_likes bl
WHERE bl.book_id = 1
  AND bl.member_id NOT IN (
    SELECT blocked_id FROM block WHERE blocker_id = 2
  );

book_likes 테이블이 있으면 추가 요구사항도 WHERE 조건으로 쉽게 대응할 수 있다.