[데이터베이스를 지탱하는 기술] - 테이블 설계와 릴레이션

테이블 설계와 릴레이션

테이블 설계의 핵심에 대해 알아보자. 데이터 모델링이라고도 불리는 영역이다. -> 재대로 되어 있지 않으면 애플리케이션 수정 및 보수시에 매우 고생한다. 이것의 중요성은 향후에도 변하지 않을 것이다. 기초를 제대로 파악해 두어야 하는 부분이기도 하다. 

# 데이터 모델링 기술의 중요성

애플리케이션을 만들때 중요한 것은 바로 어떤 데이터 항목이 필요한가를 재대로 파악하는 것이다. 항목이 부족하면 나중에 추가할 필요도 있겠지만, 이는 대부분의 경우에 애플리케이션의 개선이 필요하고 가독 후에 실시하는 것이 쉽지 않기 때문이다. 어떤 항목이 필요한지를 적절히 결정하는 데에는 "데이터를 어떻게 사용하느냐"라는 "업무 요구사항"이 명확하게 되어 있어야 한다. 기능 추가가 자주 발생하는 애플리케이션에서 데이터 항목을 완벽히 미리 밝혀내는 것은 불가능하기 때문에 나중에 손쉽게 추가할 수 있는 디자인도 중요하다. 

=> 결론

필요한 데이터 항목들을 치밀하게 분석하고 따져봐야 후에 고생하지 않는다. 하지만 데이터 항목을 완벽하게 미리 밝혀내는 것은 불가능 하기 때문에 나중에 손쉽게 추가할 수 있도록 하는 디자인도 중요하다.

# 예제를 사용하여 생각해 보자

직원과 소속조식/부서를 취급하는 어플리케이션에서 필요한 데이터 항목을 파악하여 적절하게 테이블 설계에 도입해 나가는 접근 방식을 알아보자. 

  테이블 설계시 직원과 조직을 다루는 테이블을 반드시 정해진 포맷이 있는 것은 아니다. 앞서 언급한 것처럼 애플리케이션은 사용자를 위해서 있는 것이고 그 사용자의 요구에 따라 어떻게든 달라진다. `사용자로부터의 의견 청취`를 통해 모아지는 것이 일반적이다. 이번에는 의견 청취 결과로 다음과 같은 조건이 있었다고 가정하자.

• 직원은 고유의 사원 번호를 가진다.
• 같은 이름의 사원이 있는 경우가 있다.
• 이름의 한자가 다르다 해도 발음이 같은 지원이 있는 경우가 있다.
• 직원은 메일 주소가 부여된다. 메일 주소는 중복되지 않는다.
• 경력 사원 등 수속 절차에 따라 메일 주소의 부여가 지연될 수 있으며, 이 경우는 당분간 메일 주소가 없는 상태가 된다.
• 직원은 어느 부서인가에 반드시 소속된다.
• 부서 전화번호가 가지각색이므로 이것도 관리하고 싶다.

이것들을 베이터베이스에서 관리할 경우에 어떤 구조가 되는지 살펴보자. 필요한 데이터 항목을 나열하고 테이블 설계에 도입해 나가는 작업을 `데이터 모델링`이라고 한다.

데이터 항목과 관계성에 대한 의식

직원수가 적은 회사라면 Excel에서 관리하면 될까? -> 데이터를 보유하는 방법에 따라 일관성이 무너질 가능성이 있기 때문에 그렇지 않다. 무너진 일관성은 육안 등 수동으로 여러 테이블 정의를 보겠지만, `어떠한 시나리오에서 일관성이 무너질 위험성이 있는지`라는 관점에서라도 반드시 집중해서 읽어보길 바란다.

전통적인 방법으로 테이블을 만들어 보자

테이블 설계에서는 행을 고유하게 식별하기 위한 식별자를 도입하는 것이 일반적이다. 이것은 기본 키 라고 한다. 기본 치가 없으면, 예를 들어 어떤 레코드에 문제가 있는지를 체크할 경우에 해당 레코드가 단지 하나뿐이라고 보증할 수 없다. 

  중복이 수십 개에 당하고 앞으로도 어떻게 될지가 잘 보이지 않으면 일일이 동일한 문자열을 다시 입력하는 것은 귀찮은 일이 되어 버린다. 귀찮을 뿐만 아니라 자칫 입력 실수를 할 수 도 있을 것이다. 입력 실수가 발생하더라도 일일이 눈으로 모든 내용을 보고 확일할 수 밖에 없기 때문에 그냥 지나치거나 빠뜨릴 위험이 높아진다.

포인트 1: 테이블 관계 를 도입

일반적으로 회사의 부서에는 여러 사원이 근무하고 있기 때문에 사원 번호를 키로 하여 부서의 정보를 갖게하는 경우, 곳곳의 레코드가 중복된 정보를 갖게 되는 것은 분명하다. 중복 정보를 포함하는 것은 카피 앤 패이스트를 많이 사용한 프로그램처럼 모든 부분을 바꾸었다고 생각해도 미처 바꾸지 못한 부분이 남아 있게 되는 경우가 많다. 즉, 그림 3-1 및 그림 3-2와 같이 한 개의 테이블에 전부를 갖는다는 방법 자체가 잘못인 것이다. -> 직원은 직원, 부서는 부서로 별도의 테이블에서 관리해 보면 어떨까? 라는 발생이 생겨나게 되었다.

  일반적으로 기본 키의 값은 한번 정해지면 쉽게 변경되지 않아야 한다. 테이블을 나누어 관리하면 입력 실수 같이 바보 같은 실수가 일어날 가능성이 크게 줄어든다.

참조 무결성 제약

그러면 테이블을 나누어 조인해서 관리한다면 참조하는 테이블의 기본 키를 100을 입력해야하는데 1000으로 잘못 입력할 가능성은 충분히 있을 수 있다. -> 어느 정도의 실수라면 데이터베이스의 표준 기능인 `참조 무결정 제약`에 의해 감지할 수 있다. emp테이블과 dept 테이블에서 참조 무결정 제약을 사용하면 emp테이블에서 부서 번호를 입력할 때 그 값이 dept 테이블에 있는지에 대한 여부를 자동으로 체크해준다. 만약 부서가 없는 코드를 입력하려고 했다는 오류가 반환된다. 이러한 검출을 자동으로 시행해 주는 것이 참조 무결성 제약의 매력이다. 이외에도 dept 테이블의 레코드를 지우려 하면 해당 부서 번호가 emp테이블에 존재하는지 확인할 수 있다. 존재하는 경우 오류를 반환하거나, 값을 비활성화(NULL로)하거나, 아니면 각 레코드를 지우거나 하는 동작을 시킬 수 있다. dept 테이블에 존재하지 않은 부서번호가 emp 테이블에 남아있다는 것은 일어나서는 안 되는 현상인데, 그러한 데이터의 무결성을 자동으로관리해 준다는 면에서 우수하다.

  하지만 단지 참조 무결성 제약으로 모든 것을 해결할 수있는 것은 아니다. 예를 들어 입력 실수로 `인사부(100)` 대신 `회계(101)`로 부서의 정보를 입력하면 그대로 저장되어 버린다. 이러한 입력 실수를 방지하려면 애플리케이션 쪽에서 대처가 필요하다. 

  다른 제한사항으로는 규모가 큰 웹사이트라면 emp 테이블을 전용 서버에서 관리하고, dept 테이블을 다른 서버에서 관리하는 등의 경우가 생길지도 모른다. 수가 한정된 부서 및 직원이라면 현실성이 있는 이야기는 아니지만, 부서를 도시로, 직원을 국민으로 치환해서 생각해 보면 가능성이 있는 일이라느 것을 알 수 있다. 이러한 경우에는 참조 무결성 제약을 사용하지 않고 동일한 기능을 애플리케이션에서 구현하여 확인한다.

=> 결론

데이터 항목이 많아지면 중복으로 작성되는 정보가 많아진다. 이는 잘못 입력과 같은 실수를 통한 문제점이 될 수 있고 이를 해결하기 위해서는 수동으로 해결해야 했다. -> 이러한 문제점을 테이블을 나누고 기본키를 설정하여 조인함으로써 해결되었다. 하지만 이도 몇가지 문제가 있었고 데이터베이스에서는 이것을 해결하기 위한 참조 무결성 제약이 있어서 알아서 처리된다. 

참조 무결성 제약도 완벽히 문제를 해결해주는 것은 아니다. 아래에 경우다.

1. 테이블에 있는 정보를 실수로 입력했을 때
2. 테이블이 각각 다른 서버에 저장되어있어 참조 무결성 제약을 이용하지 못할 때

다음과 같은 경우에는 직접 애플리케이션 레벨에서 처리하는 방식으로 해결할 수 있다.

포인트 2 테이블 설계의 타당성 검증하기

emp_id	emp_name	emp_roman	emp_email	dept_id
1	기리조 미쯔로	Misuru Kirijo	Miscuru.Kirijo@exmaple.com	100

dept_id	dept_name	dept_tel
100	인사부	03-1234-5678

위의 emp테이블과 dept테이블이 완성되었다. 그런데 업무를 수행함에 있어서 조금 곤란한 일이 발생할 수도 있는 것을 눈치챘는가? 대표적인 예로 `사원은 겸무를 할 수 없다.`라는 점이다. 즉 한 직원당 등록할 수 있는 부서 ID가 하나밖에 없어 두 개 이상 등록하고 싶어도 그것을 할 수 없다는 사실이다. 이를 해결하는 방법이 있는가?

  방법의 하나로, 새로 겸임이 필요하게 되었을 경우에 새로운 직원 ID를 할당하여 새로운부서에 배속시키는 방법이 있을 수 있다. 개발 부서에 있을 때는 직원 ID =123, 지원 부서에 있을 때는 직원 ID =145와 같은 방식이다. 이 경우 현재 테이블 정의 그대로 겸무 정보를 등록할 수 있다. 하지만 이방법은 단점이 있다. 먼저 emp 테이블에서 동일 인물로 기본 키가 서로 다른 복수의 레코드가 존재하게 된다. 따라서 같은 이름과 이메일 주소의 레코드가 복수로 나오게 된다. 이 중복 검사 등은 복잡하고 데이터베이스 구조적으로도 좋다고 말할 수 없다. 두번 째는 더욱 심각하다고 말할 수 있는 것은 업무에 미치는 영향이다. 사내 포털 웹사이트에 로그인할 때에는 두 개 이상의 계정을 구분해야 하며, 사원증도 두 개 이상 필요할것이다. -> 이것은 업무 생성성을 높이기 위한 어플리케이션임에도 불구하고 어플리케이션때문에 업무에 악영향을 미치게 된다. 대부분은 사용자가 불만을 확실히 밝히고 나서야 추가비용으로 테이블 설계를 바꾸거나, 경우에 따라서는 개발한 곳에서의 지출로 대응한다. 대응이 매우 어려운 경우에는 `애플리케이션 제약 조건에 따라 업무를 바꾸는`일도있다. ERP 패키지 등 매우 복잡한 소프트웨어에서는 변경 자체가 쉽지 않기 때문에 이러한 대응을 취하는 경우가 있다. 좀 더 나은 방식으로 무엇을 생각할 수 있을까? 몇 가지 살펴보도록 하자.

연속적인 번호의 열 도입하기

emp 테이블에서 부서를 관리하는 열이 한 개 밖에없다. 이에 대해 `메인 소속부터, 서브 소속부서, 서브 소속부서2`와 같이 부서에 대한 열을 여러 개 준비하는 접근 방식이 있을 수 있다. 아마도 이 방법은 많은 사람들이 생각해 내는 가장 손쉬운 방법이라 할 수 있다. 

  이러한 테이블 설계 또한 자주 눈에 띈다. 세 개의 열을 준비해 두면 최대 세 개 부서까지 겸무 정볼르 등록할 수 있다. 이 설계에서는 전체의 부서가 세 개의 포함되는 한 제대로 작동한다. 따라서 그러나 문제는 겸인 한도가 최대 세 개까지 겸무 정보를 등록할 수 있다. 그러나 문제는 네 개의 부문을 겸임하는 직원이 나타난다면 엉망이 된고 만다는 것이다. 따라서 겸임 한도가 최대 세개 부문에서 정말로 업무가 장 돌아가는지를 확인할 필요가 없다. 정말 충분한지는 업무에 깊이 관여된 사람밖에 모르기 때문에 개발자가 독단적으로 결정하는 것은 금물이다. 10개 부문을 겸임하고 있는 사람도 있을지 모른다. -> 이러게 되면 이번에는 물리적인 문제가 부상한다. 즉 낭비가 너무 많다. 세 개 부문의 열을 마려한 것조차도 대부분의 열에 아무것도 들어 있지 않은데, 10개 부문이 되면 낭비가 90% 가까이나 된다. 아무것도 열 값이 들어가지 않아도 실제로는 NULL 이라는 값을 가지고 있어, 제대로 물리적인 공간을 소비하고 있기 때문에 문제가 있다.

  연속적인 번호의 열을 제공하는 방법은 열 수가 끊임없이 증가해 나갈 가능성이 있는 경우에는 권장되지 않다. 또한 데이터베이스 구현의 관점에서 열의 추가는 온라인에서는 할 수 없는 것도 많다. 따라서 나중에 열을 추가하는 것은 쉽지 않다. 

1:N 관계를 두 개 도입하기

데이터베이스 설계상 보다 아름다운 모델이 되게 하려면 emp와 dept 같은 1:N의 관계를 하나 더 도입하면 좋을 것이다. 겸임 정보가 무엇에 의해 결정하는지를 생각해 보니 직원 ID만으로는 정해지지 않고 부서 ID만으로도 결정되지 않지만 (사원 ID, 부서 ID)가 정해지면 고유의 의미를 갖게 된다고 생각한다. 따라서 이 두 열을 기본 키로 가지는 테이블을 새로 도입하면 좋지 않을까? 라는 발상으로 이 설계에 도달할 수 있을 것이다. 

emp_id	dept_id
1	100

사원 번호와 부서 번호라는 ID밖에 테이블을 보면 그야말로 필요가 없는 것처럼 보일지도 모른다. 그러나 이 테이블이 없으면 직원이 어느 부서에 소속되어 있는지를 바로 알 수 없고, 부서 안에 어떤 직원이 있는지도 바로 알 수없다. -> 이러한 테이블을 일반적으로 `매핑 테이블`이라고 한다. 매핑 테이블은 데이터 모델링 과정에서 자주 나온다. 이번 예제에서는 emp테이블과 dept 테이블 두 개를 매핑하고 있지만, 수십 개에 걸친 테이블을 매핑하는 경우도 있다. 매핑 테이블의 특징적인 사항으로는 다음과 같은 것을 들 수 있다. 

• 기본 키값이 자주 바뀐다.
  `기본 키 값은 변경되어서는 안 된다`라는 말을 했지만, 이 매핑 테이블은 전근 등이 발생했을 때 기본 키값이 변경된다. 한편, 겸임 위치가 변경되는 경우 기존의 열을 지우고 새로운 열을 등록하는 대신 `겸임 시작일``종료일`이라는 문자열을 추가하여 기본키를 변경 하지 않는 형태로 하는 경우도 많다.
• 레코드 수가 많아진다.
  매핑 테이블의 레코드 수는 매핑의 소스가 되는 두 개의 테이블보다 많다. 수 많은 레코드가 되면 사이즈도 수백 GB 단위로 늘어나기 때문에 제대로 성능을 낼지 장담하기 어려운 과제가 된다. 게임에서 유저끼리의 대전 정보를 (user_id, target_user_id) 등으로 관리하는 경우가 많다. 이러한 경우는 쉽게 억 단위의 레코드에 도달하게 된다. 한 개의 서버 시스템으로는 데이터의 양이 너무 많아 다 수용할 수 없을 경우에는 테이블을 분할하여 복수의 시스템에서 갖고 있도록 하는 접근 방식이 사용될 수도 있다.

=> 결론

먼저 저장하는 데이터 항목이 내가 하고자 하는 서비스를 커버가능한지 파악이 중요하다. 예로 겸임과 같은 부서 정보가 특정 상황에 따라서 늘어날 수 있다면 적용할 수 있는 방법은 중복 레코드 추가, 칼럼 추가, 1:N 테이블 추가 방법이 있다. 가장 쉽게 생각할 수 잇는 중복 레코드 추가, 칼럼 추가 방식은 또 다른 문제점과 한계가 명확하다. 결국 편리를 위한 어플리케이션때문에 불편해지거나, 원하는 서비스를 커버할 수 없다거나 하는 큰 문제점이다. 1:N 테이블 추가 방식은 이러한 커다란 문제점은 없지만 두 테이블의 개수보다 더 많은 레코드가 생성될 수 있다는 문제점이 있다. 그에따라 복수의 테이블을 분할하여 복수의 시스템에 갖도록 하는 최적화 방식이 존재한다는 것을 알게 되었다.

# 정규화 이론의 기본을 파악해 두자.

지금까지 언급한 이야기는 데이터베이스 설계의 고과서에서 `정규화 이론`이라는 것으로 알려져 있다. 정규화 이론은 객체지향 이론 등과 비교하면 수학적으로 빈틈이 없는 이론으로, 어떤 테이블이 정규화에 따르지 않고 또 어떤 문제가 일어날 것인지에 대해 어느 정도 기계적으로 이끌어낼 수 있다. 용어로서는 제 1 정규형 ~ 제 5 정규형, 보이스코드 정규형 등의 여러 가지가 있다. 각 정규형 규칙을 암기할 필요는 없지만 본문에서 언급한 것과 같은 문제가 왜 발생하는지 원리를 이해하는 것은 중요하다.

제 1 정규형

테이블 구성에서 중복 또는 반복, 복합 값 등을 포함한 구조는 `제1정규형이 아닌 테이블`이라고 한다. 특히 중복한 열 값의 일부를 수정하거나 해서 그것을 자동으로 감지할 수 없다. 따라서 같은 이름의 조직임에도 주소가 다른 모순된 상태가 발생할 수 있다. 

  프로그래밍으로 말하자면, 카피 앤 패이스트를 마구 사용한 프로그램과 같은 느낌이라고도 표현할 수 있겠다. 실제 테이블의 내용을 보면 `이 중복은 도대체 뭐야?`라고 바로 알게 된다. 

제 2 정규형

기본 키가 여러 열로 구성되어 있고 그 중 일부 열의 값을 의해서만 결정되는 열이 있는 경우, 해당 테이블을 `제2정규형이 아닌 테이블`이라고 부른다.

emp_id PK	dept_id	dept_name	created
1	100	인사부	2010-01-01

(created)등록일은 (emp_id, dept_id)의 두가지 사항을 모르면 특정할 수 없지만, 부서 이름은 dept_id만 정해지면 확일 할 수 있기 때문에 이 테이블에 속하는 것은 적절치 않다. 이에 대한 문제점은 동일dept_id인데도 부서명이 사로 다른 레코드가 등록될 가능성이 있다는(충돌하는) 것이다. 

제 3 정규형

원칙적으로 테이블의 모든 열은 기본 키 값에 따라 단 하나로 결정되어야 한다. 그렇게 하지 않으면 레코드 간의 일관성이 없는 상태가 되는 등 문제를 야기할 위험이 있기 때문이다. 

emp_id PK	dept_id	dept_name
1	100	인사부

부서 번호의 경우 기본 키는 아니지만, 부서 이름은 부서 번호에 따라 결정되므로 기본 키의 사원 번호와는 직접 관련이 없다. 이것은 무엇이 문제인가 하면 같은 부서 번호임에도 불구하고 다른 dept_name을 등록할 수 있다라는 점이다. 여러 한 열을 포함하는 테이블 관계를 `제 3 정규형이 아니다`라고 말한다.

정규형은 어디까지 이해해야 하는가?

일반적으로 데이터베이스 설계의 교과서로 학습하는 것은 대부분 제3정규형까지인 경우가 많다. 그러한 점에서 제 3 정규형이면 충분하다라는 말을 들은 적이 있는 사람도 많이 않을까 싶다. 그러나 위의 emp, dept 테이블을 다시 검토해보자. 기본 키가 아닌 열은 모두 기본키 값이 정해지면 고유의 값으로 결정되기 때문에 제 3정규형의 조건을 충족한다. 하지만 현실은 어떤가? 겸임할 수 없다는 심각한 문제점이 있었다. (emp_id, dept_id)라는 조합을 고려하지 않기 때문에 생겨난 결함이다. -> 실제 현장의 데이터베이스 설계에서는 이러한 조합의 결함에 주의하여 테이블 간의 관계를 밝혀내야 한다. 그럼 도대체 어떻게 이러한 테이블이 필요하다는 것을 알 수 있을까? -> 그것은 어느 정도 테이블 설계에 익숙해지는 수밖에 없다. 사원 번호를 키로하여 부서 번호를 속성 열로 가진 테이블을 보면, 사원 번호에 대해 부서 번호를 속성 열로 가진 테이블을 보면 사원 번호에 대해 부서 번호가 단 한개로 결정되는 것이 기계적으로 결정된다. 이것은 하나의 사원 번호에 대해 여러 부서 번호를 등록할 수 없다는 것을 의미한다. 이 때문에 겸임할 수 없다는 업무상의 제약에 도달할 수 있다.

테이블 설계에 대해 알아보았다. 우선 `정규화`를 하는 것이 중요하고, 테이블 구조에 따라 업무에
큰 제약을 받게 되는 경우가 있을 수 있다는 것도 알게 되었다.
때로는 `테이블 수가 많아지면 SQL 문이 복잡하게 된다. 그래서 정규화를 허물고 한개의 테이블로 하는 편이 좋다`
라는 이야기와 `SQL을 써야하는 RDBMS보다 쓸 필요 없는 NoSQL 쪽이 좋다`라는 주장을 듣는 경우가 있다.
이는 그리 간단한 문제가 아니며, 그러한 주제들에 대해서도 이후에 차분히 살펴보자.

=> 결론

정규형에 대해서 알아보았다. 내가 알고 있는 정규형을 하는 이유는 여러가지 이상현상을 방지하고자 하는 것으로 알고있다.

내가 알고 있던 각각의 정규형의 내용이다

• 제 1 정규형은 각 칼럼은 원자값을 가져야한다
• 제 2 정규형은 기본 키가 아닌 속성들은 기본키에 완전 함수 종속이 되어야한다.
• 제 3 정규형은 이행적 함수 종속을 제거해야한다.

하지만 이 책에서는 아래와 같이 표현되어있다. ( -> 적용이 되지않을 때 이상현상 )

• 제 1 정규형은 중복, 반복, 복합 값 등을 포함하지 않아야 한다. -> 같은 이름의 조직임에도 이름이 다른 이상현상이 발생할 수 있다.
• 제 2 정규형은 기본키가 여러 칼럼으로 되어있고 그 중 일부 열에 의해서 결정되는 속성이 없어야 한다. -> 동일 dept_id인데도 부서명이 서로 다른 이상현상
• 제 3 정규형은 모든 열은 기본키 단 하나에 결정 되어야 한다. -> 레코드 간의 일관성이 없는 상태가 된다. 같은 dept_id인데도 부서명이 서로 다른 이상현상

같은 말이지만 책에서의 이해가 조금 더 깊고 쉽게 다가오는 것 같다. 기존의 알고있던 지식이 이해보다는 암기에 가까웠던것 같다.