Database design guide ( 테이블, 컬럼정의방법 ) 2004 년 11 월 18 일 Sunny Kwak sunnykwak@hanmail.net sunnykwak.egloos.com
Copyrights 2004 Sunny Kwak. All rights reserved. Other disclaimers The names of actual companies and products mentioned herein may be the trademarks of their respective owners. Version History Version 1.0 (draft) 2004 년 11 월 18 일목요일 First created. 2 / 7
Table of Contents 1. INTRODUCTION...4 1.1 OBJECTIVES... 4 1.2 TARGET AUDIENCE... 4 2. 데이터베이스설계지침...4 2.1 테이블스페이스 (TABLESPACE) 생성규칙... 4 2.2 테이블생성규칙... 4 2.2.1 테이블명칭부여...4 2.2.2 컬럼명칭부여및컬럼설계규칙...4 2.2.3 제약조건설계 (primary key, foregin key, null or not null)...6 3. 오라클데이터베이스설계규칙...6 3.1 테이블설계규칙... 6 3.1.1 인덱스설계규칙...7 3 / 7
1. Introduction 1.1 Objectives 데이터베이스설계시참고해야할정보, 필수적으로알아야할지침등을정리하였다. 1.2 Target Audience UbiFlow 프로젝트매니저, 테크니컬리더 2. 데이터베이스설계지침 2.1 테이블스페이스 (tablespace) 생성규칙 테이블스페이스는데이터용테이블스페이스공간과인덱스용테이블스페이스를별도로생성해야한다. 2.2 테이블생성규칙 2.2.1 테이블명칭부여 데이터베이스제품종류에따라서테이블명칭의대소문자가구분되는것 ( 사이베이스등 ) 과대소문자구분이되지않는제품 ( 오라클등 ) 이있다. 따라서, 데이터베이스제품종류에투명 (transparent) 하며, 일관성있는테이블설계를위해모든테이블명칭은소문자만으로구성한다. 테이블명칭은단위어플리케이션명칭 ( 메일, 결재, 게시 ) 과각테이블의기능혹은역할을표현하는단어 ( 문서, 첨부, 사용자 ) 등둘이상의단어를합성하여명명한다. 테이블명칭내에서각단어간의구분은 '_'(underline) 을사용하도록한다. 테이블명칭의두번째이후의단어은가능한표준영어명사를사용하도록하며, 어절의길이가길어짧게표현할필요가있을경우이외에는가급적약어혹은한글발음표기를사용하지않도록한다. 테이블명칭의길이는가급적최대 20자리를넘지않도록한다 pt_user pt_group pt_sign_doc : 공통시스템의사용자정보 : 공통시스템의부서정보 : 결재시스템의결재문서 2.2.2 컬럼명칭부여및컬럼설계규칙 컬럼명칭은필히소문자만으로구성한다. 컬럼명칭은가능한둘이상의어절을합성하여구성한다. 컬럼명칭의첫번째어절은컬럼의분류혹은테이블명칭의약자를나타내도록한다. doc( 문서 ), mail( 편지 ), att( 첨부 ), code( 코드 ) 4 / 7
컬럼명칭의두번째어절은컬럼에저장되는데이터의용도를나타내도록한다. id, name, subject, filepath 입력문자열의길이가일정한경우가능한 char 타입을사용하며 varchar2 혹은 varchar 타입은가급적사용하지않도록한다. ( 디스크공간의효율적활용및 insert, update 시속도향상과 fragmentation 방지를위해 varchar 보다는 char 타입이효율적이다. char 타입의출력시 right trim 처리가필요할수있으므로주의. 단, update가많이발생하지않는경우에는 varchar를사용해도무방하다고봄.) 날짜혹은시간을표현할경우 date, datetime 타입을사용해서는안되며, char 타입을사용하도록한다. 년월일을저장할경우에는 8자리, 시분초까지저장할경우에는 14자리를사용한다. char(8) : 날짜 (YYYYMMDD), char(14) : 날짜, 시간 (YYYYMMDDHHMI24SS) varchar2 혹은 varchar 타입을사용할경우, 2000 byte 이상의데이터를저장할필요가있을경우, long 혹은 text 타입을사용해야한다. 또한, 가변길이문자열컬럼을정의할경우, 오라클에서는 varchar2 타입을기타데이터베이스에서는 varcahr 타입을사용한다. 입력문자열의길이가짧은경우가급적 long, text 타입을사용하지말아야한다. long 혹은 text 타입은각종함수 (nvl,ltrim), 인덱스 (index), like 검색등을사용할수없다. 또한하나의테이블에둘이상의 long 타입컬럼을만들수없다. 단, text 타입은둘이상의컬럼을허용한다. 컬럼내에데이터가저장될때, ',', ';' 등의구분자 (delimeter) 가포함되는경우는가급적없도록설계시고려한다. 구분자를포함한데이터를저장하고자하는경우에는하위테이블 (child table) 을별도로추가한후구분자를이용해분할데이터를하위테이블에저장한다. 모든 ID 관련컬럼은 8~10 byte, 이름항목은 40 byte, 제목및설명은 255 byte, 문서내용에관한내용은 2G byte의크기를권장한다. 다음테이블에기술하는항목명칭들은관습적으로사용되는컬럼명칭이다. 이외의사례는 UbiFlow naming rule 문서를참조하라. [ Table 1 ] 테이블명칭 한글명칭 컬럼명칭 example 사용자 user user_id, user_name 부서 group group_id, group_name 기관 org org_id, org_tel, org_fax 전화 tel user_tel, group_tel 팩스 fax user_fax 직위 title user_title, user_titlename 직책 todo user_todo, user_todoname 개수 cnt doc_attcnt, doc_refcnt, child_cnt 순번 seqno add_pos_seqno, att_seqno 순서 order display_order 문서 doc doc_yearmon, doc_number, doc_type 수신 recv recv_date 5 / 7
예약 resv resv_date 참조 ref (reference) doc_refcnt 문서함 folder folder_type, folder_id 설명 comment folder_comment, type_comment 제목 subject doc_subject 대장혹은등록 reg (register) reg_date, reg_method 2.2.3 제약조건설계 (primary key, foregin key, null or not null) 기본키 (primary key) 의명칭은테이블명칭의앞에 pk_ 를추가한다. 기본키는가급적인덱스용테이블스페이스에생성토록한다. insert query 시성능향상을도모할경우에는가급적 foreign key를지정하지말아야한다. 설계서상에는논리적으로선언해야하며, 개발완료후운영용데이터베이스에서는삭제해도무방하다. ( 개발및테스트단계에서참조무결성이검증되었다고판단한다.) 프로그램개발완료후각테이블의데이터를추출 (select) 하는모든쿼리를검사한다. 이중에서가장많은빈도로호출되는쿼리를선택한후, 쿼리의응답속도가최상이될때까지인덱스를조정한다. 최빈도의쿼리에대한인덱스형성후, 그보다낮은빈도로접근하는쿼리에대한인덱스를생성하거나, 두쿼리의성능에균형 (balance) 을조절할수있도록첫번째인덱스를조정한다. 앞서하나혹은두인덱스에의해서다른쿼리의성능이떨어지는현상이발생하지않는지확인한다. 가급적 null 값이허용되고, null 데이터를많이포함하는컬럼은인덱스대상에서제외한다. 데이터값이편차가적은컬럼또한인덱스대상에서제외한다. igt_log 의 userlog_type, igt_group 의 org_id 인덱스생성으로인한성능향상을얻을수있는경우는인덱스를포함하는테이블이데이터가많거나, 향후행 (row) 의수가급격히증가할것이예상되는경우이다. 일반적으로 1000 ~ 10000건이하행을지니는테이블에서는인덱스를만들어도성능향상효과가없거나, 최적화기 (optimizer) 에의해서인덱스가무시될수도있다. 인덱스가실제사용되는지여부를알기위해서는오라클의경우, plan table을사용하거나, 싸이베이스혹은 MS-SQL 서버의경우에는 set showplan on 명령을사용하여야한다. 하나의테이블에가급적 3 개이상의인덱스를만들지않아야한다. 인덱스가많을경우, 추가 (insert), 수정 (update) 및삭제 (delete) 시쿼리의반응속도가느려지게되며, 디스크사용량이급증한다. 3. 오라클데이터베이스설계규칙 3.1 테이블설계규칙 모든테이블생성시테이블스페이스이름을명시하며, PCTFREE 10, PCTUSED 80, 6 / 7
PCTINCREASE 0 등세가지설정을추가한다. /* 환경설정정보 */ CREATE TABLE pt_ini ( ini_name varchar2(40) NOT NULL, /* 환경설정항목명칭 */ ini_value varchar2(255) NOT NULL, /* 환경설정값 */ ini_explain varchar2(255) NULL /* 환경설정항목설명 */ ) PCTFREE 10 PCTUSED 80 TABLESPACE gw_data STORAGE ( PCTINCREASE 0 ) / 3.1.1 인덱스설계규칙 인덱스생성시테이블스페이스이름을명시하며, STORAGE 옵션을다음과같이설정한다. ALTER TABLE PT_ini ADD CONSTRAINT pk_ini PRIMARY KEY ( ini_name ) USING INDEX TABLESPACE gw_index STORAGE(INITIAL 32K NEXT 32K MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 0) / 인덱스생성을위해 storage 크기는 8K * 4 배수로결정하며, 다음의표를참조하여생성한다. [ Table 2 ] 인덱스저장용량산정 페이지크기 테이블속성 적용테이블사례 32K insert rarely, delete rarely 문서번호관리 (doc_number), 환경설정 (ini), 서버설정 (server), 볼륨설정 (volume), 부서및사용자 (group, user) 등 128K 512K 2M insert frequently, delete frequently or insert rarely, but index record size is long insert frequently, delete rarely insert very frequently, delete rarely 메모 (memo), 메일문서함 (mail_doc_folder) 및첨부 (mail_attach), 게시판문서관련테이블 (bbs_doc) 등 주소록관리 (addrbook), 결재경로관리, 공람내역등 결재대장, 결재문서수발신기록, 결재날인기록등 7 / 7