[SQL고수되기] 세번째 이야기

출처 : 네이버지식인 노하우
http://kin.naver.com/knowhow/detail.nhn?d1id=8&dirId=8&docId=552842&qb=U1FM6rOg7IiY65CY6riw&enc=utf8&section=kin&rank=7&search_sort=0&spq=0

테이블 설계에 대해서 이야기해볼까합니다.
앞으로 이야기할 내용은 쿼리가 잘 수행되기 위해 고려해야 할 내용이라는 점을 다시 한 번 말씀드립니다.

몇달전에 제가 현재 소속되어 있는 부서로 한 통의 메일이 전달되었습니다. 현업(설비기술팀)에서 온 짜증이 섞인 내용이었습니다. 이과장님 저희 시스템으로 조회하면 RawData가 잘 나오는데 A시스템을 -20시간 검색하면 조회가 잘되지 않습니다. 어쩌구 저쩌구.. 빠른 조치 부탁드립니다. 라는 한 통의 비아냥 섞은 내용이었습니다.

그래서 분석에 들어갔습니다. 해당 내용을 두 시스템의 해당 테이블들이 생성되는 시점부터 관리되는 방법 및 테이블 구조까지 확인해 보았습니다. 억~~헉~~ 문제가 심각한 부분이 발견되었습니다. 조회가 잘 된다는 시스템은 단순하게 정리된 정보를 매일 새벽에 전달받아 쌓아놓는 시스템이고조회가 잘 안된다는 시스템은 1시간에 두번 file 정보를 interface 받아 원시테이블을 만들어내는 작업(Read, Write, ReWrite)을 반복적으로 수행하여 결과(Row)를 만들어 내는 시스템이였습니다.
더 안타까욵 점은 이렇게 생성된 정보를 앞에 조회 잘되는 시스템에 전달해 준다는 점이었습니다. 조금 애석하고 답답한 생각이 들었습니다. 단순하게 조회만 주로 하시는 사용자 분들은 똑같은 정보 아니냐? 시스템 관리에 문제 있는거 아니냐? 시스템 개발 및 관리하는 사람들 실력이 없는거 아니냐? 정말 당황스러웠습니다. A 시스템 담당자들은 당하는 느낌이 들었습니다. 그렇게 따지면 A시스템 담당자 입장에서도 파일 줄 때부터 아주~~ 기계적인 정보를 주지 말고 그쪽에서 어느정도 정제된 정보를 주어야 하는거 아니냐!라고 반문하고 싶을지도 모릅니다. 쩝 저도 시스템 관리자로써 조금은 슬픈 시스템 담당자들의 고통입니다.

그럼 무엇이 문제였을까요? 일단 A시스템에 90%이상의 문제가 있다라고 말하고 싶습니다. 테이블 설계시에 RDBM니 분석설계 방법론 등등 아주 중요합니다. 하지만 관리는 Oracle이라는 DB엔진이 하는 것이고 그것을 담당자들은 활용만 할뿐입니다. 이말은 Oracle자체의 Spec을 바꿀수는 없다는 것이지요. 1차,2차,3차 정규화 거치고 BCNF까지 거쳤다면 다 끝난 것일까요? 하핫 아닙니다. 이건 업무적인 관점에서 형상화시킨 개념 모델링에 불과합니다. 아주 중요한건 그럼 Oracle에 어떻게 탑제시키고 유지시키는가 하는 고민을 애햐 한다는 점입니다.

테이블 Create시에 굉장히 많은 상황을 고려하고 결정해야하지만 대부분의 개발자 분들은 전혀 고려하지 않는다는게 문제입니다. DBA는 통보만 받고 마는 사실 또한 애석할 따름입니다. 음..이 테이블은 마스터 형식의 테이블이니 한번 업무상 한번 생성하면 삭제도 없고 가끔 수정이 발생하겠군.. 어! 이 테이블은 행위정보로 매일매일 많은 량이 생성되겠군.. 헐! 수정은 일일기준인데 어떻게 수정하지? 음... 전제 지우고 재생성해야겠군... 등등 내가 생성하려는 정보가 어떤 어떻게 생성될 것이며 어떤 방식으로 유지될 것인지 여러분들이 메모하신 내용과 실제 사용자들 관점에서 한번은 생각해보고 판단하는 게 매우 중요한 것입니다.

이제 테이블을 만들어 볼까합니다. 업무별,유형별로 수많은 정보들이 만들어 지겠지만 딱 두가지 정도 경우만 예를 들어서 만들어 볼까합니다. 또 객체나 아주 큰 사이지의 정보는 제외토록 하겠습니다. 하나는 한번 생성되면 삭제는 없고 수정이 종종 발생하는 테이블이고 다른 하나는 앞에 생성된 테이블 기준으로 매일 정보가 수시로 발생되고 수정되고 삭제되고 경우입니다.

● 아르바이트생 정보

　　- 테이블명 : EMP_INF
　　- 내용 : 아르바이트생의 인적정보(이름,나이,주소,전화 등)를 관리한다.
   　- 특징 : Row기준으로 Unique한 정보들이고 한번 추가되면 삭제되지 않고 수정만 발생함

● 일별 출퇴근 정보

　　- 테이블명 : WORK_INF
　　- 내용 : 아르바이트생 별로 일일 CHECK IN/OUT 정보를 관리한다.
　　- 특징 : 행위 Entity로서 일일 n번 발생할 수 있고 기간에 종속되지 않고 Row개수는 증가될 수 있음

● 월별 시급 정보

　　- 테이블명 : PAY_INF
　　- 내용 : 아르바이트생 별로 월별 시급(월급) 정보를 관리한다.
      - 특징 : 월별로 생성되며 수정은 존재하지 않고 월별 재생성 될수 있다.

※ 참고) 서실 테이블 설계시는 더 꼼꼼해야 하겠지만 오늘 제가 이야기 할 주제와는  관련이 없어 생략하였습니다.

공부를 하시다 보면 PK, FK 이런 말들이 나옵니다. 일단 FK는 개념적인 부분이라 만드는게 아니다라는 말을 먼저 염두해두겠습니다. 자세한 내용은 앞으로 차차 설명드리겠습니다.

첫번째 아르바이트생 정보에서 PK는 무엇일까요? 지금까지 개발자들은 단순하게 Unique하다가 PK이다 하고 생각 하실겁니다. PK는 Oracle이 Row를 Database관점에서 관리하기 위해 세우는 하나의 규칙에 불과합니다. 쉽게 말씀드려서 물리적인 Entity를 관리하기 위한 수단인 것입니다.논리적인 Entity(앞으로는 그냥 쿼리라고 하겠습니다.) 를 만들고자 할 때는 의미없는 내용일 수 있다는 이야기입니다. 단 검색시엔 PK라는 인덱스적인 특징때문에 활용될 수도 있겠지만 그게 큰 의미는 없습니다. 오히려 대용량데이터 처리시에는 발목이 될 수도 있습니다. 검색은 나중에 다른 이야기에서 주제로 설명할 것이니 지금은 그냥 넘어가겠습니다. 지금까지 제가 개발자적인 입장에서 PK를 무시했습니다. 그러나 Oracle에 관점에선 PK가 없다면 그건 정확한 정보 관리에 어려움이 생길 수 있습니다.

간혹 다른분들이 하신 이야기들 중에 굳이 PK는 안잡으셔도 무방하다라는 말을 종종 보곤 합니다. 그건 Database를 고려하지 않은 개발자 관점에서의 이야기라고 생각합니다. 무결성이 없는 데이터들은 쿼리를 설계시에도 너무 큰 리스크로 오기때문에 정확한 PK설정은 반드시 선행되어야 합니다. 제가 앞에 이야기에서 내가 잘 만든 테이블 열 쿼리 안부럽다는 말씀을 드린 이유 중에 한가지에 PK설정도 포함이 됩니다. 테이블 설계는 Database 관점이 99% 반영되어야하고 그것을 바탕으로 99%개발자 생각이 반영된 쿼리가 완성되는 것입니다.

테이블 설계시 반드시 PK 설정이 중요하기에 너무 긴 내용으로 설명드렸는데 조금 따분하셨을지도 모르겠습니다. 그럼 다시 원점으로 돌아가서 아르바이트생 정보의 PK는 무엇일까요?

주민번호? 생일? 이름? 아니면 ... 일련번호? 잘하시는 분들은 바로 나오시겠지만 PK설정으로 대상 Tablespace가 결정되고 경우에 따라서는 Partition설정까지 될 수도 있습니다. 제 의견을 말씀드리면 이렇게 하는게 어떨까요...년도(2)+월(2)+일련번호(4) 이렇게 설계를 하면 나중에 PK만 가지고 년도별 아르바이트생 추이도 볼 수 있고 나아가서 각 유형별 통계시에도 기준값으로 활용될 수 있어서 괜찮을 듯 합니다. 그럼 나머지 테이블 WORK_INF나 PAY_INF는 PK를 어떻게 설정하면 좋을까요? 이건 여러분 생각에 맞기겠습니다.

EMP_INF 테이블의 PK가 결정되었습니다. 단, 기타 컬럼들은 별도로 나열하지 않겠습니다.

그럼 타입은 어떻게 해야할까요? CHAR? VARCHAR2? NUMBER? CHAR가 맞습니다. VARCHAR2되지 않나요? 라고 하시는 분들도 계실겁니다. 네 틀리지 않습니다. 그렇지만 맞지는 않습니다. PK는 규칙입니다. 컬럼이 하나가 되었든 결합해서 두개가 되었든 어떤 규칙에 의해 정해지기에 반드시라는 말을 붙일 수 있습니다. CHAR의 특징이 어떤가요? 반드시 자리수가 정해집니다. 제가 정한 PK는 8자리네요. 자리가 8자리인데 제가 모드로 2001050001 값을 넣으면 어떻게 될까요? 에러나겠지요? over flow~~ 또 20100901 이값은 어떨까요? 또 하나 더 10061 이건 어떨 까요? 에러들은 없겠지만 검색이 안되겠지요? 자리 수가 적은 경우엔 뒤에 공백이 추가되여 Equal Join의 부정합에 의한 오류가 되고 자리수는 지켰지만 값의 부정합에 의한 오류가 생깁니다. 그렇다고 시스템 오류가 나진 않습니다. 단지 쿼리 구현시 짜증만 날 뿐입니다. 그래서 테이블정의서든 스크립트든 타입과 자리를 기재하고 설명(년도(2)+월(2)+일련번호(4))을 반드시 기재하여야 합니다.

사실 중요한 건 정의된 PK를 기준으로 Table 생성을 해야 하는데 이때가 가장 중요한 부분이라 이야기가 길어질듯 하여 다음 이야기로 다루겠습니다.

이 이야기를 보시는 분들은 앞에 여쭤본 WORK_INF나 PAY_INFO의 PK에 대해서 생각해보세요

반드시 생성하고자 하는 테이블의 특징을 염두해 보시는게 좋습니다.