Chapter 0 데이터베이스설계의기본원리 이강좌에서는데이터베이스프로젝트의분석과설계에대해서기본적인지식을살펴보고자한다. 또한, 프로젝트전반적인관점보다는데이터베이스분석과설계에그리고이를델파이프로젝트에적용하는초기단계에대한설명을간략하게살펴보고자한다. 프로젝트전반적인프로세스에대한자세한설명은 /540를참고하기바란다.
데이터베이스관점에서의분석 / 설계프로세스 기능분석시스템을분석할때필자는제일우선적으로기능분석을실시해야한다고생각한다. 그이유는기능분석을통해서우리는구축해야할전체시스템의규모와기능을일목요연하게정리할수있기때문이다. 또한, 이를통해서시스템을구축하기위해필요한최소단위의프로세스를판별해낼수가있다. 기능분석은우선개발해야할시스템이가져야할기능을 Tree 구조로도식화하여정리한다. 이것은 Top-Down 방식을통해서개발해야할시스템의기능구성을보다쉽게정리할수있기때문이다. 이때기능의각항목은짧은제목으로표시한다. 또한기능설계를통해서작성된 Tree는개발일정산출의근거가되기도한다. (/142 참고 ) 쇼핑몰관리툴 회원관리 가입관리... 탈퇴관리... 주문관리...... [ 그림 1] PBS (Process Breakdown Sheet) PBS의 Tree 구조에서가장낮은단계의노드 ( 네모박스 ) 가바로시스템을구축하기위해필요한최소단위의프로세스들이다. 이제부터이것들을 프로세스 또는 단위프로세스 라고부르겠다.
엔티티도출우선단위프로세스를분석하게되면엔티티도출은상당히쉬워진다. 엔티티란정보를저장하는최소단위객체를뜻한다. 여기서는단순히데이터베이스의테이블이라명칭하겠다. 기본적으로프로세스또는프로그램이라고하면크게세가지구조로나뉘게된다. 그것은 입력 처리 출력 으로표현된다. 즉, 모든프로그램은입력된데이터를처리하고그가공한결과데이터를출력하게된다. 따라서우리는도출된각각의단위프로세스가필요한입력데이터의종류를찾아내고처리후결과를저장해야할종류를분석해나가면, 이시스템전체가필요한엔티티의종류를알아낼수가있는것이다. 엔티티는이밖에도장표분석, 업무분석, 동적분석등모든수단을통하여세밀하게검토되고도출되어야한다. 동적분석 데이터베이스시스템에서동적분석의가장대표적인예는 DFD 이다. [ 그림 2] DFD
DFD를통해서우리는프로세스와데이터흐름의관계를분석하고검증할수가있다. 필자는아래와같은형식으로 DFD를대체해서사용한다. External Entity External Entity External Entity Process Process Event Condition No Process Table Yes Process Table [ 그림 3] Job Flow Job Flow를통해서우리는프로세스의흐름과데이터베이스의흐름을분석하고검증할수있으며, 이를통해서고객이설명하고자하는요구사항을도면을통해서확인하고시스템에적용할수가있다. (/543 등참고 ) 관계정의 엔티티간의관계를정의하는단계이다. 회원 1 회원 2 부서 사원 도서 저자 [ 그림 4] ERD [ 그림 4] 에서보듯이엔티티간에는주로 1:1, 1:N, N:M의관계로분류된다. 여기서 N값이또는 M값이 0 이포함될수있느냐없느냐를다시구분하게된다. 관계가명확하게정의되어있지않을경우에는데이터의무결성을보장받을수없다. 또
한 ERD 를통해서분석 / 설계된시스템의효율성도검토하게된다. 식별자정의 주식별자정의는전체데이터모델의복잡성을결정하는중요한요소이다. 해당업무에서자주이용되는속성을주식별자로지정한다. 속성값의길이가가변적인속성은주식별자로서적당하지않다. 부서이름 보다는 부서코드 를주식별자로지정하라 속성값이자주변하는속성은주식별자로서적당하지않다. 주식별자를선정하기위한속성 ( 필드 ) 의수를적게한다. 주식별자에는 Null 데이터가들어와서는안된다. 세부사항정의 이제설계된각각의엔티티에입력될세부사항 ( 속성 ) 을정의할단계이다. 아래는속성을정의할때주의해야할원칙이다. 각각의속성은반드시하나의엔티티에속해야한다. 각각의속성은전체데이터모델에서하나의의미만을가지고있어야한다. 정규화정규화란데이터모델을보다효율적으로개선시켜나가는과정을뜻한다. 일반적으로정규화는중복된데이터를삭제하는것이주목적이다. 정규화는 1차에서 5차까지의단계로나누어지며, 5차정규화는실무에서거의사용하지않고있다. 1차정규화하나의주식별자를기준으로여러값을가진속성은존재할수없다. 고객번호 고객명 구매물품 1 류종택 면도기 비누 2 이미정 화장품 [ 그림 5] 1 차정규화대상 고객번호 (PK) 고객명 고객번호 (PK) 일련번호 (PK) 구매물품 1 류종택 1 1 면도기 2 이미정 1 2 비누 2 1 화장품 [ 그림 6] 1 차정규화이후
2차정규화모든속성은주식별자에종속적이어야한다. 상품상품번호상품명가격 주문상품번호주문번호고객번호사업자번호주문수량 주문번호에의해종속적인속성들 [ 그림 7] 2 차정규화대상 상품상품번호상품명가격 제품주문상품번호주문번호주문수량 [ 그림 8] 2 차정규화이후 주문정보주문번호고객번호사업자번호 3차정규화다른속성에종속적인속성은분리되어야한다. 주문정보주문번호고객번호 고객정보고객번호사업자번호 [ 그림 9] 3 차정규화이후 4차정규화 N:M의관계해소 5차정규화설명생략 반정규화반정규화란정규화를통해서제거된중복데이터를고의로입력하는작업을뜻한다. 정규화가잘되어있는모델의경우무결성이보장되는장점이있지만, 정규화가잘되어있을경우성능이오히려떨어질수있다. 이때, 성능자체가큰이슈가되었을때는반정규화를통해서성능을향상시킬수있다. 정규화와반정규화는시스템의무결성과성능이라는두가지이슈사이에서적절한선택을통하여균형을잡았을때빛이나게된다. 정규화는정합성과무결성을보장하는대신성능에저하를가져올수있고, 반정규화는성능과모델의단순화에대한이점이있지만무결성
저하로인하여시스템의안정성을해칠수가있다. 이에대한간단한예는 데이터베이스설계시유의사항 에서다루겠다. 검증이제는지금까지분석과정을통하여설계된데이터베이스구조가의도된것처럼진행이되었는지를파악하여야한다. 어떻게또는무엇을검증해야하는가? 에대하여상당히많은방법들이존재하겠지만여기서는 CRUD 분석을통한검증방법만을소개하도록하겠다. 이어서설명할 데이터베이스설계시유의사항 에대한점검도, 검증의한방법이라고할수있겠다. CRUD 분석은프로세스와엔티티의상관관계를이용하여구축된데이터베이스시스템을검증할수있는방법이다. 아래의토표처럼각프로세스마다사용하는엔티티를표기하고, 각각의프로세스가해당엔티티를생성 (C), 조회 (R), 변경 (U), 삭제 (D) 하는가에대한여부를표기한다. 프로세스 / 엔티티 고객 주문 제품 사용후기 가입신청 C 제품주문 R C RU 제품등록 C 제품정보보기 R R 이후아래와같은항목들을점검하여설계에이상이없는가를확인하게된다. 모든엔티티타입에 CRUD가한번이상표기되었는가? 모든엔티티타입에 C가한번이상존재하는가? 모든엔티티타입에 R이한번이상존재하는가? 모든단위프로세스하나이상의엔티티타입에표기가되었는가? 두개이상의단위프로세스가하나의엔티티타입을생성하는가? ( 이경우반드시잘못되었다기보다로직의검토대상이된다 )
데이터베이스설계시유의사항 Primary Key를가볍게사용자테이블을구축할때주로 Primary Key 로사용하는것은 UserID일것이다. 하지만, 문자열은숫자에비해그데이터사이즈가크기때문에인덱스효율이떨어진다. 일반적인코드테이블에서는그레코드숫자가작기때문에이러한것을무시할수있으나, 사용자테이블의레코드수가많아진다면문제가될수가있다. 이러한경우에는일련번호필드를새로추가해서 Primary Key 로사용하는것이효율적이다. 또한, 필드를조합하여 Primary Key 로사용할경우에도그크기가너무커지지않도록조심해야한다. 주민번호와사원번호등에대한고정관념가끔필드타입을결정하는데있어서, 사원번호를문자열형태로만만드는경향이있다. 이것들을숫자형태로바꾼다면인덱스의효과면에서나테이블이차지하는용량면에서나많은이점을얻을수있다. 주민번호는가운데 '-' 를필드에반드시넣을필요가있을까? 사원번호는 '000001' 처럼 '0' 이라는문자가반드시필드에있어야할까? 사원번호는영문과함께반드시부서이름을설정해줘야할까? 테이블이중복되면무조건역효과를가져올것인가? 통계처리와같은경우를생각해보면해당월이든기본단위외에는데이터가변하지않는다. 이런경우이미고정된범위를미리집계한테이블을생성하고추가될부분만필요시마다생성한다면, 퍼포먼스는매우향상될것이다. 만약빈번한사용이없는통계데이터라면분리할필요는없다. 만약월마다또는기본단위마다서로상관관계가없는데이터가생성된다면기본단위별로테이블을잘라내서변경시걸리는부하를줄일수있다.
테이블의분할과통합 만약빈번하게조인을해야하는테이블이라고한다면통합에대한재검토가필요하다. 분할과조합에영향을받는것은 Record의변경시보다는조회가더심하다. 예를들어 학과테이블 + 출석테이블 과같은경우만약전체학과에학생에대한출석현황을빈번하게필요로한다면분할보다는조합하는것이좋다. 반대로학과별의경우라면분할이유리하다. 분할이필요한경우 - 테이블들을조인해야하는경우가적은경우 ( 따로사용을많이할경우 ) - 테이블마다사용권한등의설정이다른경우 테이블통합의장점 - 조회하는작업이간편해진다. - 엔티티타입간중복성이제거된다. - 동일한규칙을가진업무처리를단일엔티티로표현이가능하다. - 구조가단순해진다. 테이블통합의단점 - 확장성의침해받을수있다. - 업무흐름을이해하는데어려워진다. - 시스템성능이저하될수도있다. - 속성에제약을걸지못하는경우가발생한다. - 검색조건이늘어날가능성이많다. - SQL문이복잡해지거나작성하기힘들어진다. 모든테이블은반드시서버에있어야한다? 만약코드에관련된테이블들이변경횟수가매우적다면구태여서버에두려고할필요는없다. 클라이언트에복사해서사용하는방법을적극적으로검토한다. 특히, 우편번호와같이레코드수가테이블을빈번히사용해야하는경우라면클라이언트에복사본을두고작업하는것이훨씬능률적이다.
필드의추가가고려되는경우 자주계산되는필드계산필드는 View 테이블이나델파이의 Calculated Field 등을이용하는경우가많지만계산에의한부하가많은경우에는아예계산된필드를생성한다. 이는기존에계산된데이터가필요한경우에도도움이된다. Flag를이용하여조인등의시간을절약할수있는경우미수요금이있는지없는지를검사해야할경우. 전체입금과부과금을계산하여마이너스인지를항상점검해야한다면 Flag필드를만들어주는것이좋다. 특히, 이러한조건이 Case문과같이다양한값을가져야할때효과적이다. 최근 1개월이전에물건을산적이있는사용자 에대한작업을한다고가정하면그효과는극적이다. 매레코드마다이것을계산하는것과미리 Flag를사용하여작업하는것의차이는엄청나게크다. 날짜필드의중복 Flag의경우와동일하다고볼수있다. 만약매주시작되는유료강좌가있다고가정하고사용자가이것을등록하기위한프로세스를생각하자. 이때, 각주별로통계를내거나하는프로세스라면날짜를통해서몇번째주인지를항상계산하는것은효율이없다. Index 설계시유의사항 한 Field에입력될내용의종류가적으면그 Field는 Index를만들지않는다. 예를들면성별 Field와같은경우이다. Data의양 (=Record 수 ) 이적으면 Index를만들지않는다. 변경이많은 Field는 Index를만들기를조심한다. 변경이적고, 검색이많은 Field는 Cluster 생성을고려한다. 변경이주로되며 Batch작업이많은 Table의경우는 Index를만들지않는다. Batch 작업전에인덱스를삭제하고종료후에인덱스를생성하는방법도고려할수있다 결합 Index를생성할때는검색이많은 Field를항상먼저쓴다. 결합 Index를생성할때는인덱스효율이좋은필드를먼저쓴다. 내용의종류가많은필드가인덱스효율이좋다. 하나의 Table에 5개이상의 Index를생성해야하는경우라면설계를재검토한다. 빈번하게 Join을할필요가있을때, 해당 Field의 Index를생성한다. Index가가해지는필드는가능한 Null값이없어야한다.
Join 시유의사항 Table Join 시에는 Record의수가적은것부터 Join한다. 다량의 Table과다중 Join이필요할시에는 Cluster를생성한다. 검색시필드는연산하지않는다 인덱스가적용되지못하는경우 Select * from Table1 where SUBSTR(Name, 1, 2) = ' 류 ' Select * from Table1 where Score * 10 >= 90 인덱스가적용되는경우 Select * from Table1 where Name Like ' 류 %' Select * from Table1 where Score >= 90 / 10 계산이필요한숫자형필드는 0을디폴트로지정한다 Null Data로인해 Sum이나 Avg와같은숫자연산이되지않아서생기는논리적이오류를방지할수있다. 쿼리문을통한결과값을확인하는조회의경우에는큰문제가되지않으나, 이것을토대로계산을하는 Stored Procedure 등을작성할경우에는그과정에대한가시성을확보할수가없어, 찾기어려운에러를유발하게된다.