최상운의 사선(死線)에서 (3회)

현행 모델 분석 2 : 리버스 ERD를 이용한 엔터티, 식별자 분석

[필자 소개]?최상운은 2000년 중반부터 데이터 관련 직무를 수행하고 있다. 은행·보험·증권·신용카드사 프로젝트에서 데이터 아키텍트, 데이터 모델러, PMO(Project Management Officer)를 수행했고, ISP 컨설팅에 참여했다. 한국데이터산업진흥원(KDATA)에서 주최하는 ‘DA 설계 공모대전’에서 2018년에 대상을, 2016년에는 금상을 각각 수상했다. 복잡하고 어려운 모델이 아닌 ‘업무가 정확히 담겨 있고 모두가 이해할 수 있는 모델’을 만들기 위해 현장에서 땀 흘리고 있다.

?

현장에서 전하는 데이터 모델링 이야기 필자는 수년간 시스템 통합(SI) 프로젝트에서 데이터 아키텍터, 데이터 모델러, PMO, 컨설턴트로서 역할을 했다. 그때마다 ‘왜 저렇게 데이터 모델링을 하지? 어떻게 하면 사람들이 데이터 모델 이론을 쉽게 익히고 베스트는 아니지만 모델에 업무를 표현하고 관련자에게 공유할 수 있도록 도와줄 수 있을까?’를 놓고 고민했다. 정답은 아니지만 데이터 모델링 과정을 이해하고 각 과정에서 해야 할 것과 점검할 것을 자료 형태로 정리하면 도움이 될 것 같아 조금씩 정리하고 있었다. 어렵고 복잡한 데이터 모델 이론은 배제하고 개발자 입장에서 접근 가능한 데이터 모델 이론을 소개하고, 실제 베스트와 워스트 데이터 모델 사례를 소개함으로써 '제대로 된 상당한 수준의 모델'보다는 '업무가 정확히 담겨 있고 모두가 이해할 수 있는 모델'을 작성할 수 있도록 하고 싶다는 생각에 감히 도전하게 되었다. 필자는 SI 프로젝트 현장에서 데이터 모델러로서 생사가 갈리는 전쟁터, 그 사선(死線)을 넘나들고 있다. 어떻게 하면 이 사선을 넘어 목표 지점에 도달할 수 있을까? 이 차원에서 다소 무겁지만 현장에서 겪는 문제점들을 먼저 알아보고, 나름대로 대책도 제시해 보고자 한다. 대책이 정답은 아니더라도, 모든 것을 해결할 수는 없더라도, 새롭고 획기적인 방법은 아니더라도 모델 다운 데이터 모델을 만들기 위한 방법을 생각했고 그것을 나누고자 이 글을 시작한다.

지난 2회에서 현행 데이터베이스 스키마 정보를 이용해 리버스 ERD를 작성했다. 이번 회에는 리버스 ERD로 무엇을·어떻게 분석할 것인지에 대해 알아본다.

? 엔터티를 실체·주요·행위·목적으로 분류

금융권의 경우 한 모델에 300~400개 정도의 엔터티가 존재한다. 이 많은 엔터티를 4주 정도의 현행분석 기간 동안 ‘정석대로’ 분석하기는 힘들다.

‘파레토 법칙(Pareto principle)’을 들어보았는가? ‘80대20 법칙’이라고도 하는데 ‘전체 결과의 80%가 전체 원인의 20%에서 일어나는 현상'을 가리킨다. 이 법칙은 데이터 모델에도 적용된다. 전체의 20% 엔터티가 업무의 80%를 담당하고, 나머지 80% 엔터티는 참조나 부가적인 정보를 관리한다. 따라서?80% 업무를 담당하고 있는 20% 엔터티에 집중해야 한다.

하지만 많은 엔터티 중 어떤 것이 20% 안에 드는 것인지를 모르는데! 그래도 걱정할 필요는 없다. 프로젝트 초기 단계에는 현행 엔터티에 대해 가장 많이 알고 있는 사람은 고객사 담당자다. 고객사 담당자가 엔터티 명만 보고 바로 뭐라고 설명할 수 있는 엔터티가 바로 20%의 주인공이다.

‘AS-IS 테이블 조사 양식’에 '엔터티 분류' 항목을 추가하고 엔터티가 실체·주요·행위·목적 중 어느 것에 해당하는지를 고객사 담당자에게 요청한다.


[표 1] 엔터티 분류다른 건 몰라도 실체와 주요 엔터티는 가장 많이 사용하고 있으므로 고객사 담당자가 자신 있게 분류해 줄 수 있을 것이다.


[그림 1] 실체·주요·행위·목적 순으로 엔터티 분석?

수백 개의 엔터티를 한 번에 분석할 수는 없다. [그림 1]과 같이 실체·주요· 행위·목적 엔터티로 분류하는 이유는 중요도에 따른 분석 및 설계 우선 순위를 정하기 위해서다.

이 글의 앞에서 소개했듯이 20%로 80% 일을 할 수 있으니 얼마나 효율적인가!

? 엔터티 정체 파악

그 많은 엔터티 가운데 20%의 엔터티를 분류했다. 이제 엔터티 정체를 파악할 차례다. 엔터티가 어떤 데이터를 관리하는지를 확인하는 것이다. 다른 말로는 엔터티를 정의(Definition)하는 일이다.

너무 걱정할 필요는 없다. 이 과정은 20% 엔터티를 분류해준 고객사 담당자와 함께할 것이기 때문이다. 먼저 리버스 ERD를 보기 좋게 엔터티 명에 공통된 단어가 있는 것끼리 모아 놓고 좌우 정렬한다. 엔터티 명에 공통된 단어가 있으면 관련성이 있을 가능성이 높다. 다음으로 고객사 담당자와 인터뷰를 하여 엔터티 하나 하나를 보면서 어떤 데이터를 관리하는지, 업무적 특성은 있는지, 사용 빈도는 어떻게 되는지, 관련된 엔터티는 어떤 것인지를 물어보고 기록한다.

이때 유의할 점은 아래와 같다.

(1) 한 개 엔터티에 대해 많은 시간을 들이지 않는다
고객사 담당자가 술술 풀어 놓으면 그 만큼 중요한 엔터티란 뜻이다. 반대로 머뭇거리며 헷갈려 하면 덜 중요한 엔터티로 볼 수 있다.
지금은 전체적인 윤곽을 잡는 단계임으로 한 곳에 너무 많은 시간을 들이지 않도록 한다.

(2) 너무 깊게 들어가지 않는다
고객 담당자와 인터뷰는 어떤 데이터를 관리하고, 어느 정도 중요하고, 다른 엔터티와 관계가 있는 것을 대략적으로 파악하기 위한 목적이므로 너무 깊게 들어가지 않는다. 간혹 A부터 Z까지 설명하려는 열정이 넘치는 고객사 담당자를 만날 수도 있다. 현장에서 모델링을 하다 보면, 적절한 타이밍에 말을 끊는 지혜로운 기술(?)도 필요하다.

(3) 중요한 속성을 물어본다
중요한 속성이 무엇인지 물어보되 즉각적인 대답이 없으면 그냥 넘어간다. 고객사 담당자는 유지·보수를 하면서 자주 사용하는 속성은 한 번에 알아본다.
속성은 엔터티를 구성하는 요소다. 속성을 분석하면 엔터티 정체를 더 확실히 파악할 수 있다.

식별자 분석

식별자(Identifier)는 데이터를 유일하게 구분할 수 있는 한 개 속성 또는 여러 개의 속성 집합이다. 또한 엔터티를 대표하는 속성 또는 속성 집합이다.
식별자를 흔히 PK(Primary Key)라고 생각하는데 엄밀히 말하면 식별자와 PK는 유사 하지만 다르다. 하지만 여기서는 식별자와 PK를 같은 의미로 사용하겠다.

식별자를 이해하기 위해서는 함수 종속성(Functional Dependency)을 거론하지 않을 수 없다. 함수 종속성은 정규화를 이해하는 이론이기도 하다. 여기서는 간략하게 설명한다.

함수 종속성은 데이터들이 어떤 기준 값에 의해 종속되는 현상을 지칭하는 것이다. A가 B에 의해 결정된다면, B를 결정자(Determinant)라고 하고 A는 종속자(Dependent)라고 한다. 만일 모든 속성에 대한 결정자가 있다면 이를 식별자라고 한다. 즉 식별자를 찾는 것은 모든 속성의 결정자를 찾는 것이다.


[그림 2] 함수 종속성의 예시?

[그림 2]에서 사원번호는 사원이름, 전화번호, 주소를 결정하는 결정자다. 만일 이 엔터티를 ‘사원’이라고 하면 ‘사원’ 엔터티의 식별자는 ‘사원번호’다. 식별자는 엔터티가 관리하는 데이터의 성격을 규정하고 엔터티를 대표하는 것이므로 정확한 식별자 도출은 좋은 데이터모델을 만드는 기본이자 핵심이다.

데이터 모델링 자료들을 보면 후보 식별자, 주 식별자, 인조 식별자, 대체 식별자 등 여러 종류의 식별자가 나와 혼란스러울 수 있다. 여기서 분류 기준에 따른 식별자의 종류를 간단하게 알아본다(식별자에 대한 자세한 소개는 이어질 연재에서 다루겠다).

(1) 대표성을 기준으로 식별자 분류
엔터티를 대표하는지 여부에 따른 식별자 분류다.


[표 2] 대표성을 기준으로 식별자 분류?

(2) 대체 가능 여부를 기준으로 식별자 분류
엔터티에 원래 존재하는 속성으로 구성됐는지 여부에 따른 분류다.


[표 3] 대체 가능 여부를 기준으로 식별자 분류?

(3) 속성 수 기준으로 분류
식별자를 구성하는 속성이 1개인지, 여러 개인지에 따른 분류다.


[표 4] 속성 수 기준으로 분류?

식별자 분류에 따른 식별자 종류를 예제를 통해 확인해 보자.

[그림 3] 주문내역 엔터티 1?

위 주문내역 엔터티는 다음과 같이 설명할 수 있다.
- 2개의 후보 식별자, 후보 식별자 ①과 후보 식별자 ②가 있다.
- 2개의 후보 식별자 중 후보 식별자 ①이 주 식별자로 선정돼 후보 식별자②는 대체 식별자가 됐다.
- 주 식별자는 본래 엔터티에 있는 속성으로 구성됐기 때문에 본질 식별자이다.
- 주 식별자는 여러 개의 속성으로 구성됐으므로 복합 식별자다.


[그림 4] 주문내역 엔터티 2?

주문내역 엔터티의 식별자를
- ‘주문번호’로 변경했다. ‘주문번호’는 본래 엔터티에 있는 속성이 아닌, 인위적으로 생성한 속성이므로 인조 식별자를 주 식별자로 했다.
- 주 식별자는 한 개 속성으로 구성됐으므로 단일 식별자다.
- 주 식별자를 ‘주문번호’로 선정해 2개 후보 식별자는 대체 식별자가 됐다.

식별자는 데이터의 유일성을 보장하고, 데이터를 생성하는 기준이 돼야 한다. 따라서 유일성·최소성·안전성·활용성·비암호화·최소길이·고정길이 원칙에 부합한 것을 지정해야 한다.
그러나 안타깝게도 기존 모델의 식별자는 이러한 원칙에 따라 지정하지 않고 잘못 지정한 경우가 많다. 식별자를 잘 못 지정한 몇 가지 사례를 알아본다.

(1) 검색조건 사용을 이유로 식별자에 포함하는 경우
현장에서 가장 많이 발견되는 경우다. 데이터의 유일성을 보장하는 데 관여하지 않는 속성이나 검색조건에 많이 사용되는 속성을 식별자에 포함시키는 것이다. 별도 인덱스를 생성하지 않고 PK 인덱스에 포함시키는 형태다.


[그림 5] 고객기본 엔터티‘고객기본’ 엔터티의 식별자는 ‘고객번호’다. 그런데 검색조건에 ‘고객명’이 많이 사용되기 때문에 식별자로서의 자격이 없는 ‘고객명’을 식별자에 포함한 사례다. 하지만 ‘고객명’을 식별자에 포함해 PK 인덱스를 생성하더라도 복합 인덱스의 첫 번째 컬럼이 ‘고객번호’이므로 검색조건에 고객명만 입력한다고 인덱스를 탈 수 없다.


[그림 6] 계좌거래내역 엔터티이 엔터티의 본질 식별자는 ‘계좌번호+거래일시(연월일+시분초)’다. 원래 존재하는 속성은 아니나 관리적인 측면에서 계좌번호에 종속적으로 증가하는 일련번호인 ‘거래일련번호’를 추가해 ‘계좌번호+거래일련번호’를 주 식별자로 했다.

‘거래구분코드’는 해당 거래가 어떤 거래(입금·출금·취소 등)인지를 구분하는 것이다. 이는 데이터의 성격을 나타내는 것이지 데이터의 유일성을 보장하는 데 참여하는 속성은 아니다. 하지만 계좌번호별 ‘입금’ 거래를 조회하는 검색조건으로 많이 사용된다는 이유로 식별자에 포함한 사례다.

이것을 PK 인덱스로 생성한다면 복합 인덱스의 첫 번째 컬럼이 ‘계좌번호’이므로 인덱스를 탈 수도 있겠지만 ‘거래구분코드’는 세 번째 컬럼이므로 결국 인덱스를 효율적으로 사용하지 못하게 된다. 또한 불필요한 컬럼을 인덱스로 만들어 인덱스 사이즈를 증가시켜 결국 시스템 성능에 좋지 않은 영향을 미친다.

(2) 데이터가 변경되는 속성을 사용한 경우
상위 엔터티 식별자 값을 업데이트하면 식별자를 상속받는 하위 엔터티 값 역시 업데이트해야 참조무결성을 지킬 수 있다. 만일 상위 엔터티 식별자 값을 변경했는데 상위 엔터티의 식별자를 참조 키로 갖고 있는 모든 하위 엔터티 참조 키 값을 같이, 그것도 동시에 업데이트하지 않으면 참조무결성이 깨지게 된다. 더불어 조인 시 엉뚱한 결과가 조회된다. 메타시스템으로 참조 관계가 관리되고 있더라도 동시에 참조 키 값을 업데이트하는 것은 현실적으로 불가능하다.


[그림 7] 결재 프로세스 ERD?

‘결재진행내역’ 엔터티의 정확한 식별자는 ‘결재번호’다. 그런데 ‘결재진행상태코드’가 검색조건에 많이 사용된다는 이유로 식별자에 포함했다. ‘결재진행상태코드’는 결재가 진행됨에 따라 그 값(상신·취소·대기·검토·승인·반려·삭제)이 업데이트되는 속성이다.

[그림 7] ERD와 같이 상위 엔터티인 ‘결재진‘결재항목별내역’ 엔터티의 ‘결재진생상태코드’도 업데이트돼야 한다. 만일 제때 업데이트되지 않으면 조인 시 엉뚱한 결과가 조회될 것이다. 정상적으로 ‘결재진행내역’ 엔터티의 ‘결재진행상태코드’를 일반속성으로 했다면, 값이 업데이트돼도 하위 엔터티에는 전혀 영향이 없다.

따라서 식별자는 그 값이 변경되지 않는 속성을 선정해야 한다. 만일 적절한 속성이 없는 경우 인조 식별자(본질 속성+인조 속성으로 구성)를 도입하고 변경되는 속성은 일반속성으로 해야 한다.

(3) 암호화한 속성을 사용한 경우
개인정보 보호 강화에 따라 기업 또는 기관에서는 개인정보 보호조치를 해야 한다. 이 중 DB 암호화는 관리적·기술적 보호 조치의 빈 틈을 이용해 데이터를 반출하더라도 개인정보가 노출되지 않는 최종적이며 강력한 조치다.

문제는 DB 암호화 시 적용하는 암호화 방식과 키 관리 방식에 따라 원천 값과 암호화 후 값이 다르다는 점이다.


[그림 8] 암호화한 속성 사용[그림 8] ERD에서 ‘개인관계자기본’ 엔터티에서 ‘관계자주민등록번호’가 식별자다.

평문(plain text) 주민등록번호를 암호화 알고리즘과 암호화 키로 암호화하면 값이 달라진다. 기간별 또는 시스템간 암호화 키를 다르게 사용했다면, 동일 평문에 대한 암호화한 값이 다르므로 암호화한 값으로 조인하는 것은 무의미하다.

암호화한 값을 복호화(decoding)해 조인하는 방법이 있다. 하지만 이때는 인덱스를 탈 수 없다. 온라인성 단건 조회가 아닌, 배치성의 다량 조회일 때는 모든 데이터를 복호화해야 하므로 결과 출력 시간이 크게 늘어 난다(타임 크리티컬이 강조되는 배치 처리일 때 시간 내 처리가 되지 않을 수 있어서 업무 개시시간을 보장하지 못할 수도 있다).따라서 암호화 대상이 되는 속성이 포함되지 않은 후보 식별자를 주 식별자로 하거나 적절한 후보 식별자가 없을 경우 인조 식별자를 사용한다.


[그림 9] 인조 식별자로 암호화 조인문제에 대처[그림 9]처럼 ‘관계자주민등록번호’ 대신 인조 식별자인 ‘개인관계자번호’로 대체해 암호화로 인한 조인 오류와 성능 저하에 대비한다.

(4) 길이가 일정하지 않거나 긴 속성 사용 시
물리모델은 DB의 물리적 특성과 성능을 고려해해야 하므로 주 식별자 속성 값이 짧고, 고정 길이여야 효율적이다(주 식별자는 PK 인덱스로 생성되고 디스크에 저장되는 공간이 적을수록 디스크 I/O를 줄일 수 있다. 데이터 1건은 몇 바이트 차이밖에 나지 않지만, 전체를 보면 무시하지 못 할 성능 차이가 발생할 수 있다).


[그림 10] 데이터 타입 길이가 가변적인 ERD[그림 10] ERD는 ‘이벤트내역’ 엔터티의 식별자가 ‘이벤트명’으로 돼 있고, 데이터 타입 길이가 가변 문자열 200바이트로 돼 있다. 이벤트 명을 유일하게 부여할 수 있는 한계가 있고, 값이 길고 길이도 일정하지 않다. 이에 따라 하위 엔터티나 참조 키를 갖고 있는 엔터티 간 조인 시 효율적이지 못 하다. 이런 경우 인조 식별자인 ‘이벤트번호’로 대체하면 값이 짧아져 조인 시 더 효율적이다.


[그림 11] 인조 식별자로 대체일반적으로 ~명, ~내역, ~금액, ~료 등의 속성은 될 수 있으면 주 식별자에 포함하지 않는 것이 좋다.


[그림 12] 금액 속성을 식별자로 한 ERD[그림 12] 엔터티는 금액 구간별로 담보등급을 관리하는 엔터티다. 시작과 종료 금액을 식별자로 한 것이 타당성이 없는 것은 아니다. 하지만 시작 금액과 종료 금액을 식별자로 하더라도 기본키 제약조건(Primary Key Constraint)으로 데이터 입력 시 금액 구간의 중첩을 막을 수는 없다.


[그림 13] 금액 구간 중첩 데이터 입력 예시어차피 금액 구간의 중첩을 막으려면 별도의 로직을 애플리케이션에서 구현해야 한다. 따라서 시작과 종료 금액을 식별자가 아닌, 일반 속성으로 하더라도 금액 구간별 담보등급을 구하는 데 문제가 없다. 오히려 ‘담보등급코드’를 식별자로 하는 것이 저장 공간 및 참조 키 역할을 하는 데 더 효과적이다.


[그림 14] ‘담보등급코드’를 식별자로 지정(5) 식별자가 동일한 엔터티가 여러 개일 때
현행 모델 분석 과정에서 식별자가 동일한 엔터티가 여러 개 있는 경우를 종종 보게 된다. 식별자는 가게의 간판과 같은 역할을 한다. 한 건물에 동일한 간판을 내세운 가게가 많다면 어떻게 될까? 엔터티 명을 전사적으로 유일하게 부여(예: ‘업무명_엔터티 명칭’으로 부여)하면 엔터티를 식별할 수 있듯이, 식별자도 전사적으로 유일하게 부여하는 것이 이상적이다. 현실적으로 전사적으로 유일하게 부여하는 게 어렵다면, 최소한 한 모델 안에서는 유일하게 부여하도록 한다.


[그림 15] 식별자가 동일해 엔터티 파악이 어렵고 모델의 가독성 저하식별자를 유일하게 부여하는 방법 중 하나는 적절한 수식어를 붙여 상세하게 하는 것이다. 예를 들어 도메인은 '계좌번호'를 사용하지만 '여신계좌번호', '수신계좌번호', '외환계좌번호'와 같이 수식어를 붙이면 데이터의 성격을 직관적으로 알 수 있다.

또 '계좌번호+일련번호'도 엔터티 정의에 따라 '여신계좌번호+여신거래일련번호', ‘여신계좌번호+여신상환일련번호', '수신계좌번호+수신입금일련번호' 등으로 적절한 수식어를 붙여 상세하게 하면 유일하게 부여할 수 있다. 이렇게 하면 엔터티가 어떤 데이터를 관리하는지 직관적으로 알 수 있어 모델의 가독성을 높일 수 있다.식별자가 유일하면 참조 키를 보고도 어떤 엔터티와 관계가 있는지도 직관적으로 알 수 있다.


[그림 16] 적절한 수식어로 유일한 식별자를 부여해 엔터티 파악과 모델의 가독성 제고현장에서 흔히 볼 수 있는 식별자를 잘 못 설정한 사례를 살펴보았다. 이 상황을 염두에 두면 현행 ERD나 리버스 ERD에서 설정한 식별자의 적절성 여부를 분석할 수 있을 것이다.

다음 회에는 리버스 ERD에서 엔터티 간 관계를 어떻게 분석하고, 속성을 어떻게 분석 하는지 알아보겠다. (다음 회에 계속)

?

?

출처 : 한국데이터산업진흥원

제공 : 데이터 전문가 지식포털 데이터온에어(dataonair.or.kr)