논리 모델의 물리 설계

논리 모델에 적은 "정수 ID"와 "시각"은 물리 DDL에서 충분한 설명이 아닙니다. 외래 키 양쪽 열의 타입 속성이 다르면 제약을 만들 때 오류가 나고, VARCHAR에 숫자를 저장하면 정렬과 검사, 인덱스의 뜻이 흔들립니다. 이름도 코드와 문서, 운영 쿼리가 함께 쓰는 규칙입니다.

회원 게시판은 snake_case의 복수형 테이블 이름, id라는 기본 키, UTC 값을 담는 DATETIME(6), 조회수를 담는 BIGINT UNSIGNED를 기준으로 삼습니다. 물리 설계(physical design)의 결정은 논리 모델의 후보 키와 관계의 선택을 바꾸지 않는 범위에서 내립니다.

외래 키 타입의 불일치

이름과 크기가 같아 보여도, MySQL은 정수의 부호 유무가 다르면 외래 키로 이을 수 없는 타입으로 봅니다. 아래 코드에서 부모의 id는 BIGINT UNSIGNED이고 자식의 author_id는 부호 있는 BIGINT입니다.

DROP TABLE IF EXISTS physical_posts_bad;
DROP TABLE IF EXISTS physical_members_bad;

CREATE TABLE physical_members_bad (
  id BIGINT UNSIGNED NOT NULL PRIMARY KEY
);
CREATE TABLE physical_posts_bad (
  id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  author_id BIGINT NOT NULL,
  view_count VARCHAR(10) NOT NULL,
  published_at VARCHAR(30) NOT NULL,
  CONSTRAINT fk_physical_bad_author
    FOREIGN KEY (author_id)
    REFERENCES physical_members_bad (id)
);

ERROR 3780 (HY000): Referencing column 'author_id' and referenced column 'id' in foreign key constraint 'fk_physical_bad_author' are incompatible.

부모 테이블은 만들어지지만, 자식 테이블은 CREATE TABLE 문장 전체가 실패해 존재하지 않습니다. 이때 급한 마음에 제약만 빼고 다시 만들면 테이블은 생기지만, 그때부터는 부모가 없는 author_id가 들어올 수 있습니다. 부모와 자식의 열은 같은 도메인 정의에서 만들어 불일치 자체를 줄입니다.

MySQL의 외래 키 규칙은 타입의 종류마다 다릅니다. INTEGER나 DECIMAL 같은 고정 정밀도 타입은 크기와 부호가 같아야 하고, 문자열은 길이가 달라도 되지만 비이진 문자열이라면 문자 집합과 정렬 규칙이 같아야 합니다(외래 키 제약). 열의 이름은 id와 author_id처럼 달라도 됩니다.

위의 자식 테이블에는 문제가 하나 더 있습니다. 조회수와 공개 시각을 문자열로 두면, 문자열의 비교가 숫자나 시각의 비교와 다르게 동작합니다.

SELECT '100' < '20' AS as_string,
       100 < 20 AS as_number;

as_string | as_number
1         | 0

문자열로는 첫 글자 '1'이 '2'보다 앞서므로 '100'이 '20'보다 작다고 판단합니다. 그래서 문자열 조회수로 정렬하면 순서가 틀리고, 날짜 문자열에는 유효하지 않은 날짜나 형식이 다른 값이 들어와도 막을 수 없습니다. 타입은 저장 공간보다 먼저, 가능한 연산과 금지할 상태를 정합니다.

타입의 범위와 이름 규칙

BIGINT UNSIGNED는 0부터 18446744073709551615까지 담을 수 있어, 오래 쓰는 식별자와 계속 늘어나는 조회수에 알맞습니다. 가격과 비율처럼 정확한 소수가 필요하면 DECIMAL을, 측정 오차를 허용하는 과학 계산이라면 FLOAT나 DOUBLE을 검토합니다.

DATETIME(6)은 넓은 날짜 범위의 값을 시간대 변환 없이 그대로 저장합니다. 반면 TIMESTAMP는 연결의 time_zone을 기준으로 UTC로 바꾸어 저장하고 읽을 때 다시 바꾸며, 2038-01-19 03:14:07 UTC까지만 담을 수 있습니다(DATETIME과 TIMESTAMP의 시간대 동작).

회원 게시판은 애플리케이션이 UTC 값을 만들어 DATETIME(6)에 저장하고, 화면에서 사용자의 시간대로 바꾸는 규칙을 씁니다. DATETIME이 값을 UTC로 바꾸어 주지는 않으므로, CURRENT_TIMESTAMP 기본값도 UTC로 쓰려면 그 연결의 세션 time_zone을 '+00:00'으로 맞추어야 합니다. 연결을 새로 만들면 그 연결의 설정도 다시 확인합니다.

SET SESSION time_zone = '+00:00';
SELECT @@session.time_zone;

@@session.time_zone
+00:00

업무의 범위를 MySQL 타입으로 옮길 때는 네 가지를 정합니다. ID는 모든 외래 키에 같은 BIGINT UNSIGNED를 쓰고, 문자열은 최대 길이와 정렬 규칙, 유일성을 비교하는 방식을 정합니다. 시간은 사건이 일어난 시각과 데이터베이스에 쓴 시각, 사용자의 시간대를 구분하며, 이름은 예약어와 축약어를 피하고 불리언은 is_나 has_로 시작해 참과 거짓으로 읽히게 합니다.

테이블 정의서와 DDL

테이블 정의서에는 열마다 논리 이름과 물리 이름, 타입, NULL 허용, 기본값, 키, 설명을 적고, 같은 정의로 부모와 자식의 DDL을 만듭니다. 아래 DDL에서 시각 열은 모두 마이크로초까지 담는 DATETIME(6)이고, 기본값도 같은 정밀도의 CURRENT_TIMESTAMP(6)으로 적었습니다. MySQL은 열과 기본값의 소수 초 정밀도가 다르면 테이블을 만들지 않으므로 둘을 함께 맞춥니다. 회원의 id와 email에 붙인 COMMENT처럼 정의서의 설명을 DDL에 함께 남겨 둘 수도 있습니다.

DROP TABLE IF EXISTS physical_posts;
DROP TABLE IF EXISTS physical_members;

CREATE TABLE physical_members (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '회원 ID',
  email VARCHAR(255) NOT NULL COMMENT '로그인 이메일',
  password_hash VARCHAR(255) NOT NULL,
  name VARCHAR(80) NOT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id), UNIQUE (email)
) ENGINE = InnoDB;
CREATE TABLE physical_posts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  author_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(120) NOT NULL,
  content TEXT NOT NULL,
  view_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
  published_at DATETIME(6) NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  FOREIGN KEY (author_id) REFERENCES physical_members (id)
) ENGINE = InnoDB;

이번에는 두 열의 타입이 모두 BIGINT UNSIGNED이므로 외래 키가 만들어집니다. 부모 행 하나와 마이크로초까지 적은 공개 시각으로 기본값과 정밀도를 확인하고, 부모가 없는 글도 넣어 봅니다. 두 테이블을 방금 다시 만들었으므로 게시글은 한 행뿐이며, 마지막 문장은 id가 999999인 회원이 없다고 전제합니다.

INSERT INTO physical_members (email, password_hash, name)
VALUES ('[email protected]', '$2b$12$physical.fixture', '물리 설계 회원');

INSERT INTO physical_posts (author_id, title, content, published_at)
VALUES (LAST_INSERT_ID(), '마이크로초 확인', '정밀도 실습',
        '2026-07-14 09:00:00.123456');

SELECT view_count, published_at
FROM physical_posts;

INSERT INTO physical_posts (author_id, title, content)
VALUES (999999, '부모 없는 글', '외래 키 확인');

view_count | published_at
0          | 2026-07-14 09:00:00.123456

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`board_lab`.`physical_posts`, CONSTRAINT `physical_posts_ibfk_1` ...)

조회수는 기본값 0으로 채워졌고, 공개 시각은 마이크로초 여섯 자리까지 그대로 읽힙니다. 숫자와 시각 타입이므로 합계나 범위 검색, 정렬에 그대로 쓸 수 있습니다.

VARCHAR의 길이는 습관처럼 255로 두지 않고, 업무와 인덱스, 화면 표시의 제한에서 정합니다. utf8mb4는 문자 하나가 최대 4바이트이므로, 16KB 페이지와 DYNAMIC 행 형식의 InnoDB에서 3072바이트인 인덱스 키 길이의 한도를 복합 인덱스에서 넘지 않는지 계산합니다.

created_at의 기본값은 데이터베이스에 쓴 시각이고, published_at은 게시글을 공개한 시각입니다. 두 이름과 입력 경로를 나누어 두면, 늦게 도착한 업로드가 공개 시각을 쓴 시각으로 덮어쓰지 않습니다.

타입 불일치의 자동 탐지

DDL을 리뷰하는 것과 별개로, 외래 키 양쪽의 column_type과 정렬 규칙을 메타데이터로 비교합니다. 첫 쿼리는 두 테이블의 모든 열을 정의한 순서(ordinal_position)대로 늘어놓아 타입, NULL 허용, 기본값, 정렬 규칙을 나란히 보게 하고, 둘째 쿼리는 physical_posts에 실제로 등록된 외래 키를 확인합니다. 쿼리에는 열 이름을 소문자로 적었지만 결과의 머리글이 대문자로 나오는 것은, MySQL 8.4의 information_schema 뷰가 열 이름을 대문자로 정의해 두었기 때문입니다.

SELECT table_name, column_name, column_type,
       is_nullable, column_default, collation_name
FROM information_schema.columns
WHERE table_schema = DATABASE()
  AND table_name IN ('physical_members', 'physical_posts')
ORDER BY table_name, ordinal_position;

SELECT constraint_name, table_name, referenced_table_name
FROM information_schema.referential_constraints
WHERE constraint_schema = DATABASE()
  AND table_name = 'physical_posts';

TABLE_NAME       | COLUMN_NAME   | COLUMN_TYPE     | IS_NULLABLE | COLUMN_DEFAULT       | COLLATION_NAME
physical_members | id            | bigint unsigned | NO          | NULL                 | NULL
physical_members | email         | varchar(255)    | NO          | NULL                 | utf8mb4_0900_ai_ci
physical_members | password_hash | varchar(255)    | NO          | NULL                 | utf8mb4_0900_ai_ci
physical_members | name          | varchar(80)     | NO          | NULL                 | utf8mb4_0900_ai_ci
physical_members | created_at    | datetime(6)     | NO          | CURRENT_TIMESTAMP(6) | NULL
physical_posts   | id            | bigint unsigned | NO          | NULL                 | NULL
physical_posts   | author_id     | bigint unsigned | NO          | NULL                 | NULL
physical_posts   | title         | varchar(120)    | NO          | NULL                 | utf8mb4_0900_ai_ci
physical_posts   | content       | text            | NO          | NULL                 | utf8mb4_0900_ai_ci
physical_posts   | view_count    | bigint unsigned | NO          | 0                    | NULL
physical_posts   | published_at  | datetime(6)     | YES         | NULL                 | NULL
physical_posts   | created_at    | datetime(6)     | NO          | CURRENT_TIMESTAMP(6) | NULL

CONSTRAINT_NAME       | TABLE_NAME     | REFERENCED_TABLE_NAME
physical_posts_ibfk_1 | physical_posts | physical_members

두 ID 열이 모두 bigint unsigned이므로 타입이 맞습니다. MySQL 8.4는 정수 타입의 표시 폭을 보여 주지 않으므로 bigint(20)이 아니라 bigint unsigned로 나옵니다. 문자열 열의 정렬 규칙은 DDL에 적지 않았으므로 데이터베이스의 설정 utf8mb4_0900_ai_ci를 이어받았고, 이름을 붙이지 않은 외래 키에는 physical_posts_ibfk_1이라는 이름이 만들어졌습니다.

시간과 수치 타입의 선택

TIMESTAMP의 UTC 변환은 연결의 시간대 설정에 영향을 받습니다. DATETIME은 바꾸지 않으므로 애플리케이션이 UTC 규칙을 엄격히 지켜야 하며, 어느 타입도 Asia/Seoul 같은 시간대의 이름 자체를 보존하지는 않습니다. MySQL의 BOOLEAN은 TINYINT(1)의 별칭이므로, 0과 1만 허용하려면 CHECK를 더합니다.

이미 데이터가 많은 테이블에서 열의 타입을 바꾸면 테이블의 재생성과 잠금이 생길 수 있으므로, 섀도 마이그레이션과 온라인 DDL, 복제본의 지연을 검토합니다. 섀도 마이그레이션은 새 구조로 만든 빈 복사본 테이블에 기존 행을 나누어 옮기고 그사이의 변경도 따라 적용한 뒤, 원래 테이블과 이름을 바꾸어 교체하는 방식입니다. 이름을 바꿀 때는 뷰의 별칭이나 옛 이름과 새 이름을 함께 읽는 기간을 두고, 애플리케이션과 BI, 배치 같은 소비자를 함께 옮깁니다.

더 깊이: 시각 타입의 정확한 범위와 소수 초의 반올림

두 시각 타입의 범위와 저장 크기는 MySQL 8.4 매뉴얼에 다음처럼 정해져 있습니다. 소수 초를 저장하는 공간은 소수 자릿수가 1이나 2이면 1바이트, 3이나 4이면 2바이트, 5나 6이면 3바이트이므로, 이 교재의 DATETIME(6)은 값 하나에 8바이트를 씁니다.

소수 자릿수를 생략하면 0이 되는데, 이는 표준 SQL의 기본값 6과 다른 MySQL의 선택입니다. 열보다 소수 자릿수가 많은 값을 넣으면 잘리지 않고 반올림되며, 이때는 경고도 오류도 나지 않습니다. 매뉴얼의 예에서 DATETIME(2) 열에 '2018-09-08 17:51:04.777'을 넣으면 17:51:04.78로 저장됩니다. 같은 규칙으로 DATETIME(0) 열에 23:59:59.7을 넣으면 다음 날 00:00:00이 되므로, 하루의 끝을 저장하는 값이라면 날짜까지 바뀔 수 있습니다. 반올림 대신 잘라 내려면 TIME_TRUNCATE_FRACTIONAL SQL 모드를 켭니다.

TIMESTAMP는 저장할 때 지금 세션의 시간대에서 UTC로 바꾸고, 읽을 때 다시 세션의 시간대로 바꿉니다. 그래서 같은 행이라도 time_zone이 다른 연결에서 읽으면 다른 시각이 보이며, 매뉴얼은 저장할 때와 읽을 때 같은 시간대를 쓰지 않았기 때문이라고 설명합니다. DATETIME에는 이런 변환이 없으므로, 이 교재처럼 UTC로 저장한다는 규칙을 애플리케이션이 지켜야 합니다.

이름과 타입의 예외

order와 rank는 MySQL 8.4의 예약어라서 백틱 없이는 식별자로 쓸 수 없고, user나 status는 예약어가 아니어도 뜻이 모호합니다. 문자열 외래 키는 양쪽의 정렬 규칙까지 같아야 합니다. 부호 없는 열에서 0보다 작아지는 뺄셈은 오류 1690으로 실패하므로, 차감은 업무의 검사나 조건부 UPDATE로 미리 막습니다. 2038년 이후의 시각이나 오래된 과거 날짜가 필요하다면 TIMESTAMP의 범위를 먼저 확인합니다.

물리 설계의 테스트 데이터에는 각 수치 타입의 최댓값과 업무상의 최댓값, 연결 시간대를 바꾸었을 때의 TIMESTAMP와 DATETIME의 표시, 문자열로 둔 숫자의 정렬을 함께 넣습니다.

타입은 프레임워크의 기본값으로 고르지 않고, 잘못된 값이 어디에서 막히는지 설명할 수 있어야 합니다. 테이블 정의서도 DDL과 따로 노는 문서가 아니라, 메타데이터와 자동으로 비교할 수 있는 규칙이어야 합니다.

정리

• 외래 키 열은 이름이 아니라 크기와 부호, 문자 집합, 정렬 규칙 같은 타입 속성이 호환되어야 합니다.
• 수치와 시각을 문자열로 저장하면 연산과 정렬, 유효성의 검사를 잃습니다.
• DATETIME은 시간대를 바꾸지 않으므로, UTC 규칙과 연결의 time_zone을 함께 정합니다.
• 사건의 시각과 쓴 시각, 사용자의 시간대는 서로 다른 규칙입니다.
• 테이블 정의서와 information_schema를 비교해 타입의 불일치를 찾습니다.

연습 문제

1. 첨부파일의 물리 설계

게시글 첨부파일의 URL과 원래 파일 이름, MIME 타입, 바이트 크기, 업로드 시각을 물리 설계해 보세요. 빈 파일과 음수 크기를 막고, 같은 저장 URL이 두 번 저장되지 않게 합니다.

DROP TABLE IF EXISTS physical_attachments;
CREATE TABLE physical_attachments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  canonical_url VARCHAR(768) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  mime_type VARCHAR(127) NOT NULL,
  byte_size BIGINT UNSIGNED NOT NULL,
  uploaded_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE (canonical_url),
  CHECK (byte_size > 0)
) ENGINE = InnoDB ROW_FORMAT = DYNAMIC;

2. 어떤 제약이 작동하는가

1번의 테이블에 https://files.example.com/a.png, 10바이트인 행이 이미 있습니다. 다음 네 입력을 각각 따로 넣으면 무엇이 일어날까요? 다른 필수 값은 모두 유효하다고 가정합니다.

또 mime_type에 'image/png'를 저장했다는 사실만으로, 파일의 실제 내용이 PNG라고 할 수 있을까요?

ERROR 1062 (23000): Duplicate entry 'https://files.example.com/a.png' for key 'physical_attachments.canonical_url'
ERROR 3819 (HY000): Check constraint 'physical_attachments_chk_1' is violated.
ERROR 1264 (22003): Out of range value for column 'byte_size' at row 1

다음 글 역정규화와 집계 데이터에서는 역정규화한 게시글 통계가 원래 데이터와 어긋나는 오류를 만들고, 동기화와 재계산의 전략을 고릅니다.