소규모 프로젝트를 위한 데이터베이스 모델링과 설계

소규모 프로젝트를 위한 데이터베이스 모델링과 설계

물리적 모델링 실전 가이드

곽중선 l 클라우드나인에서 웹 사이트 구축 컨설팅

지난호까지 이론에 치중했으나, 이번 호에는 실질적으로 데이터베이스에 탑재할 수 있는 설계과정을 살펴보고자 한다. 이전 호에서 설명한 논리 모델을 구체화하여 물리 데이터 모델을 작성해보자. 물리 모델을 작성한다는 것은 실제 데이터베이스에 탑재 가능한 설계도를 작성한다는 의미다. 이같은 연장선상에서 데이터베이스에 테이블을 생성하고 컬럼을 정의하는데 있어서 성능에 영향을 미치는 요소들을 중점적으로 설명하고자 한다. 논리적 모델링은 설계 의도를 기술하기 위한 것이나, 물리적 모델링은 최적의 성능을 실현하기 위한 작업이다.

 

데이터 모델링에서 가장 중요한 작업은 논리 모델링이며, 지난 호에서 이를 자세히 살펴보았다. 그러나 데이터 모델링을 시스템 구축 프로세스 중 사소한 업무로 치부하고 사용자 인터페이스나 프로그램 로직을 구현하는데 집중하는 경우가 많은 게 사실이다. 게다가 모델링 툴(ER-Win) 등을 사용하고 있다면 언제라도 손쉽게 데이터베이스 스키마를 생성하고 물리적인 구현을 할 수도 있다. 단순한 절차에 따라 구현된 데이터베이스는 필요한 정보를 저장할 수 있더라도 DBMS의 특성을 살린 최적의 성능을 반드시 충족시킨다고는 할 수 없다.

개발 단계에서는 아무런 문제가 없던 시스템이 운영 단계로 넘어가면서 급격히 응답시간이 지연되거나, 심지어 시스템이 정지하기도 하는 것은 거의 물리적 모델링을 생략하거나, 적은 시간을 투자했기 때문이다. DB 설계를 잘못한 경우, 최악의 경우에는 시스템 전면 재개발이 필요하다. 따라서, DBMS 설계자는 DBMS의 특성이나 처리 효율을 이해해야 하고, 그런 특성을 고려한 모델을 설계할 줄 알아야 한다.

최근 데이터베이스 옵티마이저(optimizer)의 알고리즘이 적극적으로 개선되고 있다. 또 메모리, 디스크 등 하드웨어 부품의 가격 하락에 힘입어 데이터베이스가 과거보다 대용량의 트랜잭션을 소화할 수 있게 되었다. 이같은 결과로 보다 덜 정교한 논리모델을 그대로 구현해도 대체로 문제가 없다. 그러나 DBMS 제품의 특성, 하드웨어 그리고 처리(알고리즘, 비즈니스 규칙)의 관점에서 모델을 재검토하는 것이 중요하다. 물리 모델로 변환하는 과정에서 경험하고 얻어낸 노하우는 향후 성능 튜닝에서도 활용될 수 있다. 또한 모델의 변환 작업 이 외에도 테이블 컬럼 등의 명칭 부여, 각종 제약의 작성과 같은 부가적인 작업들도 있다.

 

논리 모델을 물리 모델로 변환하는 과정

전체 모델링 과정을 다시 한 번 정리해보자. 개념, 논리 모델에서 DBMS 구현까지의 흐름이다. 물리 모델링은 테이블의 구조와 그에 수반하는 테이블 명, 컬럼 명, 데이터 타입, 데이터 길이, NULL 옵션, 각종 제약의 정의 등을 포함한다. 그런데 데이터를 저장하기 위한 디스크 용량, 디렉토리 상에서의 위치, DBMS 제품 고유의 환경설정 등은 모델링에서 다루지 않기 때문에 실제 데이터베이스를 설치하는 과정은 별도의 학습이 필요하다.

 

물리 모델로 변환

 

물리 모델링 단계에서 절대 지나쳐서는 안 되는 것은 바로 논리모델의 ‘수정’이다. 즉, 논리 모델의 설계를 그대로 유지하려고 해서는 안 된다는 것이다. 논리모델은 업무의 모습(혹은 흐름)을 그대로 투영하여 설계자와 사용자가 손쉽게 이해할 수 있도록 하는데 그 목적이 있다. 달리 말하면, 데이터베이스를 운용하는 하드웨어를 위한 것이 아니라 인간에게 적합한 설계라는 것이다. 반면에 물리 모델은 오라클, MS-SQL, MySQL과 같은 데이터베이스 소프트웨어와 CPU, 메모리를 장착한 하드웨어가 최고의 성능을 발휘하도록 만들어야 한다. 논리 모델과 물리 모델은 언뜻 같아 보이지만 다른 것이라고 이해해야 한다.

아름다운 집을 디자인한다고 생각해보자. 최초의 구상 단계에서는 집 지을 재료는 생각하지 않아도 된다. 다양한 곡선과 아름다운 배치를 떠올려서 스케치할 것이다. 그런데, 집을 지을 재료가 콘크리트와 철근이라면 아무래도 좀 더 딱딱한 모양새로 변형해야 할 것이고, 나무라면 약간 더 곡선을 살릴 수 있을 것이다. 논리 모델과 물리 모델의 차이는 이와 같다. 따라서 논리 모델까지는 데이터베이스의 내부 원리와 특성을 잘 모르더라도 작성할 수 있었으나, 물리 모델을 작성하기 위해서는 각 데이터베이스 솔루션에 대한 지식이 필수다. 똑같은 관계형 데이터베이스라고 하더라도 오라클과 MS-SQL, MySQL은 서로 다른 성능과 특징들을 지니고 있다. 물리 모델을 설계하는 단계에서는 각 제품의 매뉴얼이나 해설서를 꼭 참고하는 것이 좋고, 필수서적 한 권씩은 필히 사서 읽어보거나 비치해둘 것을 권한다.

<그림 1> 논리 모델과 물리 모델

 

개체 통합

 

논리적 모델링을 수행할 때 게시판을 개체 정의하고 그 하위 요소로서 첨부파일, 답글, 덧글 등을 별개의 개체로 선언하고 관계를 선언하였다. 분리하여 표시하는 것이 각기 다른 정보들이 발생하는 시점이나 의미가 다름 등을 쉽게 알아차릴 수 있다는 장점을 지니기 때문이다. 즉, 논리적 모델은 업무의 의미 별로 개체를 상세히 분리해 선언한다. 하지만, 물리적 모델링에서는 개체들을 통합하는 작업부터 시작해야 한다. 애써 추출하고 분리한 개체들을 다시 모으는 작업이 점진적으로 상세히 그려간다는 모델링의 목적에 위배되는 것처럼 보일 것이다. 하지만, 이것은 정말 필요한 작업이다. 물리적 모델링의 목적은 업무에 대한 인간의 이해가 아니라, 하드웨어가 최고의 성능을 내도록 하기 위함임을 다시 기억해주길 바란다.

그렇다면, 둘 이상의 개체 혹은 테이블을 통합할 때 어떠한 장점이 있다는 것일까? 답은 I/O 처리효율 증가와 응답 시간의 단축이다. 데이터베이스는 둘 이상의 테이블을 조인(join)하는 것보다는 하나의 테이블을 참조하는 쪽이 훨씬 빠르기 때문에 테이블을 통합하는 것이 유리하다. 같은 주요 키(Primary key)를 공유하는 테이블들을 통합할 것인지, 혹은 레코드의 사이즈, 데이터의 건수, 혹은 한 블록에 저장되어 있는 레코드 수(버퍼 효율)등을 고려해 통합할 것인지를 결정한다. 보통 테이블의 사이즈가 작은 편이라면 통합하는 쪽이 효율적이다.

다시 게시판에 대한 이야기로 돌아가 보자. 게시판과 첨부파일 개체를 통합할 수 있는가를 생각해보자. 만일 게시판에 첨부할 수 있는 파일을 하나로 한정한다면 두 개의 테이블을 통합해도 된다. 단지 성능 때문에 복수 첨부파일 기능을 포기해야 하는 것은 아니지만, 가능하다면 그렇게 하는 것이 분명히 낫다. 게시물을 조회할 때마다 발생하는 데이터베이스 쿼리 횟수가 줄어든다는 것은 무시할 수 없는 이득인 것이다.

<그림 2> 테이블의 통합

 

레코드 길이에 따른 테이블 분할

 

앞에서 다룬 이야기는 반대로 하나의 테이블을 성능을 위해 둘로 쪼개는 경우도 있다는 것을 암시한다. 한 개체에 포함된 속성의 수가 많아지거나 텍스트 입력을 위해 길이를 크게 설정할 경우 레코드의 최대값이 데이터베이스에서 정의하고 있는 블록의 크기를 넘어버릴 수 있다. 블록 사이즈를 크게 만들어 주면 되지만, 블록 사이즈의 변경은 시스템 전체에 큰 영향을 주기 때문에 간단히 행할 수 있는 것이 아니다. 그러나 아무런 조치도 하지 않는다면 블록에 담을 수 없는 레코드는 확장 영역을 사용하게 되어 처리 효율이 극단적으로 하락하게 된다. 이러한 경우에는 테이블을 분할한다. 분할에 의해 테이블의 조인(join)이 발생하게 되지만 확장 영역으로 분할된 레코드를 검색하는 것보다는 훨씬 부하가 줄어든다.

<그림 3> 비정규화 예

 

참조 엔티티 속성 추가(비정규화)

데이터베이스 모델링에서 중요하게 강조되는 기법으로 정규화(normalization) 과정이라는 것이 있다. 개별 테이블에서 반복되는 데이터가 없어야 한다. 일관성을 유지해야 한다는 원칙, 그리고 이를 수행할 수 있는 방법을 아우르는 것이다. 정규화에서는 기본키(primary key)에 의존하지 않는 속성은 밖으로 꺼내 다른 엔티티에 배치한다. 예를 들어 게시판에 글을 쓴 사용자 정보를 표현할 경우, 게시판 테이블에서는 작성자의 ID만을 포함시키고 상세 정보는 사용자 테이블에서 조인(join)해 읽어오도록 설계한다. 그러나 물리모델로 변환할 때에는 반대로 비정규화를 하기도 한다.

즉, 게시판 작성자의 성명을 게시판 테이블에 포함시키는 것이다. 이렇게 함으로써 게시판 출력 시에 조인(join)을 하지 않고 단일 테이블을 쿼리함으로써 성능을 높일 수 있게 된다. 반면에, 실수로 사용자의 성명이 잘못 입력되어 추후에 사용자 정보를 수정하더라도 게시판에 기록된 작성자 성명은 변경되지 않는다. 이것이 비정규화의 단점이다.

 

필자 메모

기본에 충실해라


데이터베이스를 이해하고, 다양한 이론과 실전을 경험하다 보면 누구나 대용량 시스템을 자유롭게 설계하고, 높은 응답 성능을 낼 수 있도록 구축하고자 하는 욕심이 생기게 마련이다. 그렇다면 대체 무엇을 어떻게 학습해야 하는 것일까? 간단한 테이블을 생성하고, 쿼리를 작성하는 것은 그리 어렵지 않다. 하지만, 운영 데이터가 늘어갈수록 처리 속도는 점차 떨어지고 심지어 다운되는 현상이 발생하기도 한다. 성능을 보장하기 위해서 하드웨어의 용량을 늘려야만 할까?
지금까지 다양한 고객의 데이터베이스를 구축하고, 튜닝해온 경험과 외부의 컨설팅 업체에 의뢰하여 지적받은 설계상의 문제점들에 대한 답들을 요약하면, 바로 기본에 충실해야 한다는 것이다. 데이터베이스를 튜닝하기 위해서 아주 어려운 기술이나 복잡한 도구를 사용해야 한다는 것은 대개 편견이거나, 오해에 불과하다. 하드웨어를 증설하거나, 각종 설정을 조정한다고 한들 성능 개선 효과는 의외로 높지 않다. 예를 들어, 웹 사이트 응답 시간이 10초인데, 2배로 빨라 진다해도 사용자는 여전히 웹 사이트가 느리다고 느낄 것이다.

테이블을 설계하는 시점에 어떻게 분할할 것인가, 주요 키(primary key)와 외부 키(foreign key)를 정의하는 방식, 그리고 인덱스(index)를 어떻게 정의하는 하는가, 쿼리를 수행할 때 어떤 방식으로 조인(join)하는가에 따라서 수배에서 수십 배에 달하는 성능 차이를 가져온다. 쿼리가 수행되는 과정을 이해하고, 테이블 간의 조인(join)과 관계(relation)의 의미를 이해하며, 인덱스가 제대로 사용되는지를 정확히 알아야 한다.
이런 기술이나 이론들은 거의 기초 과정에서 배우게 되는 것들이다. 쿼리가 실행되는 과정(execution plan)을 이해해야 한다. 그리고 데이터베이스가 테이블 데이터들에 접근하는 과정을 알아야 한다. 데이터베이스 제품 혹은 하드웨어가 성능을 보장해줄거라는 환상을 가지지 말라. 그것은 단지 도구일 뿐이다. 깊이 알지 못하면서 본래의 성능을 끌어낼 수 없다. 쿼리와 테이블이라는 용어를 아는 것에 그치지 말고 그 원리를 이해해야 한다. 숨겨진 것은 없다, 단지 발밑을 보지 않았기 때문인 것이다.

코드 개체의 취급

 

직원들의 직위, 학생들의 학년, 학부 등과 같은 데이터가 취할 수 있는 한정된 값이나 값에 대한 명칭(화면 표시에 사용)을 관리하기 위해 코드 개체(코드 테이블)를 정의하고 자료를 저장하는 개체와 관계를 설정하는 방법들이 자주 사용된다. 그러나 코드 개체를 선언하고 데이터를 저장하는 개체와 함께 물리적 모델에 선언하면 개념적으로 이해하는 데는 무리가 없지만 작은 테이블이 다수 발생한다. 더불어 자료를 조회하는 쿼리가 복잡해지고, 자료의 관리가 어려워진다. 게다가 참조 제약을 일일이 설정하는 과정도 매우 번거롭기 때문에 물리적 모델을 작성할 때에는 다음 중 한 가지를 선택한다.

① 개별 구분 테이블을 하나로 모은다(구분 분류, 구분 코드).
② 테이블로 구현하지 않는다(프로그램에서 관리).
③ 테이블로 구현하는 것과 프로그램에서 관리하는 것으로 분리한다.

 

물리 모델에서 설정하는 컬럼

 

지금까지 논리 모델의 변환 과정에 대해서 살펴보았다. 이제 논리모델에서 나타나지 않았지만 추가해야 할 개체와 특성에 대해서살펴보자.

 

시스템 운영에 필요한 컬럼 추가

 

운영 시 복구나 분석 작업을 고려해 처음 데이터를 기록한 작성일시와 작성자, 이후 데이터를 변경할 때마다 최신 갱신일자와 갱신자 등을 설정하기도 한다. 게시판을 예로 들자면 작성자의 ID뿐 아니라 해당 게시물을 작성한 PC의 주소를 기록할 수 있다. 감사(audit)나 시스템 장애(system fault)를 대비하여 모든 테이블에 일률적으로 설정하기도 한다. 참고로, 이 경우에는 트리거(insert나 update 등의 액션이 발생했을 때 이름이나 시스템 시간을 설정)나 insert, update 메소드 안에서 기록하도록 설정해두면 개별 애플리케이션에서 이러한 처리를 부가적으로 할 필요가 없어진다.

 

데이터 타입 정의

 

논리 모델에서는 도메인을 규정하고 논리적인 데이터 타입이나 길이를 정하였다. DBMS는 제품마다 구현되어 있는 데이터 타입이 다르기 때문에 논리 모델 상에서 규정한 데이터 타입을 DBMS의 데이터 타입으로 변환할 필요가 있다. 자료와 데이터베이스 제품 유형에 따라 다음과 같은 타입과 길이를 권장한다.

·성명, 주소, 제목 등 수십,수백 자리의 짧은 문장 최대 길이를 예측할 수 있어야 하며, 예측한 최대 길이보다 긴 문자열이 입력되는 경우는 잘려서 입력된다. 가변길이 문자열 타입을 varchar(오라클에서는 varchar2)사용하며, 데이터베이스 제품과 버전에 따라 저장할 수 있는 최대 문자열 길이가 일정하지 않지만 각 제품의 버전에 따른 기술문서를 숙지해야 한다. 일반적으로 영문 기준으로 2000자까지 저장 가능하다. 한글을 입력할 때는 한 글자가 2byte를 차지하므로 절반 길이의 문자열을 저장할 수 있다는 것을 잊지 말아야 한다. 이름, 명칭을 저장하는 항목의 길이는 50자리, 짧은 설명 등을 포함하는 컬럼은 500자리라는 식의 간단한 문자열 크기 제한 규칙을 정해두는 것도 유용하다. 이름을 저장하는 컬럼을 설계하면서 한글 이름을 떠올리고 20자리 정도를 설정했는데, 외국인 이름이 입력되어서 중요한 정보를 상실하는 경우도 발생한다. 따라서 넉넉한 자릿수를 설정하는 것을 습관화 하는 것이 좋다.

·설명, 게시판 내용 등의 긴 문자열 긴 문자열을 저장하고자 할 경우 신중해야 한다. 대부분의 데이터베이스는 아주 긴 문자열(최대 2GB)을 저장할 수 있는 자료형을 제공하지만, 제품마다 그 명칭이 틀리고(Oracle에서는 CLOB 혹은 long 타입, MS-SQL은 text 타입) 읽고 쓰는 방식이 까다로울 수도 있다. 따라서, 영문 2000자를 넘지 않는다고 판단될 경우에는 가변길이 문자열 타입(varhar, varchar2)을 사용한다(데이터베이스 제품과 버전에 따라서 2000자 이상을 저장할 수도 있다). 게다가 아주 긴 문자열을 저장할 수 있는 타입들은 하나의 테이블에 단지 하나의 컬럼을 선언할 수 있다거나, 읽고 쓰는 속도가 상대적으로 느리다는 등의 단점이 있다. 마치 사용하지 말 것을 권장하는 것으로 해석될 수 있지만, 그런 의미가 아니라 장단점을 알아야 개발 단계의 문제 발생을 사전에 방지할 수 있다는 것이다.

·날짜 및 시간 모든 데이터베이스 제품들은 날짜와 시간을 저장할 수 있는 자료 형식(date, datetime 등)을 제공한다. 더불어 특정 제품에서 제공하는 날짜(시간) 타입을 사용하면 날짜(시간)를 출력하거나, 계산 처리(이전 날짜, 이후 날짜, 두 날짜의 간격 계산 등)를 하기 위한 함수를 사용할 수 있다는 이점이 있다. 그러나 특정 데이터베이스 제품에서 제공하는 함수들은 여지없이 다른 제품에서는 사용할 수 없어 새롭게 익혀야 한다. 게다가, 데이터베이스가 교체될 경우 프로그램을 재개발하거나 다른 버전을 제공해야 하는 경우도 발생한다. 물론 특정 데이터베이스에서만 동작할 것이 확실한 경우라면 괜찮지만, 신중히 결정하기를 권한다. 필자의 경우는 가급적 데이터베이스의 영향을 적게 받고 표준 SQL만으로 개발할 수 있도록 날짜의 경우는 8자리(YYYYMMDD), 시간은 6자리(HHMI24SS)의 char 타입을 선언한다.

·고정 크기의 문자열 데이터베이스에서 짧은 문자열을 저장할 때는 가변길이 문자열 타입(varchar, varchar2 등)과 고정길이 문자열 타입(char) 중 한 가지를 사용할 수 있는데 대개 가변길이 문자열을 사용하게 된다. 두 가지 유형 어느 쪽을 사용할지 결정할 때 성능 상의 차이는 무시할 만한 수준이다. 고정길이 문자열은 해당 컬럼이 ‘Y’, ‘N’ 등의 한자리 플래그(flag) 값이나, 코드를 저장한다는 것을 암시하기 위한 목적으로 사용된다. 즉, 개발자나 사용자가 손쉽고 빠르게 컬럼의 기능을 해석할 수 있도록 돕는 것이다. 그렇다고 코드 컬럼인 경우에만 써야 한다는 규칙은 없다. 업계에서 많이 사용되는 유용한 관습이라고 보면 된다.

·이진 자료(binary data) 최신 데이터베이스 제품들은 이진 데이터(binary data)를 저장할 수 있는 타입을 제공한다. 데이터베이스 설계를 처음 접하는 개발자(혹은 프로젝트 리더)들은 이런 유형을 적극적으로 사용해도 되는 것인지 쉽사리 판단하기 어렵다. 예를 들어, 게시판의 경우 첨부 파일을 디스크에 저장할 것인가, 데이터베이스에 저장할 것인가 하는 문제이다. 필자의 경험 뿐 아니라 대다수의 프로젝트에서는 데이터베이스에 파일의 경로만을 가변길이 문자열 컬럼(varchar)에 저장하고 실제 파일은 디스크에 보관한다. 이진 데이터를 보관하게 되면 데이터베이스의 입출력 부하, 백업 및 복구 시간이 급격히 늘어난다. 또한 관계형 데이터베이스는 주로 텍스트와 숫자 정보를 고속으로 처리하기 위해 설계되었다는 것을 잊지 말아야 하는데, 달리 말하자면 부가적인 기능은 말 그대로 부가적인 기능일 뿐이다. 추가 기능이 제공된다고 해서 그것을 사용하는 것이 언제나 최선일 수는 없다. 설계에 대한 판단과 그에 따른 책임은 분명히 개발자 혹은 프로젝트 관리자가 져야 하는 것이다. 자칫 설계 시의 판단 착오 때문에 적절한 성능이 발휘되지 않았을 때, 뒤늦게 제품의 특성을 잘 몰랐다고 항변해야 소용없는 것이다. 기본값 및 NULL 허용 여부 설정 데이터베이스 기반 애플리케이션을 개발하면서 개발자들이 가장 자주 보게 되는 오류는 무얼까? 물론 데이터베이스 관련 개발에 국한된 것은 아니지만, 아마 NULL 관련 오류(Null pointer 오류라고도 한다)일 것이다. 데이터베이스의 특정 항목에 빈값이 들어 있고, 해당 항목을 읽어내는 프로그램이 주의하지 않을 경우(혹은 빈 값 검사를 안 할 경우)에 NULL 오류가 발생한다. 그런데, 이런 오류는 의외로 쉽게 비켜갈 수 있다. 컬럼을 물리적으로 정의할 때에 기본 값으로 빈 문자열이나, 0과 같은 숫자를 설정하는 것이다. 혹은 입력 시점에 빈 상태를 허용하지 않는 것이다. 테이블을 생성하는 것은 한번 뿐이지만 참조하는 프로그램 코드 위치는 매우 다양할 수 있다. 테이블을 정의할 때 조금 더 신경을 쓰면 프로그램 코드 길이와 테스트 시간을 줄일 수 있다. 더불어 기본값을 지정하면 SELECT 쿼리를 작성하면서 특정 컬럼이 빈값일 때 유효한 값으로 변환하는 NVL 함수 등의 사용 빈도를 줄일 수 있다. 이것은 읽기 성능을 작게나마 향상시킨다.

<그림 4> 데이터 타입 정의

 

물리 명칭 설정

 

논리 모델을 물리 모델로 변환할 경우 모델의 변환이나 속성의 추가 이외에 물리 구현 명칭을 부여하게 된다.

 

테이블, 컬럼명 설정

 

논리 모델에서는 개체와 속성명에 한글 명칭을 부여한다. 그러나 일반적으로 사용되는 프로그래밍 언어에서는 개체의 명칭에 한글을 허용하지 않기 때문에 영문 명칭으로 부여하게 마련이다. 개체의 명칭을 부여하기 위해 명명 규칙(naming rule)과 명명 사전을 정의해 둘 필요가 있다. 이를 통해 중복이 제거된 논리 모델의 상태가 구현까지 이어져 개발이나 유지보수 시 작업의 효율이 높아진다. 또한 DBMS 상의 스키마나 프로그램 개발에서도 통일된 이름을 사용할 수 있기 때문에 영향을 미치는 범위를 쉽게 찾아낼 수 있는 장점도 있다. 구체적으로 설명하자면 한글 이름을 그대로 로마자 표기하면 단어가 길어지므로 한글 논리명에 대해 영문 약어를 설정하고 그 조합으로 명칭을 부여하는 방법을 널리 사용한다. 예를 들어 번호는 ‘NO’, 년월일은 ‘ymd’, 명칭은 ‘nm’으로 설정하고 주문 번호는 order_no, 전자문서는 elec_doc으로 선언하는 식이다.

 

제약 명칭 설정

 

테이블이나 컬럼명 이외에 명명이 필요한 개체로 각종 제약(constraints or restrict) 명칭이 있다. 만약 정의 시에 제약을 부여하지 않으며 DBMS가 자동으로 ‘sysxxxxx’(오라클)와 같은 형태로 일련번호를 부여한다. 실제로 제약 에러가 발생했을 때, 제약 이름만 봐서는 어디에 설정된 제약인지 알 수 없어 에러가 발생한 곳을 찾기 어려워지므로 제약명은 부여하는 것이 좋다. 마찬가지로 주요 키(primary key) 제약은 한 테이블에 하나만 존재하기 때문에 PK + 테이블명으로 선언한다.

<그림 5> 명명 사전 예

 

명명 표준(naming rule)

명명법 혹은 네이밍 표준(naming rule)이라는 용어에 대해서 익숙하지 않을지도 모른다. 하지만, 명명 표준은 프로그래밍에서의 변수, 클래스 이름 정의, 네트워크 구성 등 시스템 정의에서 광범위하게 필요로 하는 것이다. 간단한 시스템을 소수가 개발하는 경우에는 모든 대상의 이름을 부여함에 있어서 표준화가 굳이 필요하지는 않지만, 다양한 사람이 함께 일하는 기업이나 프로젝트에서는 상호간의 혼란을 막고, 대상에 대한 정확한 이해를 도우며, 유지보수를 돕기 위해 이름을 정의하는 표준을 설정하는 것이 중요하다.
간단하지만 구체적인 속성 정의 사례를 들어 명명 표준의 필요성에 대해 알아보도록 하자. ‘날짜’라는 항목은 매우 빈번하게 사용되는 속성인데, 년월일(YYYYMMDD) 처럼 연도(year), 월(month), 일자(date)까지만 기록하는 경우, 날짜와 시간을 함께 기록하는 경우가 있다. 이 두 가지 경우에 모두 날짜(date)라는 속성 명칭을 부여한다면 혼란이 발생하고 말 것이다. 또한 사람들을 구분하기 위해서 아이디(ID) 속성을 사용하는데 그 값으로 사원번호, 주민등록번호, 학번 등을 사용할 수 있기에 역시 같은 문제가 발생할 수 있다. 나아가서 사람의 이름(name) 항목을 생각해보자. 사람의 이름(full name)은 성(family name)과 이름(given name)으로 구분될 수 있다.


명명 표준은 분명 표준이라는 단어를 포함하고 있지만, 공유할 수 있는 표준이 없다. 다양한 기업, 다양한 업무가 존재하기 때문에 각 개체와 속성에 맞는 정확한 표기법을 통일할 수 없는 것이다. 그렇기 때문에 데이터베이스 구축 경험이 많은 업체들은 용어집이라는 책자를 만들어 직원들에게 배포하기도 한다. 작은 프로젝트에서는 표준화가 이루어지기 어려운 것이 현실이지만, 팀 내에서 표준화에 대한 노력을 기울일 필요는 있다. 매사가 저절로 이루어지는 법은 없으니 말이다.

명명법의 기본적인 개념은 자주 사용되는 기본 용어(단어)를 정리하고 데이터의 의미와 역할에 맞는 조합 규칙을 정해둔 다음, 한 번에 이해할 수 있는 이름을 붙이는 것이다. 고전적인 명명 표준으로는 기본어(Prime), 수식어(Qualfier), 분류어(Class)의 조합으로 나타내는 방법이 있다. [Q] + P + C의 순서로 조합하며, 단어는 단어집에 등록되어 있는 단어 중에서 골라내고 없는 경우 단어집에 추가한다. 또한, 주요어는 경우에 따라서 수사어로 사용된다. 기본어(주제어)는 정의하고 싶은 대상을 나타내며, 수식어는 기본어의 의미를 보충하거나 제한한다. 분류어는 속성을 분류하는 것인데, 타입(type)이라고 설명할 수도 있다. 아래 단어집 예시를 보도록 하자.

기본어(주제어) : 매출, 직원, 구매, 상품, 문서, 장비, 배송, 고객 등 개체나 속성 자체를 표현하는 단어
수식어 : 일별, 월별, 기간, 영역, 연령 등 범위를 나타내거나 기본어를 구체화하는 단어
분류어 : 코드, 번호, 종류, 일시, 금액, 비율, 회차, 이름, ID 등 값의 형태를 나타내는 단어

 

참조 제약

 

논리모델에서 주요 키(primary key)와 외부 키(foriegn key)로 관계가 설정되어 있는 곳은 모두 참조 제약으로 구현되어야 한다. 툴(tool)을 사용하고 있다면 자동으로 제약을 생성해 주지만, 물리 모델로 변환할 때 제약 그 자체를 구현할 것인지 아닌지를 생각해볼 필요가 있다. 참조 제약을 정의하면 데이터의 무결성을 확실하게 유지할 수 있다. 그러나 프로그램이 복잡하거나 성능 상의 문제가 발생할 것을 예상한다면 제약을 풀고 프로그램에서 정합성을 유지하는 방법을 고려할 수도 있다. 외부 키를 사용하는 것은 데이터의 무결성을 보장해주는 장점이 있지만 insert, update 시 연결된 테이블을 검색하기 때문에 성능을 떨어뜨린다.

 

그 외 데이터베이스로 구현 가능한 규칙

 

데이터베이스 모델링의 목적은 자료 구조를 정의하는 것이라고 짧게 정의할 수 있다. 그런데, 데이터베이스의 기능은 그렇게 단순하지 않으며 다양한 기능을 추가로 제공한다. 업무 규칙(business rule) 혹은 각종 처리(process)를 DB 내에서 정의할 수 있다는 것이다. 값 제약(value restric), PK 제약, 참조 제약 이외에도 트리거(trigger)나 저장 프로시져(stored procedure) 등 각종 스크립트를 이용하여 규칙을 정의할 수 있다. 규칙을 정의한다는 표현을 달리 해석하면 데이터베이스 내에 프로그램을 선언하고 실행할 수 있다는 의미다. 이로써 얻을 수 있는 장점은 유지보수 단계에서 사용자 프로그램을 컴파일하거나, 재배포하지 않고 규칙을 변경할 수 있다는 것이다. 게다가, 데이터를 변경하는 프로그램이 데이터베이스 내에 저장되어 있기 때문에 처리 속도 또한 수배에서 수십배까지 빠르다. 이런 장점 때문에 대규모 데이터베이스 개발 시에는 트리거와 저장 프로시져를 적극적으로 사용한다.

 

트리거 정의

 

트리거는 테이블의 insert, update, delete 등 작업(action)의 전후에 테이블에 대한 조작(operation)을 정의한 것이다. 시스템 운용 시에 일률적으로 필요한 컬럼의 추가나 DBMS에서 제공하는 참조 제약을 보완하는 목적으로 자주 이용된다. 예시한 소규모 웹 사이트의 경우에는 사용자의 소속이 변경되는 경우, 자동으로 변경된 사용자 이름을 게시판 테이블에 반영하고자 할 경우에 트리거를 설정할 수 있다. 트리거는 데이터베이스 내부에서 동작하는 프로그램이기 때문에, 응용 프로그램을 재배포하거나 다시 컴파일할 필요없이 쉽게 설치, 제거, 변경이 가능하다. 단, 트리거나 저장프로시져의 작성법은 데이터베이스 제품마다 조금씩 다르기 때문에 개발하려면 해당 제품 가이드와 샘플을 참고해야 한다.

 

<리스트 1> MS-SQL 게시판 정의 테이블 스크립트
CREATE TABLE bbs_doc
(
doc_key char(17) COLLATE Korean_Wansung_CS_AS NOT NULL,
/* 문서고유번호 */
bbs_id char(8) NOT NULL, /* 게시판 ID */
doc_subject varchar(255) NOT NULL, /* 문서 제목 */
doc_text text NULL, /* 문서 본문 */
doc_expiration char(14) NOT NULL, /* 문서 폐기일자 */
doc_writer char(8) NOT NULL, /* 문서 작성자 ID */
doc_writer_name varchar(40) NOT NULL, /* 문서 작성자 이름 */
doc_password varchar(20) NULL, /* 문서 비밀번호 */
doc_att_cnt int NULL, /* 첨부 문서 수 */
doc_background char(10) NULL, /* 문서 배경 이미지 */
doc_regdate char(14) NULL, /* 문서 등록 일자 */
doc_ref_cnt int DEFAULT 0, /* 문서 조회 수 */
doc_rec_cnt int DEFAULT 0, /* 문서 추천 수 */
doc_seq int NULL, /* 게시판 문서의 순번 */
doc_subseq int NULL, /* 게시판 문서의 답변 순서 */
doc_indent int NULL, /* 게시판 문서의 답변 레벨 */
doc_child int DEFAULT 0 /* 하위 게시물 수 */
);

ALTER TABLE bbs_doc ADD CONSTRAINT pk_bbs_doc PRIMARY KEY (doc_key);
ALTER TABLE bbs_doc ADD CONSTRAINT fk_bbs_doc FOREIGN KEY (bbs_id) REFERENCES bbs_catalog (bbs_id);
CREATE INDEX idx_bbs_doc ON bbs_doc (bbs_id asc, doc_seq desc, doc_subseq asc);
CREATE INDEX idx_bbs_doc2 ON bbs_doc (resv_date desc, doc_subseq asc);

 

인덱스와 뷰 정의

 

DBMS의 처리 효율을 높이려 할 때에 인덱스는 매우 중요한 역할을 담당한다. 인덱스를 정의하지 않으며 데이터베이스에 저장된 데이터를 처음부터 끝까지 순서대로 검색하기 때문에 테이터 건수가 몇 백만 건으로 늘어갈수록 응답속도가 급격히 떨어지게 된다. 반면 쓸데없이 인덱스를 정의하면 데이터 갱신 시에 테이블에 정의된 수많은 인덱스를 갱신해야 하기 때문에 부하가 발생하고 시스템이 느려지게 된다. 또한 여러 유저가 동시에 접속할 시에 락킹(locking) 때문에 대기 시간이 길어지게 된다. 이러한 상반된 점을 고려하여 설계할 필요가 있다. 빈번하게 액세스 되는 패턴이나 복수의 테이블을 조인하여 액세스하는 경우에는 뷰를 작성해 두기도 한다.

 

<리스트 2> Oracle 게시판 정의 테이블 스크립트
CREATE TABLE bbs_doc
(
doc_key char(17) NOT NULL, /* 문서고유번호 */
bbs_id char(8) NOT NULL, /* 게시판 ID */
doc_subject varchar2(255) NOT NULL, /* 문서 제목 */
doc_text long NULL, /* 문서 본문 */
doc_expiration char(14) NOT NULL, /* 문서 폐기일자 */
doc_writer char(8) NOT NULL, /* 문서 작성자 ID */
doc_writer_name varchar2(40) NOT NULL, /* 문서 작성자 이름 */
doc_password varchar2(20) NULL, /* 문서 비밀번호 */
doc_att_cnt number NULL, /* 첨부 문서 수 */
doc_background char(10) NULL, /* 문서 배경 이미지 */
doc_regdate char(14) NULL, /* 문서 등록 일자 */
doc_ref_cnt number DEFAULT 0, /* 문서 조회 수 */
doc_rec_cnt number DEFAULT 0, /* 문서 추천 수 */
doc_seq number NULL, /* 게시판 문서의 순번 */
doc_subseq number NULL, /* 게시판 문서의 답변 순서 */
doc_indent number NULL, /* 게시판 문서의 답변 레벨 */
doc_child number DEFAULT 0 /* 하위 게시물 수 */
)
PCTFREE 10 PCTUSED 70 TABLESPACE ${db-tblspc-name} STORAGE ( PCTINCREASE 0 );

ALTER TABLE bbs_doc ADD CONSTRAINT pk_bbs_doc PRIMARY KEY (doc_key) USING INDEX TABLESPACE IDX_TBL_SPACE;
ALTER TABLE bbs_doc ADD CONSTRAINT fk_bbs_doc FOREIGN KEY (bbs_id) REFERENCES bbs_catalog (bbs_id);
CREATE INDEX idx_bbs_doc ON bbs_doc (bbs_id asc, doc_seq desc, doc_subseq asc) TABLESPACE IDX_TBL_SPACE;
CREATE INDEX idx_bbs_doc2 ON bbs_doc (resv_date desc, doc_subseq asc) TABLESPACE IDX_TBL_SPACE;

 

물리적 DB 스크립트 작성

 

관계형 데이터베이스 제품들의 DML(Data Manipulation Lanuage) 쿼리는 거의 호환되지만, DDL(Data Difinition Language) 명령 규칙은 각기 다르다. 따라서, 각기 다른 데이터베이스의 테이블, 제약 선언 문법을 이해해야 한다. 다음의 예는 두 가지 데이터베이스에 맞추어 설계한 게시판 정의 테이블 스크립트의 결과다. 이상으로 소규모 데이터베이스를 설계하는 과정에서 필히 알아두어야 할 여러 개념, 용어 그리고 기법들을 설명하였다. 그동안 설명한 과정들은 데이터베이스 모델링을 수행하는 과정에서 꼭 필요한 작업들을 언급한 것이다. 가급적 실무에서 잠깐씩이라도 고민해야봐야 하는 단계들이다. 보다 상세한 기법과 설명을 예시하지 못한 점이 아쉽기는 하지만 우선 제시된 용어와 절차를 이용해 큰 흐름을 이해하기를 바란다. 더불어 보다 대규모 데이터베이스를 다루어야 한다거나 성능에 관심을 가지고 있다면, 인덱스 설계, 트리거 및 저장 프로시저 작성 기법을 중점적으로 학습하는 것이 좋다. 무엇보다 데이터베이스를 제대로 다루기 위해서는 각 제품의 특성을 상세히 파악해야 한다. 이론적인 바탕은 동일하지만 세부 기법은 큰 차이를 가지고 있기 때문이다.

 

이달의 디스켓 : db.zip

참고 자료

1. 실천적 데이터베이스 모델링 입문 |Mano Tadachi | 영진닷컴
2. 대용량 데이터베이스 솔루션 | 이화식 | 엔코아컨설팅
3. 운명적 존재를 위한 데이터베이스 설계(2판) Michael J. Hernandez | 사이텍미디어

 

 

제공 : DB포탈사이트 DBguide.net

출처 : 마이크로 소프트웨어 -[2006년 7월호]