SI DB 운영 · 스키마 설계와 표준 · 실습

표준에 맞춘 스키마 설계와 제약 검증

목표

데이터 표준(단어→도메인→용어)에 맞춰 스키마를 설계하고, 제약조건이 실제로
동작하는지 확인하고, 조회 패턴에 맞는 인덱스를 만들고,
테이블정의서를 메타데이터에서 자동 생성할 수 있게 됩니다.

왜 중요한가

같은 고객명이 CUST_NAME(50), CUSTOMER_NM(100), CUST_NM(30) 으로
흩어져 있는 시스템은 통합 조회에서 고생하고, 짧은 컬럼에서 데이터가 조용히 잘리고,
3년 뒤 이관 때 매핑표를 사람이 손으로 만들게 됩니다.
그리고 테이블정의서에 '필수'라고 적어 놓고 DDL 에 NOT NULL 이 없으면
그 문서는 지켜지지 않습니다. 제약은 DB 에 걸어야 강제됩니다.
마지막으로, 손으로 유지하는 정의서는 개발이 끝날 때쯤 반드시 실제와 어긋납니다.
메타데이터에서 뽑는 습관이 이 문제를 없앱니다.

단계

0. 요구사항: /opt/lab/fixtures/dbo/req/schema-req.md
표준단어: /opt/lab/fixtures/dbo/std/standard-words.csv
1. /root/db 를 만들고 sqlite DB /root/db/si.db 를 생성합니다.
(테이블이 하나라도 있어야 파일이 만들어집니다. 3단계에서 함께 해도 됩니다.)
2. /root/db/naming.csv 를 만듭니다. 첫 줄은 logical,physical,domain.
요구사항에 나오는 논리명을 표준단어로 조합해 물리명을 정합니다.
10행 이상이고, 물리명은 대문자와 언더스코어만 쓰며 domain 은 비어 있으면 안 됩니다.
고객명, 주문번호, 주문일자, 주문금액, 상품코드 다섯 논리명은 반드시 포함합니다.
3. 아래 네 테이블을 만듭니다.
CUSTOMER, PRODUCT, ORDERS, ORDER_ITEM
요건:

모든 테이블에 PRIMARY KEY
ORDERS.CUST_ID → CUSTOMER, ORDER_ITEM.ORD_NO → ORDERS,

ORDER_ITEM.PROD_CD → PRODUCT 로 FOREIGN KEY

ORDER_ITEM.QTY 에 양수만 허용하는 CHECK
ORDERS.ORD_STS_CD 에 '01','02','03','09' 만 허용하는 CHECK
모든 테이블에 REG_DT 컬럼과 NOT NULL, 기본값
CUSTOMER.CUST_EMAIL 에 UNIQUE

4. /root/db/constraint.txt 를 만듭니다. 세 줄이고, 각 줄은
위반 시도의 결과 메시지 첫 줄을 담습니다.

   qty=<QTY 를 0 으로 INSERT 했을 때 오류 메시지>   status=<ORD_STS_CD 를 '99' 로 INSERT 했을 때 오류 메시지>   email=<CUST_EMAIL 을 중복으로 INSERT 했을 때 오류 메시지>

(원본 DB 를 더럽히지 말고 사본에서 시험하세요.)
5. 인덱스를 세 개 만듭니다.

IX_ORDERS_01 : WHERE CUST_ID = ? AND ORD_DT BETWEEN ? AND ? 용
IX_ORDERS_02 : WHERE ORD_DT = ? AND ORD_STS_CD = ? 용
IX_ORDER_ITEM_01 : WHERE PROD_CD = ? 용

컬럼 순서까지 맞아야 합니다.
6. /opt/lab/fixtures/dbo/seed/ 의 CSV 네 개를 각 테이블에 적재합니다.
CUSTOMER 200행, PRODUCT 50행, ORDERS 1000행, ORDER_ITEM 2400행 이어야 합니다.
7. 뷰 V_DAILY_SALES 를 만듭니다.
컬럼은 ORD_DT, ORD_CNT, AMT_SUM 이고,
취소 상태(09)를 제외한 주문만 집계하며 ORD_DT 오름차순입니다.
8. /root/db/table-def.csv 를 만듭니다. 첫 줄은
table,column,type,notnull,pk.
DB 메타데이터에서 뽑아 네 테이블의 모든 컬럼을 담습니다.
테이블명, 컬럼 순서 그대로입니다.

참고

sqlite 외래키 활성화: PRAGMA foreign_keys = ON; (연결마다 설정해야 합니다)
메타데이터: PRAGMA table_info(<테이블>);, PRAGMA foreign_key_list(<테이블>);
CSV 적재: .mode csv / .import --skip 1 <파일> <테이블>
흔한 실수 1: PRAGMA foreign_keys 를 켜지 않아 FK 가 검사되지 않는 것.

sqlite 는 기본이 꺼짐입니다.

흔한 실수 2: CHECK 를 문서에만 적고 DDL 에 안 넣는 것.
흔한 실수 3: 복합 인덱스의 컬럼 순서를 반대로 만드는 것.

단계 8개

DB 생성
표준용어 매핑표
테이블 생성
제약 동작 확인
조회 패턴 기반 인덱스
샘플 데이터 적재
집계 뷰 생성
테이블정의서 자동 생성