Standards-Compliant Schema Design and Constraint Checks

한국어 원문으로 표시합니다.

목표

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

왜 중요한가

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

단계

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

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

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

(원본 DB 를 더럽히지 말고 사본에서 시험하세요.)

인덱스를 세 개 만듭니다.
- 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 = ? 용 컬럼 순서까지 맞아야 합니다.
/opt/lab/fixtures/dbo/seed/ 의 CSV 네 개를 각 테이블에 적재합니다. CUSTOMER 200행, PRODUCT 50행, ORDERS 1000행, ORDER_ITEM 2400행 이어야 합니다.
뷰 V_DAILY_SALES 를 만듭니다. 컬럼은 ORD_DT, ORD_CNT, AMT_SUM 이고, 취소 상태(09)를 제외한 주문만 집계하며 ORD_DT 오름차순입니다.
/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: 복합 인덱스의 컬럼 순서를 반대로 만드는 것.

DB 생성

/root/db 를 만들고 sqlite DB /root/db/si.db 를 생성합니다. (테이블이 하나라도 있어야 파일이 만들어집니다. 3단계에서 함께 해도 됩니다.)

sqlite 는 파일 하나가 DB 입니다. 외래키 검사가 기본적으로 꺼져 있다는 점을 기억하세요.

표준용어 매핑표

/root/db/naming.csv 를 만듭니다. 첫 줄은 logical,physical,domain. 요구사항에 나오는 논리명을 표준단어로 조합해 물리명을 정합니다. 10행 이상이고, 물리명은 대문자와 언더스코어만 쓰며 domain 은 비어 있으면 안 됩니다. 고객명, 주문번호, 주문일자, 주문금액, 상품코드 다섯 논리명은 반드시 포함합니다.

표준단어사전을 조합해 물리명을 만듭니다. 같은 의미의 단어가 두 번 쓰이면 약어도 같아야 합니다.

테이블 생성

아래 네 테이블을 만듭니다. 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

제약은 문서가 아니라 DDL 에 있어야 강제됩니다. 필수 항목, 코드값 범위, 업무 키 중복 방지를 각각 어떤 제약으로 표현할지 생각하세요.

제약 동작 확인

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

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

(원본 DB 를 더럽히지 말고 사본에서 시험하세요.)

제약이 실제로 막는지 확인하려면 위반하는 데이터를 넣어 봐야 합니다. 원본 DB 를 더럽히지 않도록 사본에서 시험하는 습관을 들이세요.

조회 패턴 기반 인덱스

인덱스를 세 개 만듭니다.

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 = ? 용 컬럼 순서까지 맞아야 합니다.

복합 인덱스는 컬럼 순서가 전부입니다. 등치 조건 컬럼을 앞에, 범위 조건 컬럼을 뒤에 두는 것이 기본 규칙입니다.

샘플 데이터 적재

/opt/lab/fixtures/dbo/seed/ 의 CSV 네 개를 각 테이블에 적재합니다. CUSTOMER 200행, PRODUCT 50행, ORDERS 1000행, ORDER_ITEM 2400행 이어야 합니다.

CSV 적재 시 헤더 줄 처리와 구분자 지정에 주의하세요. 적재 후 건수를 반드시 확인합니다.

집계 뷰 생성

뷰 V_DAILY_SALES 를 만듭니다. 컬럼은 ORD_DT, ORD_CNT, AMT_SUM 이고, 취소 상태(09)를 제외한 주문만 집계하며 ORD_DT 오름차순입니다.

뷰는 조회 표준을 강제하는 수단이기도 합니다. 논리 삭제 조건을 뷰에 넣어 두면 조건 누락 사고를 줄일 수 있습니다.

테이블정의서 자동 생성

/root/db/table-def.csv 를 만듭니다. 첫 줄은 table,column,type,notnull,pk. DB 메타데이터에서 뽑아 네 테이블의 모든 컬럼을 담습니다. 테이블명, 컬럼 순서 그대로입니다.

문서를 손으로 유지하면 곧 거짓말이 됩니다. DB 메타데이터에서 뽑아내면 항상 실제와 일치합니다.