데이터 모델 작성시, 많이 나타나는 속성 중에 하나가 여부속성 즉 여부컬럼입니다. 여부 컬럼은 테이블상에 보통 _YN, _FLAG로 끝나는 컬럼입니다. 모델링을 할때마다 여부 컬럼을 어떻게 구현해야할까요? 항상 고민거리가 되는 부분입니다. 심한 경우 한 테이블에 여부 컬럼이 백여개 이상인 것들도 존재합니다. 상황에 따라 모델 구성이 제각각으로 이루어질 수 있다고 하지만, 가만히 그 내부를 살펴보면 몇가지 규칙 및 패턴이 있습니다. 이제 모델링 프로젝트에 투입시에 매번 똑같은 고민을 하지말고 패턴을 잘 적용하여 좀 더 알차게 적용해보겠습니다.
여부 컬럼들은 주로 핵심 혹은 메인 테이블에서 발생하곤 합니다. 어떤 여부 컬럼들이 있을까 한번 나열해보겠습니다. 아래는 상품 테이블에서 봄직한 속성들입니다.
사용여부 / 특별상품여부 / 대표상품여부 / 주문불가여부 / 판매여부 / 품절여부 / 재판매제한여부
설치상품여부 / 가격노출여부 / 비교가격표시여부 / 무료판매여부 / 적립금제한여부 / 할인금적용여부
일시불할인제한여부 / 즉시적립금할인제한여부 / 방송자동적립금제한여부 / 프로모션제한여부 등등
해당 속성마다 제 고유의 비즈니스 로직도 함께 녹아져있으니 속성의 참 뜻을 이해하기란 상당히 어렵습니다. 하나의 테이블에 있는 속성이라고 하더라도 개발 담당하였던 시기가 다를때 한사람이 모든 여부 컬럼의 목적과 활용도를 알기는 쉽지 않습니다.
여부 컬럼들은 그 사용 목적에 따라 여러가지 유형이 존재할 수 있습니다.
1) 중복 속성
- 다른 테이블의 결과만을 요약형태로 갖는 경우(처리 여부)
배치처리 및 후속처리의 결과값으로 활용
- 부모 테이블에 자식 테이블 데이터가 있다없다 표기하는 경우 (존재여부/예약여부)
- 동일테이블의 컬럼에 대한 이중 표현
승인일시의 값이 있다 없다로 표현할 수 있지만 여부컬럼을 부수적으로 사용할 수 있다.
(요일상품여부 + 적용요일코드) (승인여부 + 승인일시) (원본여부 + 원본코드) (전송여부 + 전송일시)
2) 데이터 분류용, 데이터 개체의 정의로 사용되는 속성
- 서브타입 역할을 하는 것 (품목여부/해외상품여부, 거래처여부, 판매처여부)
- 테이블의 구성을 알려주는 역할 (복사된 상품 여부 / 삭제여부)
3) 제어용 속성으로서 로직 처리 하기 위함
- 어플리케이션에서 특정 데이터만을 위한 예외 처리 (사용여부/무료여부)
- 제한 및 제약을 두기 위한 처리 (특판제한여부, 반품불가여부, 카드가능여부)
가능, 불가를 표현하기 위한 것
4) 성능을 위한 속성
- 이벤트 및 거래 액션의 결과 중에서 마크가 필요한 것 : 품절여부
- 요약용 컬럼 : 프로모션 중 여부, 사은품있음 여부
중복속성과 유사한 성격이라고 할 수 있다.
5) 코드를 여부로 나열한 속성
- M개의 값을 YN으로 표기하는 경우 (취미코드 : 바둑여부, 게임여부, 스포츠여부 ...)
- 정규화에 의해서 다른 테이블로 분리가능한 속성들.
6) 기타
여부 컬럼의 표준화
여부 컬럼들을 어떻게 모델에 표현해야 할까. 그 방법에 대해 살펴보겠습니다.
1) 표준화된 명칭
- 일반적으로 속성분류어로서 일관성 있게 사용합니다. 여부, 플래스, 유무라고 표현됩니다.
OO여부 → OO_YN
OO플래그 → OO_FLAG
2) 물리적 세팅
- NOT NULL화 시키고, 디폴트 값을 부여합니다.
여부 컬럼은 NULL 컬럼이 많고, 디폴트 값을 부여하지 않은 시스템들이 많습니다. 하지만 본인은 DB에서 강제하는 것을 선호합니다. 시간이 흘러 여러 담당자가 바뀌다 보면 해당 컬럼의 초기값과 의미를 프로그램 소스를 열어보아야만 알 수 있는 상황이 옵니다. 누구나 테이블에 세팅된 값을 보고 직관적으로 이해할 수 있도록 DB에 세팅하는 것이 좋습니다.
물론 운영중에 추가되는 여부 컬럼은 NULL 허용이 많습니다. 기존 데이터는 영향받지 않고 신규 데이터 혹은 특정 조건일때만 사용되기 때문입니다. 또한 NOT NULL 처리시 데이터 사이즈가 클 경우 디폴트 값을 세팅하는데 오랜 시간이 걸릴 수 있습니다.
프로젝트 진행시 초기에 모델표준에서 여부 칼럼에 대한 사용을 정의해 놓지 않으면, 개발 및 이행단계에서는 NOT NULL화 처리하지 못하고 NULL 허용으로 그대로 가는 경우가 있습니다. 또 NOT NULL 처리는 입력 및 수정 프로그램의 수정이 필요하기 때문에 영향도 파악이 쉽지 않을 수 있습니다.
3) NOT NULL화 할 수 없는 여부 컬럼
모든 여부 컬럼을 NOT NULL화 시켜야 할까? 특정 컬럼은 NULL이 올 수 밖에 없는가?
[상품테이블] [카드가능여부] 칼럼의 값을 보겠습니다.
Y: 100건 / N: 200건 / NULL: 700건
업무규칙은 Y이면 상품구매시 카드를 사용할 수 있습니다. N이면 카드 사용 불가입니다. NULL이면 아직 세팅된 값이 없지만 카드 사용 불가입니다. N과 NULL은 동일 업무 규칙을 따릅니다. 단지 판단할 수 있는 것은 값의 변경이력인데 처음에 여부 값은 [NULL]이였다가 카드사용이 가능해 졌을때 [Y]로 변경되고 이후 다시 [N]로 변경되었음을 짐작할 수 있습니다. 칼럼값의 변경을 위한 추적이라면 이력 혹은 로그 관리를 해야합니다.
변경이 한번도 없었던 값이라는 의미로서의 NULL의 값을 유지할 필요성이 많지는 않습니다. 다소 위험성이 있더라도 NOT NULL화 시키고, 예외가 되는 부분은 NULL 허용이라는 규칙을 가져가는 것이 좋습니다.
( 예외 : YN에 인덱스를 생성하는 경우? 데이터 사이즈를 조금이라도 줄이고 싶은 경우 등 )
4) 데이터 값 검증
여부 컬럼의 데이터 값은 어떻게 세팅 될까요>
가) Y/N: 긍정의 의미로서 Y, 부정의 의미로서 N
일반적인 데이터 세팅입니다. 표준은 YN으로 세팅하는 것이 좋습니다.
나) 1/0: 긍정의 의미로서 1, 부정의 의미로서 0
고전적인 표기법이라고 할 수 있으며 비트연산을 고려해서 만들 수도 있겠습니다. 추천하지는 않습니다.
다) NULL: NULL은 가급적 없애는 것이 좋습니다. 특히 핵심/메인/마스터 테이블에서는 없애야 합니다.
마스터 테이블의 NULL값을 여러 시스템, 서로 다른 개발자가 다른 값으로 치환하는 경우를 간혹 볼 수 있습니다. A 프로그램에서는 NVL(카드가능여부, "Y") B 프로그램에서는 NVL(카드가능여부, "N")로 작성할 오류 가능성도 존재합니다.
라) 1/2
혹은 Y/N을 1/2로 표현한 사이트도 있지 않을까 싶습니다.
마) Y/N/Y/Z
여부 칼럼에 왜 이리 값이 많을 것일까요?
추측1) Y/Z는 오류 값입니다. YN이어야만 합니다. 이런 데이터는 클렌징이 필요합니다.
추측2) 여부 컬럼의 개념이 확장되었습니다. 처음에는 YN 값만 있었는데, 세월이 지나고 시스템이 성장하면서 의미가 확장될 수 있습니다.
[ 사용여부 ]
Y: 사용 / N: 미사용 / Y: 삭제(미사용은 화면에 보이는데 아예 삭제된 데이터를 표기하고자 함) / Z: 검토중(주요 속성이라서 검토/승인 프로세스 진행이라는 것을 표기)
물론 이렇게 개념이 확장된 것은 이제 컬럼명은 사용여부이지만 도메인은 사용유형코드로 변경되어야합니다. 여부컬럼이 코드컬럼으로 진화한 경우라고 볼 수 있습니다.
(컬럼명은 변경하기가 어렵습니다. 이미 모든 프로그램에 적용되어 있을테니 말이죠.)
5) 이해가 편한 한글속성 표기
여부 컬럼도 한글속성명과 영문컬럼명을 잘 만들어 주어야 합니다. 이름이 너무 어려우면 매번 머리에서 2번 고민을 해야 의미를 알 수 있는 컬럼들이 있습니다.
[ 무검사여부 : YN ]
Y: 제품에 대한 정밀검사를 하지 않았다는 의미. 즉 검사하지 않아도 되는 제품
N: 제품에 대한 정밀검사를 하지 않으면 안된다는 의미. 즉 검사를 반드시 해야하는 제품
위 컬럼은 몇일이 지나면 반드시 헷갈리게 되므로, 컬럼명을 명확하게 바꿔주는 것이 좋습니다.
[ 적립금제한여부 : YN ]
Y: 적립금을 줄 수 없는 상품
N: 적립금을 줄 수 있는 상품
컬럼을 이렇게 바꿉니다 적립금가능여부 : YN
사람마다 다르겠지만 제한의 의미를 더욱 강조해야만 하는 경우에는 제한여부를 사용할 수도 있습니다. 판단은 모델러에게 다른 관련자들의 합의하에 결정하면 됩니다.
다음은 NOT NULL화 되지 않는 칼럼들에서 나타나는 NVL() 처리 예시입니다.
SELECT C.PROD_CD
, C.PRD_NM AS PRD_NM
, NVL(C.CHR_DLV_YN, 'N') AS CHARGE_FLAG // 여부가 NULL 처리: N으로 바뀐다
, CASE WHEN NVL(C.CHR_DLVC_CD, 0) > 0 THEN
, NVL(B.SALE_PRC, 0) AS KHS_PRICE // 금액 NULL 처리 0으로 바뀐다.
FROM 배송 D, 상품 C, 가격 B
WHERE A.PRD_CD = B.PRD_CD
AND A.PRD_CD = C.PRD_CD
AND C.SUP_CD = D.SUP_CD
AND VALID_STR_DTM <= SYSDATE AND VALID_END_DTM > SYSDATE
AND A.GUBUN = 'L'
AND A.RANKING <= 20
AND NVL(C.REP_YN, 'N') <> 'Y' // 여부가 NULL 처리 N으로 바뀐다.
AND NVL(C.CARD_USE_LIMIT_YN, 'Y') <> 'N' // 여부가 NULL처리 Y로 바뀐다.
AND C.GBN_CD <> '10')
행여 여부 컬럼이 인덱스 대상 컬럼이라면 NVL()로 인한 가공으로 인하여 인덱스를 최적으로 사용하지 못할 수도 있습니다.
다중컬럼(BIT 모음) 데이터 모델
여부 컬럼을 구현하는 특수한 사례를 살펴보겠습니다. 예전에 생각의 다양함을 확인하기 위하여 특별한 실험을 한 적인 있습니다. 사과를 떠올리면서 연상되는 단어를 5개씩 적어 보았는데, 참여한 사람들이 적어낸 단어들의 공통점이 하나도 없었습니다. 동일한 사건이나 사물을 보고 떠올리는 이미지는 천차만별이였습니다.
데이터 모델링도 사람의 생각을 표현하는 기법이기 때문에 하나의 모델을 작성하기 위해서는 1개의 답만이 있는 것이 아닙니다. 고민과 경험에 의해 생긴 테이블과 칼럼들은 그 나름대로의 사연이 있기에 모두 존중받을 가치가 있습니다.
여기 소개하는 사례도 그 중 하나입니다. 이런 모델 기법이나 방법도 있다는 것을 알리는 것이지, 소개한 사례를 반드시 적용하라는 것은 아닙니다. 데이터 모델의 다양성 측면에서 바라보면 되겠습니다. 데이터 모델은 다음과 같습니다.
1) 일반적인 여부 컬럼
# 상품번호
------------------------
재판매제한여부
무료판매여부
적립금제한여부
할인금적용여부
프로모션제한여부
쿠폰적용가능여부
포인트미부여여부
제휴특판제한여부
카드사용제한여부
사례로 살펴 볼 모델의 구조는 다음과 같습니다.
2-1) 그룹 컬럼
- 컬럼이 모두 사라지고, 속성명도 아리송해졌습니다. 그룹1, 그룹2로 나타납니다.
- 컬럼의 데이터는 어떻게 발생하는지 궁금합니다. 아래 샘플 데이터를 참조합니다.
# 상품번호
------------------------
여부컬럼그룹1
여부컬럼그룹2
2-2) 그룹컬럼 코드 테이블 정의
- 위에 정의된 컬럼의 정의가 무엇인지 코드 테이블에서 관리하기 위함입니다.
- 코드 테이블을 관리함으로서 여러 장점이 있습니다. 아리송한 컬럼에 대한 명확한 정의를 기술할 수 있으며, SQL 작성시 마치 컬럼이 존재하는 것처럼 여러 조작을 할 수도 있습니다.
# 그룹코드
# 컬럼순번
------------------------
컬럼명
한글명
일반적인 방식을 적용한다면 상품 테이블에 20개의 여부 컬럼이 존재합니다. 만약 여부 컬럼이 100개 200개 라면 컬럼 관리하기도 쉽지 않습니다. 이런 상황에서 데이터 값으로만 적용할 수 있는 방식이 몇 개 있는데 그 중 하나가 바로 1개의 컬럼에 여러 개의 값을 표현하는 방법입니다. 무엇보다 유연성이 강조되어 대량의 여부 칼럼이 필요한 상황에서 적용을 고려해 볼 수 있겠습니다.
위 모델에 대한 샘플 데이터는 다음과 같이 구성합니다.
1) 일반적인 여부 컬럼 데이터 값
- 예상했듯이 각 여부 칼럼에 대한 Y,N 값이 존재합니다.
2) 상품 BIT 컬럼 데이터 값
- 한개의 컬럼에 여러 개의 값이 함께 공존합니다.
한 개의 컬럼에 여러 개의 값이 함께 공존하고 있습니다. 이런 모델은 사용 금기시된 것입니다. 먼저 속성 정규화에 위배됩니다. RDBMS의 컬럼에는 유일한 의미의 값을 입력한다는 규칙에도 어긋나며, 데이터품질 관리시에도 애를 먹을 것 같습니다. 또한 SQL 작성도 힘들어질 수 있습니다.
그러나 오래된 시스템을 관리하는 곳에서는 심심치 않게 볼 수 있는 컬럼 구성법입니다. 반드시 여부 컬럼 방식이 아니라 중복 속성으로서 활용하고 있는 예시를 찾아볼 수 있습니다. 테이블 리버스를 수행하다 보면 가끔 위와 같은 형태의 데이터를 확인할 수 있습니다.
위와 같은 모델은 그 값을 정의하는 코드 테이블의 값은 아래와 같이 세팅됩니다.
컬럼 순번은 Y/N의 위치를 가리킵니다. 컬럼명은 영문표기 명, 한글명은 속성명이 되겠습니다. 데이터 값을 정의하지 않으면 하드 코딩된 프로그램 소스를 확인해야만 알 수 있기 때문에 누구나 확인할 수 있도록 정의해 두는 것이 좋습니다.
이러한 테이블에서는 SQL문을 직접 작성해보겠습니다.
SELECT 조회 SQL
SELECT PRD_NO , SUBSTR(COL_GRP_1, 1, 1) AS RE_SALE_LIMIT_YN , SUBSTR(COL_GRP_1, 2, 1) AS NO_PAY_YN , SUBSTR(COL_GRP_1, 3, 1) AS ACCU_LIMIT_YN , SUBSTR(COL_GRP_1,4 ,1) AS SALE_APPLY_YN , SUBSTR(COL_GRP_1,5 ,1) AS PMO_LIMIT_YN , SUBSTR(COL_GRP_1,6 ,1) AS COUP_ABA_YN , SUBSTR(COL_GRP_1,7 ,1) AS POINT_NO_APPLY_YN , SUBSTR(COL_GRP_1,8 ,1) AS JE_LIMIT_YN , SUBSTR(COL_GRP_1,9 ,1) AS JE_SALE_LIMIT_YN , SUBSTR(COL_GRP_1,10,1) AS CARD_LIMIT_YN FROM T_PRD
일반 SQL하고 동일하지만 컬럼을 구분하기 위해서 SUBSTR()를 사용하였습니다. SUBSTR()를 사용했습니다. SUBSTR(컬럼, 컬럼순번, 1) 형태로 정의되어 있기 때문입니다. WHERE 조건절로 투입되지 않기 때문에 성능에도 지장이 없습니다.
적립금제한여부가 'Y'인 것을 찾아라.
SELECT * FROM T_PRD WHERE SUBSTR(COL_GRP_1, 3, 1) = 'Y'
WHERE 절에 SUBSTR()를 사용하여 검색합니다. 여부 컬럼은 주요 액세스 조건 컬럼이 아니라 체크용 조건이라면 성능에 문제가 없습니다.
SELECT PRD_NO ,G.ATTR_NM ,G.COL_NM ,SUBSTR(COL_GRP_1, G.COL_SEQ, 1) YN ,COL_GRP_1 FROM T_PRD P, T_GRP G WHERE P.PRD_NO = 111
예상했듯이 메타로 정의된 코드테이블과 조인하여 데이터를 검색하면 마치 1:M으로 분리된 테이블을 조회한 것과 같은 형태로 데이터가 조회됩니다. 컬럼방식이 아니라 ROW형태로 데이터가 검색됩니다. 리스트 조회가 아니라 PK를 엑세스하는 팝업 창에서는 성능에 이상이 없습니다.
상품 111 중에서 'Y'인 것만 조회하라
SELECT PRD_NO ,G.ATTR_NM ,G.COL_NM ,SUBSTR(COL_GRP_1, G.COL_SEQ, 1) YN ,COL_GRP_1 FROM T_PRD P, T_GRP G WHERE P.PRD_NO = 111 AND SUBSTR(COL_GRP_1, G.COL_SEQ, 1) = 'Y'
WHERE 절에 SUBSTR(COL_GRP_1, G.COL_SEQ, 1)만 추가하면 특정 값이 있는 것만을 조회 가능합니다.
적립금제한여부를 찾되, 마치 컬럼이 있는 것처럼 조회하라.
SELECT
PRD_NO
, G.ATTR_NM
, G.COL_NM
, SUBSTR(COL_GRP_1, G.COL_SEQ, 1) YN
, COL_GRP_1
FROM T_PRD P, T_GRP G
WHERE P.PRD_NO = 111
AND G.COL_NM IN ('ACCU_LIMIT_YN');마치 컬럼이 존재하는 것처럼 WHERE 절이 사용되었습니다. 정의된 메타코드 테이블을 활용하기 때문입니다.
이제 변경 UPDATE 처리를 살펴보겠습니다. 여러 개의 의미가 한 값으로 구성되었기 때문에 한 필드만 UPDATE하려면 약간의 조작이 필요합니다. 원리는 값을 조합할 때 앞부분 + 변경부분 + 뒷부분으로 나눈 후 조립하는 것입니다.
3번째 필드 변경 e SUBSTR(값, 1, 2) + 'YN변경' + SUBSTR(값, 4)
-- 위치 3을 하드코딩하여 UPDATE
-- 3번째 필드를 변경하기 위하여 1~2번, 4~10번 값은 유지하고 3번 위치만 'N'으로 변경.
UPDATE T_PRD SET COL_GRP_1 = SUBSTR(COL_GRP_1, 1, 3-1) || 'N' || SUBSTR(COL_GRP_1, 3+1) WHERE PRD_NO = 111
-- 위치 3을 메타값 사용하여 UPDATE
-- 하드 코딩을 피하기 위하여 메타코드에 정의된 필드의 순번을 가지고 해당 위치의 값만 변경하도록 할 수도 있다.
-- MERGE 구문을 이용하여 3번째 자리만 'N'으로 UPDATE 처리 하였다.
MERGE INTO T_PRD M USING (SELECT COL_SEQ FROM T_GRP WHERE COL_NM = 'ACCU_LIMIT_YN') S ON (M.PRD_NO = 111) WHEN MATCHED THEN UPDATE SET M.COL_GRP_1 = SUBSTR(COL_GRP_1, 1, S.COL_SEQ-1) || 'N' || SUBSTR(COL_GRP_1, S.COL_SEQ+1)
상품111의 모든 여부 컬럼을 UPDATE하라
프로그램에서는 1개의 컬럼만을 업데이트 처리하는 것이 아니라 여러 개의 필드 값이 동시에 변경해야 하는 경우도 있을 것이다. 이런 경우는 어떻게 처리할 수 있을까? 3가지 방법을 생각할 수 있습니다.
첫째. 먼저 프로그램에서 해당 필드 값을 변수로 받아서 FOR ~ LOOP을 돌면서 위에 작성한 1건 업데이트하는 로직을 호출할 수 있겠습니다.
둘째. 여부 값을 변수(파라미터)에 받아서 업데이트할 수 있도록 값을 보정합니다. 파라미터 값 조정하는 부분을 모듈화 한 후 충분히 사용할 수 있겠습니다.
ColGrp_1 = 'YNNNNYNYNN' -- 프로그램에서 조립. UPDATE T_PRD SET COL_GRP_1 = ColGrp_1 WHERE PRD_NO = 111 -- 바로 업데이트.
셋째. 여러 필드를 SQL을 통하여 한번에 업데이트 합니다.
MERGE 구분을 사용해 보았습니다. 샘플은 5개 여부 컬럼만 적용해 봅니다. 샘플은 5개 여부 컬럼만 적용해 봅니다. 우선 USING 절에 사용한 SELECT SQL문장을 작성한 이후 MERGE 구문을 완성하면 됩니다. SQL이 복잡해져서 좀 난해해 질 수 있습니다.
MERGE INTO T_PRD M USING ( SELECT PRD_NO , COL_GRP_1 , SUBSTR(AFT_GRP_1, 1, 1) || SUBSTR(AFT_GRP_2, 2, 1) || SUBSTR(AFT_GRP_3, 3, 1) || SUBSTR(AFT_GRP_4, 4, 1) || SUBSTR(AFT_GRP_5, 5, 1) AS AFT_COL_GRP_1 FROM (SELECT A.PRD_NO , A.COL_GRP_1 , SUBSTR(COL_GRP_1, 1, S1.COL_SEQ-1) || 'N' || SUBSTR(COL_GRP_1, S1.COL_SEQ+1) AS AFT_GRP_1 , SUBSTR(COL_GRP_1, 1, S2.COL_SEQ-1) || 'N' || SUBSTR(COL_GRP_1, S2.COL_SEQ+1) AS AFT_GRP_1 , SUBSTR(COL_GRP_1, 1, S3.COL_SEQ-1) || 'N' || SUBSTR(COL_GRP_1, S3.COL_SEQ+1) AS AFT_GRP_1 , SUBSTR(COL_GRP_1, 1, S4.COL_SEQ-1) || 'Y' || SUBSTR(COL_GRP_1, S4.COL_SEQ+1) AS AFT_GRP_1 , SUBSTR(COL_GRP_1, 1, S5.COL_SEQ-1) || 'N' || SUBSTR(COL_GRP_1, S5.COL_SEQ+1) AS AFT_GRP_1) ) S ON (M.PRD_NO = 111) WHEN MATCHED THEN UPDATE SET M.COL_GRP_1 = AFT_COL_GRP_1
지금까지 소개된 방식은 OO은행에서 적용한 적이 있는데, 반드시 사용해야 하는 방식이 아니라 이런 방식도 고려할 수 있다는 상황이 재미있습니다. 사람의 생각은 무궁무진합니다.
동의여부 데이터모델
여부 컬럼이 모델 내에서 어떻게 변화되는지 샘플을 통하여 살펴봅니다. 아파트나 빌라를 지을때 같은 101동이라면 방구조가 집집마다 동일할 것입니다. 허나 겉모양과 구조는 동일하지만 그 내부에 살고 있는 사람들의 성향에 따라 방에 들여놓은 가구, 전자제품, 옷장 등은 서로 다르게 배치됩니다. 설사 유사하게 배치되더라도 각 방의 사용 용도 및 활용성은 서로 다릅니다.
데이터 모델도 이와 유사하여 겉모습은 같아 보이지만 시스템, 관리자, 환경에 따라 조금씩 다른 사용 모습을 보이곤 합니다. 이번에는 특정 여부 컬럼이 데이터 모델에서 어떻게 구성될 수 있는지 변화의 추이를 살펴봅니다. 약간의 시나리오를 가미하여 현실감 있게 구성해보았습니다.
1) 일반적인 여부 컬럼이 마스터 테이블에 함께 있는 경우.
# 고객번호
------------------------
메일동의여부
SMS동의여부
전화동의여부
우편동의여부
처음 모델에서는 여부 컬럼만 덩그러니 존재합니다. 잠잠한 테이블에 슬금슬금 변화의 바람이 불어오게 됩니다. 바람은 내부적으로 불기도하고, 외부에서 강제적으로 불어 닥칠 수도 있습니다. 개발팀은 변화하지 않으려고 처음에 반항해 볼테지만, 개발IT가 현업과 고객의 요구사항을 이길 수는 없습니다.
- 고객이 언제 동의하였는지 시간을 알아야 한다.
- 누가 동의 처리를 했는지 알아야 한다.
요구사항을 반영하기 위해 모델러/설계자의 고민은 시작되고, 데이터 유연성과 성능까지 고려하다 보면 머리가 복잡해지기 시작합니다.
2) 요구사항을 테이블에 반영한다.
# 고객번호
------------------------
# 고객번호
# 변경일시
------------------------
메일동의여부
메일동의일시
메일동의처리자
SMS동의여부
SMS동의일시
SMS동의처리자
전화동의여부
전화동의일시
전화동의처리자
우편동의여부
우편동의일시
우편동의처리자
고객번호 + 변경일시를 PK로 하는 로그 테이블을 생성하였습니다. 데이터 발생규칙은 다음과 같이 설정하였습니다. 고객 등록시에는 고객 테이블에만 값이 적용되고, 로그는 만들지 않습니다. 여부가 변경되면 현재 값은 고객에만 존재하고 로그는 이전 값을 갖게됩니다. 문제가 무엇일까요? 문제가 있겠지만 일단 무시하고 개발하였다고 가정하겠습니다.
[고객여부이력1]
시간이 흘러 차세대 혹은 고도화 프로젝트가 시작되어서 해당 테이블을 재설계할 때가 되었습니다. 하여 그동안 유지보수하면서 발생한 문제점들을 개선해보기로 했습니다.
- 여부는 4개에서 6개로 추가되었다 (실명인증여부, 은행공유여부)
- 여부 컬럼이 더 많이 추가될 수도 있다. DB 설계 우선 목표 중 하나는 유연성이다.
- 여부 컬럼이 변경되면 변경된 것들만 이력/로그 관리되어야 한다. ASIS 로그 테이블은 어떤 항목이 변경된 것인지 직관적으로 알 수가 없었다. "메일동의여부"만 변경되더라도 모든 컬럼의 로그를 그냥 생성했던 것이다. 즉 테이블 단위 스냅샷 로그 형태이기 때문이다.
위와 같은 요구사항을 반영하기 위해서는 여러가지 모델이 도출될 수 있겠으나 유연성에 바탕을 두어 여부 컬럼을 마스터에 분리하고, 항목별로 관리단위를 변경하는 모델로 설계합니다.
# 고객번호
# 여부구분코드
# 변경일시
------------------------
동의여부
동의일시
동의처리자
1차 모델은 동의여부에 사용되는 컬럼만을 1:M 테이블로 분리한 형태입니다. 여부구분코드는 마스터에 붙어있는 컬럼들을 그룹핑한 코드 세트입니다. 데이터가 m으로 관리되기 때문에 유연성 면에서는 아주 뛰어납니다.
모델에서는 여부구분코드가 6개까지 구성가능 한데, 고객이 동의 여부 'Y'로 선택한 데이터만 발생시켰습니다. 발생되지 않는 여부를 'N'으로 생성하지 않습니다. 그런데 위 모델에는 치명적인 단점이 있습니다. 여부 칼럼의 변경이력에만 치중하다 보니 현재의 101 고객의 메일 동의여부를 확인하려면 최종 변경일시인 2011-03-01의 값을 읽어야만 합니다. SQL은 다음과 같이 작성되는데 MAX() 처리로 인한 비효율이 존재합니다.
1) 고객 111의 동의여부 검색
SELECT CUST_NO, YN_CD, CHG_DT, AGREE_YN FROM TT_CUST_YN WHERE CUST_NO = 101 AND (YN_CD, CHG_DT) IN (SELECT YN_CD, MAX(CHG_DT) FROM TT_CUST_YN WHERE CUST_NO = 101 GROUP BY YN_CD );
단 건 처리라면 PK인덱스를 활용하니까 별문제 없겠지만 리스트 조회 화면에서는 성능의 부담이 될 수 있습니다. 사용자의 화면은 다음과 같이 설계 되었다면 검색 SQL은 어떻게 될까요?
SELECT MAX(DECODE(YN_CD,'메일', AGREE_YN)) "메일", MAX(DECODE(YN_CD,'SMS', AGREE_YN)) "SMS" , MAX(DECODE(YN_CD,'전화', AGREE_YN)) "전화", MAX(DECODE(YN_CD,'우편', AGREE_YN)) "우편", MAX(DECODE(YN_CD,'실명', AGREE_YN)) "실명", MAX(DECODE(YN_CD,'은행', AGREE_YN)) "은행" FROM TT_CUST_YN WHERE (CUST_NO, YN_CD, CHG_DT) IN ( SELECT CUST_NO, YN_CD, MAX(CHG_DT) CHG_DT FROM TT_CUST_YN GROUP BY CUST_NO, YN_CD ) GROUP BY CUST_NO ORDER BY CUST_NO
NVL() 처리를 하면 화면 요구사항에 맞게 데이터 검색은 가능하나 성능은 보증하지 못합니다. MAX() 처리로 인한 비효율이 존재하기 때문이며, 테이블도 2번 액세스 해야 합니다.
그리하여 모델링 시마다 고민에 고민을 하게 만드는 선분이력 테이블로 재설계합니다.
[고객여부이력2]
# 고객번호
# 여부구분코드
# 시작일자
# 종료일자
------------------------
동의여부
동의일시
동의처리자
변경일시 대신에 시작일자~종료일자 형태의 이력 테이블이 완성되었습니다. 물론 선분이력이기때문에 데이터 발생시 유의해야합니다.
1) 고객 111의 동의여부 검색
SELECT CUST_NO, YN_CD, STR_DT, AGREE_YN FROM TT_CUST_YN_2 WHERE SYSDATE BETWEEN STR_DT AND END_DT AND CUST_NO = 101;
- 이력 테이블은 1번만 읽음으로써 쉽게 구현되었습니다. SQL도 쉬워졌고, 성능도 좋아졌습니다.
- 다만 입력/수정시 선분구조로 데이터를 발생시켜야 하는 까다로움이 있습니다.
하여 여부관련 이력 모델의 제3모습이 나타날 수 있습니다.
고객 여부 이력3
3번 이력 모델은 ASIS와 1번 모델의 결합품입니다.
[ 고객 테이블 ]
# 고객번호
------------------------
메일동의여부
SMS동의여부
전화동의여부
우편동의여부
실명동의여부
은행동의여부
기타여부1
기타여부2
기타여부3
현재 고객의 동의관련 여부 칼럼은 마스터 테이블에 두었습니다. 온라인 화면에서는 "동의여부" 필드만을 검색할 뿐이며, 가끔씩 데이터 변경사항을 확인하기 위해서만 변경일자, 변경자 정보가 필요합니다. 모델상으로는 성능을 위한 여부 컬럼의 중복이라 할 수 있습니다.
[ 고객 여부 로그 ]
# 고객번호
# 여부구분코드
# 변경일시
------------------------
동의여부
동의일시
동의처리자
- 로그 테이블은 점이력으로 구성하였습니다.
- 데이터 발생규칙은 고객의 동의 여부를 최초 등록할 때 로그테이블에 입력되며, 여부 컬럼은 고객 테이블과 동기화 처리됩니다. 즉 로그 테이블에는 현재+과거의 이력이 모두 포함되어 있습니다.
- 데이터 입력은 선분이력 모델보다는 간결해 집니다. 개발자의 SQL 작성도 쉬워지면, 성능에도 문제가 없습니다. 단 중복 속성관리로 인해 데이터 품질관리를 주기적으로 점검할 필요가 있습니다.
지금까지 고객 테이블에 존재하는 동의 여부 컬럼에 대한 모델을 살펴 보았습니다. 어떤 모델이 최상일까요? 상황에 따라 다르겠지만 나름대로 의견을 제시해보겠습니다. 처음 ASIS에 구성한 모델의 형식은 바람직하지 않습니다. 그때마다의 요구사항과 개발 요건에 따라 흔들려 버린 모델이기 때문에 곳곳에 비효율이 발생합니다.
여부이력1은 현장에서 흔히 볼 수 있는 구조입니다. 미처 이력과 성능을 염두해 두지 않은 구조입니다. 사실 모델링을 진행하다 보면 설계 당시에는 이것이 이력기능으로 사용되는 것인지 로그기능으로 사용되는 것인지 판단하기 어렵습니다. 어플리케이션 설계가 끝나고 개발된 sql을 뒤늦게 발견하게 되면, 개발 막자지에 데이터 이행이 거의 마무리된 상태에서는 올바로 뒤집을 수 있는 용기있는 모델러는 그리 많지 않습니다.
여부이력2는 모델로서는 최상입니다. ASIS 이행을 위한 이행 능력자가 있으며, 모델의 쓰임새를 올바로 이해하고 있는 어플리케이션 설계자가 있고, 이를 뒷받침하는 개발자가 있다면 충분히 적용가능한 구조입니다. 또한 성능을 보완해 주는 튜너와 고급SQL을 점검해주는 DBA가 있다면 금상첨화입니다.
여부이력3은 2보다는 유연성 면에서 떨어지지만 현실적인 입장에서 적용해 볼만합니다.
여부컬럼 모델 적용
여부 컬럼이 실제 데이터 모델에서 어떻게 표현되는지 살펴보겠습니다. 데이터 모델링은 건축과 많이 비교되곤 합니다. 컬럼의 위치를 설정하는 것도 유사합니다. 내 집의 잡동사니들을 어느 곳에 위치해 둡니까?
방 하나에 모든 잡동사니를 모아 놓는다 (귀찮은 사람, 혹은 방1개만 있는 집)
사용빈도에 따라 다르다 (자주 쓰는 것은 거실에, 가끔 쓰는 것은 베란다)
값어치에 따라 배치한다 (비싼 것은 거실에 장식용으로, 싸구려는 창고에)
라벨을 붙여서 목록화한다 (꼼꼼한 사람은 그룹별로 라벨 주어서 쉽게 찾을 수 있도록 꾸민다)
여부 컬럼들은 테이블의 어떤 위치에서 살아갈 수 있을까요. 여부 컬럼들이 존재하는 방들을 살펴보겠습니다. 적절하게 방안을 꾸미는 것은 모델러의 몫입니다.
1) 기본 마스터 테이블의 컬럼으로 존재
실제 금융 프로젝트의 테이블내 무려 200여개의 여부 컬럼이 존재하기도 합니다. 여부 컬럼은 키/메인/핵심 테이블에 위치될 수 있습니다. 보통 오래된 사이트의 메인 테이블을 리버스 해보면 마스터 테이블에 덕지덕지 달라부터있는 여부 컬럼들을 흔히 볼 수 가 있습니다.
[ 장점 ]
- 마스터 테이블 조회시 바로 검색할 수 있다. SELECT COL 구문만 작성하면 된다.
- 조인이 필요없기 때문에 조인을 회피함으로써 성능에 유리할 수 있다 (항상 그런 것은 아니다)
[ 단점 ]
- 모델이 유연하지 못하다. 여부 컬럼이 추가될때마다 테이블의 변경이 발생하고, 마스터 테이블 변경은 주요 애플리케이션의 변경도 함께 동반할 수 있습니다.
- 여부 컬럼이 특정한 상황일때만 사용되는 경우라면 값이 대부분 없음에도 불구하고 1BYTE를 차지하게 됩니다. 예를 들어 정품여부는 특정 브랜드 일때만 체크하는 속성입니다. Y:1000건, N:2999000건. 즉 대부분은 정품여부 컬럼을 사용하지 않습니다.
[ 의견 ]
- 개체의 구성과 역할을 이해하는데 필요한 여부 컬럼은 마스터에 둡니다.
- SELECT 절에서 자주 사용되며, 사용자 화면에서 매번 검색되는 컬럼은 마스터에 둡니다.
- 성능에 필요하다고 판단되는 컬럼은 마스터에 둡니다.
- 대부분의 여부 값이 세팅되는 컬럼은 마스터에 둡니다 (사용여부, 판매여부 : 대부분 Y)
2) 1:1 테이블 분리
부속품은 부속품대로 따로 보관하는 전략을 사용합니다. 여부 컬럼은 1:1 확장 테이블에 위치 시킵니다.
[ 장점 ]
- 기본 테이블이 가벼워졌습니다. 많은 컬럼들을 가지고 다니지 않으니 부담감이 없습니다. 그만큼 들도다닐때 빠른 속도를 낼 수 있습니다.
- 추가되는 속성은 확장에만 있는 컬럼 ADD시 기본 테이블의 변경에 대한 부담이 없습니다.
[ 단점 ]
- 확장 컬럼을 참조하려면 조인을 해야합니다.
- 1:1 필수 입력이기 때문에 INSERT시 2개 테이블에 동시에 데이터를 발생시킵니다.
[ 의견 ]
- LIST 목록에 조회가 자주되는 컬럼은 확장에 만들지 않습니다.
- 가끔 참조되는 컬럼은 확장에 둡니다.
- PK로 조회되는 팝업 창, 상세내역 화면에서만 보면 되는 컬럼은 컬럼은 확장에 둡니다.
- 컬럼의 값이 많지 않은 경우는 확장에 위치합니다. (정품여부는 1%만 값이 있으므로 확장에 위치 시키면 되겠습니다)
3) 1:M 테이블 분리
여부 컬럼은 1:M 확장 테이블에 위치시킵니다. 컬럼은 물리적 위치에서 논리적인 값으로 변화를 일으킵니다. 모델이 쪼개지는 순간 머리도 함께 조개지려 하지 않습니까? 머릿속이 하얘지는 이유는 형상화 되지 않기 때문입니다. 이럴땐 엑셀을 꺼내 들고 데이터를 만들어보는 습관을 갖는 것이 좋습니다. 데이터를 그리는 방법이 모델을 이해하는 가장 좋은 방법입니다.
[ 여부코드 ]
- 컬럼 정의를 하기위한 코드 테이블을 정의합니다.
- 값으로 정의하기 때문에 관리를 위한 정보를 추가할 수 있습니다.
(사용여부, 한글명, 영문명, 사용여부)
[ 상품_M분리 ]
- 상품과 여부코드가 만나서 생기는 매핑 테이블 형식을 취합니다
( 매핑 테이블 = 교차 테이블 = 관계 테이블 : 모두 동의어 )
- 속성값 레벨이기 때문에 추가적인 관린속성을 만들 수 있습니다.
[ 장점 ]
- 최고 장점은 유연성입니다. 속성이 추가되더라도 테이블 변경없이 데이터 값만 추가하여 관리하면 됩니다. 유연하다고 하여 프로그램을 수정하지 않아도 된다는 것은 아닙니다. 당연히 해당 로직에 들어갈 코드는 하드 코딩 되어야 합니다.
- 여부 마다 인덱스를 만들지 않아도 됩니다. 1:1 모델에서는 재판매제한여부 인덱스, 무료판매여부 인덱스, 적립금제한여부 인덱스를 모두 만들어야 하지만, 1:M 모델에서는 여부속성코드에만 인덱스를 만들면 됩니다.
- 이력관리를 하고자 한다면 시작일시~종료일시를 부여하여 변경이력까지도 쉽게 관리 가능합니다.
[ 단점 ]
- SQL이 복잡합니다. 어려운 것은 아니지만 컬럼으로 조회하는 것보다는 접근이 어렵습니다.
- 성능이 나빠질 수도 있습니다. 특히 리스트 조회하는 곳에서는 M건을 1개로 치환하기 위하여 GROUP BY 혹은 스칼라 서브쿼리가 사용될 수 있습니다.
[ 의견 ]
- 여부 컬럼을 목록화하여 값으로 가져갈 때는 성능상 이슈가 발생할 수 있습니다. 여기서는 성능까지 심도 깊게 다루지는 않습니다.
- PK로 상품별, 혹은 고객별로 상세 정보 뷰 하는 곳에서는 사용해 봄직합니다.
- 한시적인 관리 속성일 때 사용가능합니다.
- 속성이 M 테이블로 분리되는 사유 및 상황은 아주 다양합니다. 그 변화를 느끼고 알면서 모델에 적용하는 것이 좋겠습니다.
- 테이블을 설계하고 CREATE 하는 것은 쉬울 수 있겠으나, 활용성과 편이성을 고려하여 여부 컬럼을 적절하게 분산 배치해야 합니다. 데이터 이행시에도 주의해야 겠습니다.
4) 1:1 테이블 분리 : 테이블 메타 기법
1:M의 성능 문제를 다소 해결한 모델 기법입니다. 한마디로 표현한다면 정의를 내리고 사용한다. 교과서에 나오는 컬럼 정의 방법에는 위배 되지만 패키지 모델에서 많이 사용되고, 알게 모르게 현장에서 많이 사용되고 있는 방법입니다.
다음 기회에 자세한 사례를 다루도록하고 여기서는 특성만 살펴보겠습니다. 테이블에 컬럼을 메타형태로 표현했다해서 "테이블 메타 기법"이라 부르기도 합니다.
- 시스템마다 이런 형태의 테이블이 한두개는 존재할 것입니다.
- 속성값1, 속성값2, 속성값3, 속성값4의 의미는 여부구분코드에 따라 다릅니다.
제한속성 일때는 컬럼 순서대로 재판매제한여부/무료판매여부/적립금제한여부/일시불할인제한여부로 사용됩니다.
배송속성 일때는 컬럼 순서대로 유료배송여부/당일배송여부/반품제한여부/배송제한여부로 사용됩니다.
[ 장점 ]
- 컬럼으로 정의되기 때문에 SQL 작성이 쉽고, 1:M 형보다는 성능에 유리합니다.
- 여부 속성을 공통으로 묶어주는 단위인 "여부구분코드" 설정에 따라 많은 속성을 정의하여 사용할 수 있습니다.
- 물론 속성이 추가되더라도 테이블 변경없이 값 변경으로 적용 가능합니다 (유연성이 좋다고 말할 수도 있겠습니다)
[ 단점 ]
- 테이블과 컬럼, 데이터만 보면 정의를 알 수 없습니다. 따라서 별도의 속성을 정의하는 메타관리 코드 테이블이 필요할 수 있습니다.
- 1개의 컬럼이 다중적인 의미를 갖고 있기 때문에 주의해야 합니다.
[ 의견 ]
- 컬럼이 무수히 많이 발생할 소지가 많은 테이블일 경우 적용 검토할 수 있습니다.
- 서브타입 역할을 하는 "구분코드"를 유형별로 잘 묶어 주어야 합니다.
- 성능이나 SQL 작성은 1:M 형보다 단순하고 간단합니다.
지금까지 여부컬럼이 일반적으로 테이블에 적용되는 사례를 살펴보았습니다. 다음에는 특수한 여부컬럼 방식에 대해 살펴보겠습니다.
'데이터베이스(DA, AA, TA) > 대용량DB' 카테고리의 다른 글
| [RDBMS] 성능향상을 위한 SQL 작성법 (0) | 2017.10.08 |
|---|---|
| [대용량DB] 데이터 테이블의 종류 및 특성 (1) | 2017.07.16 |
| [대용량DB] OLAP 이해하기 (0) | 2017.06.02 |
| [대규모데이터] 대규모 데이터를 다루기 위한 기초지식 (1) | 2017.05.30 |
| [대규모데이터] 규모조정의 요소 (0) | 2017.05.30 |