논리 모델의 물리 설계

논리 모델의 ‘정수 ID’와 ‘시각’은 물리 DDL에서 충분한 설명이 아닙니다.

FK 양쪽 타입 속성이 다르면 제약 생성에 오류가 발생하고, VARCHAR에 숫자를 저장하면 정렬·검사·인덱스 의미가 흔들립니다.

이름도 코드·문서·운영 쿼리가 공유하는 규칙입니다.

회원 게시판은 snake_case 복수 테이블명, id 기본 키, UTC DATETIME(6), 조회수용 BIGINT UNSIGNED를 기준으로 삼습니다.

논리 모델의 후보 키와 선택 관계를 바꾸지 않는 범위에서 물리 결정을 내립니다.

외래 키 타입 불일치

이름과 표시 크기는 같아 보여도 MySQL은 부호 유무를 다른 타입 규칙으로 봅니다.

FK 생성은 오류 3780과 함께 실패하며, 개발자가 제약을 빼면 고아 가능성이 생깁니다.

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)
);

자식 author_id의 부호 있음 속성이 달라 FK 생성 단계에서 즉시 오류가 발생합니다.

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

table physical_posts_bad: not created

부모와 자식의 타입은 다음 비교표처럼 종류별 호환 조건으로 판단합니다.

부모와 자식을 같은 도메인 정의에서 생성해야 불일치를 줄일 수 있습니다.

조회수·published_at을 문자열로 두면 '100'이 '20'보다 문자열 정렬에서 앞설 수 있고 날짜 유효성·범위 검색도 약해집니다.

타입은 저장 공간보다 가능한 연산과 금지 상태를 먼저 결정합니다.

타입 불일치가 관계 생성을 막는 지점

1. 범위 — 실제 최소·최대와 미래 증가를 적습니다.
2. 연산 — 합계·범위·정렬·시간대 변환에 필요한 타입을 봅니다.
3. 호환 — PK/FK의 모든 타입 속성을 나란히 비교합니다.
4. 이름 — 용어집과 DDL·API가 같은 의미를 쓰는지 확인합니다.

타입 범위와 이름 규칙

BIGINT UNSIGNED는 장기 식별자와 계속 증가하는 조회수에 적합합니다.

가격·비율의 정확한 소수는 DECIMAL, 측정 오차를 허용하는 과학 계산은 FLOAT/DOUBLE을 검토합니다.

DATETIME(6)은 넓은 날짜 범위와 시간대 비의존 벽시각 저장에, TIMESTAMP는 UTC 변환과 제한된 범위를 고려합니다.

회원 게시판은 애플리케이션이 UTC 값을 만들어 DATETIME(6)에 저장하고 화면에서 사용자 시간대로 변환하는 계약을 사용합니다. DATETIME이 자동으로 UTC로 바꾸지는 않습니다. 아래 CURRENT_TIMESTAMP 기본값도 UTC 값으로 쓰려면 연결의 세션 time_zone을 '+00:00'으로 설정해야 합니다.

업무 범위를 MySQL 타입으로 내리기

1. ID — 모든 FK에 BIGINT UNSIGNED를 동일하게 적용합니다.
2. 문자 — 최대 길이·정렬 규칙·유일성 비교 의미를 정합니다.
3. 시간 — 이벤트 시간·쓰기 시간·사용자 시간대를 구분합니다.
4. 이름 — 예약어·축약어를 피하고 불리언은 is_/has_로 읽히게 합니다.

테이블 정의서와 DDL

열마다 논리명, 물리명, 타입, NULL, 기본값, 키, 설명을 기록하고 같은 정의로 부모·자식 DDL을 생성합니다.

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;

FK가 정상 생성되고 수치·시각은 타입 연산과 범위 인덱스를 직접 사용할 수 있습니다.

CREATE physical_members: Query OK
CREATE physical_posts: Query OK
FK column type: BIGINT UNSIGNED ↔ BIGINT UNSIGNED
view_count 0: allowed
published_at precision: microseconds preserved

VARCHAR 길이는 무조건 255가 아니라 업무·인덱스·표시 제한에서 정합니다.

utf8mb4는 문자당 최대 바이트가 크므로 복합 인덱스 키 길이를 확인합니다.

created_at 기본값은 DB 쓰기 시간이고 published_at은 게시글 공개 시각입니다.

이름과 입력 경로를 분리해 지연 업로드가 두 시각을 덮지 않게 합니다.

경계값과 FK 호환성을 순서대로 검증

1. 경계값 — 조회수 0과 BIGINT 상한에 가까운 값을 검토합니다.
2. 정밀도 — 마이크로초 시각 왕복 검증을 확인합니다.
3. FK — 부모 없는 ID와 부호 있음 임시 열을 각각 시도합니다.
4. 계획 — 날짜 범위 쿼리가 함수 없이 published_at 인덱스를 쓰는지 봅니다.

타입 불일치 자동 탐지

DDL 리뷰 외에도 FK 양쪽 column_type과 정렬 규칙을 메타데이터에서 비교합니다.

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();

첫 쿼리에서 두 ID 열의 column_type은 bigint unsigned인지 확인합니다. 문자열 정렬 규칙은 DDL에 명시하지 않았으므로 상속한 데이터베이스 설정을 확인합니다. 두 번째 쿼리는 현재 스키마의 FK 전체를 반환하므로 그중 physical_posts가 physical_members를 참조하는 한 관계를 찾습니다.

DATETIME·TIMESTAMP·DECIMAL 선택 비용

TIMESTAMP의 자동 UTC 변환은 연결 시간대 설정에 영향을 받습니다.

DATETIME은 변환하지 않으므로 애플리케이션 UTC 규칙을 엄격히 지켜야 합니다.

어느 타입도 timezone_name 자체를 보존하지 않습니다.

불리언은 MySQL에서 TINYINT(1) 별칭이므로 0/1만 허용해야 한다면 검사를 추가합니다.

DDL 이름과 타입 변경 배포

대형 열 타입 변경은 테이블 재생성과 잠금을 만들 수 있습니다.

섀도 마이그레이션·온라인 DDL·복제본 지연을 검토합니다.

이름 변경은 뷰 별칭이나 이중-읽기 기간을 거쳐 애플리케이션·BI·배치 소비자를 함께 전환합니다.

물리 타입·이름 선택표

• DATETIME(6) — 넓은 범위·명시적 값. 감수할 비용은 UTC 변환 책임, 적합한 조건은 애플리케이션이 시점을 통제할 때.
• TIMESTAMP(6) — 연결 시간대에 따른 변환. 감수할 비용은 범위·설정 의존, 적합한 조건은 DB 변환 규칙이 명확할 때.
• DECIMAL — 정확한 소수. 감수할 비용은 고정 정밀도 설계, 적합한 조건은 금액·비율 정확성이 중요할 때.
• FLOAT — 넓은 과학 계산. 감수할 비용은 이진 근사 오차, 적합한 조건은 근사 측정값일 때.

최솟값·최댓값·시간대 예제 데이터

1. 3780 — 부호 있음 자식으로 FK 생성 오류를 재현합니다.
2. 문자 수치 — '100'과 '20' 정렬을 숫자 타입과 비교합니다.
3. 시간대 — 연결 시간대 변경에서 TIMESTAMP·DATETIME 표시를 비교합니다.
4. 경계 — 각 수치 타입 최대값과 검사 업무 최대값을 테스트합니다.

예약어·정렬 규칙·부호 없음·정밀도 예외

• 사용자·순서·순위 같은 예약어·모호한 이름은 백틱 의존을 만듭니다.
• 문자 FK는 양쪽 정렬 규칙까지 호환되어야 합니다.
• UNSIGNED 차감은 언더플로 전에 업무 검사·조건부 UPDATE로 막습니다.
• 2038 이후 범위와 과거 날짜 요구는 사용 MySQL 버전의 TIMESTAMP 범위를 확인합니다.

타입과 이름을 고정했으므로 다음 문서에서는 조회 성능을 위해 중복 집계를 추가할 때 원본·기준 시각·재계산 규칙을 설계합니다.

물리 이름과 타입 선택

타입을 프레임워크 기본값으로 고르지 않고 잘못된 값이 어디에서 차단되는지 설명합니다.

테이블 정의서는 DDL과 다른 문서가 아니라 자동으로 일치 여부를 확인할 수 있는 규칙이어야 합니다.

연습 문제

게시글 첨부파일의 URL, 원본 이름, MIME 타입, 바이트 크기, 업로드 시각을 물리 설계하세요.

빈 파일과 음수 크기를 막고 같은 저장 URL의 중복을 막으세요.

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;

핵심 정리

• FK 열은 이름뿐 아니라 모든 타입 속성이 호환되어야 합니다.
• 수치·시간을 문자열로 저장하면 연산과 무결성을 잃습니다.
• 이벤트 시간·쓰기 시간·시간대는 별 규칙입니다.
• 테이블 정의서와 information_schema를 일치 검사해 불일치를 막습니다.

다음 문서에서는 역정규화된 게시글 통계가 원본과 어긋나는 오류를 만들고 동기화·재계산 전략을 고릅니다.