목차

• 1. 관계형의 뜻
  • 1) 행은 위아래 순서를 가지지 않는다
  • 2) 컬럼은 좌, 우 순서를 가지지 않는다
  • 3) 중복 행은 허용되지 않는다
  • 4) 행은 숨은 컴포넌트를 갖지 않는다

• 2. 정규화에 대한 미신

• 3. 정규화란?
  • 1) 제 1 정규형
  • 2) 제 2 정규형
  • 3) 제 3 정규형
  • 4) 보이스-코드 정규형
  • 5) 제 4 정규형
  • 6) 제 5 정규형

1. 관계형의 뜻

• 관계형(relational)이란 용어는 테이블 사이의 관계를 뜻하는 게 아니라, 테이블 자체 또는 테이블과 컬럼 사이의 관게를 의미함.
• 수학자들은 관계(relation)를 서로 다른 domain의 값으로 구성된 두 집합의 모든 가능한 조합에 어떤 부분집합을 만드는 조건을 적용한 것이라 정의함.
• 테이블 정규화는 이 컬럼 및 테이블간의 관계가 적절한지 확인해야 하며, 이들은 몇 가지 조건을 만족해야 함.

1) 행은 위아래 순서를 가지지 않는다

• SQL에서 ORDER BY 절을 사용해 순서를 지정하지 않는 한, 쿼리는 예상할 수 없는 순서의 결과를 리턴함.

2) 컬럼은 좌, 우 순서를 가지지 않는다

• 테이블의 몇 번째 열의 데이터를 요청하는 것이 아닌, 어떤 컬럼의 데이터를 명확하게 요청해야 함.

3) 중복 행은 허용되지 않는다

• 각 행은 해당 테이블에서 유일하게 구분할 수 있는 식별자를 가져야 하며, 이를 통해 접근할 수 있어야 함.

4) 행은 숨은 컴포넌트를 갖지 않는다

• 컬럼은 데이터 값을 포함하며, 행 ID나 객체 ID와 같은 물리적 스토리지 지시자를 갖지 않는다.

2. 정규화에 대한 미신

• "정규화는 데이터베이스를 느리게 만든다. 반정규화(denomalization)는 데이터베이스를 빠르게 만든다."
  • 잘못된 이야기이다. 반정규화를 통해 특정 쿼리에 대해서는 편해지고 성능에도 도움이 되지만, 다른 쿼리에 대해서는 엄청난 비용을 초래함.

• "정규화에서는 데이터를 지식 테이블로 밀어내고 가상키를 사용해 참조하라고 한다."
  • 잘못된 이야기이다. 편의나 성능, 저장 효율성을 위해 가상키를 사용할 수 있고, 이런 이유는 정당하지만 이것이 정규화와 관련이 있는 것은 아니다.

• "정규화는 EAV 설계에서와 같이 속성을 최대한 분리하는 것이다."
  • 잘못된 이야기이다. 개발자들은 정규화를 사람이 읽기 어렵게 만들거나 쿼리하기 불편하게 하는 것으로 잘 못 알고 있지만, 사실은 그 반대임.

• "아무도 제 3 정규형 이상으로 정규화할 필요는 없어. 그 이상의 정규형은 너무 난해해서 절대 쓸 일이 없을 거야."
  • 잘못된 이야기이다. 한 연구에 의하면 데이터베이스의 20% 이상이 제 3 정규형 까지는 만족하지만, 제 4 정규형에는 위반된다고 함.
  • 이는 잠재적으로 데이터 손실을 초래할 수 있고 20%의 애플리케이션에서 발생할 수 있는 버그를 내재함.

3. 정규화란?

• 정규화 목표
  • 우리가 이해할 수 있는 방법으로 실세계에 대한 사실을 표현함.
  • 사실을 중복해 저장하는 것을 줄이고 데이터에 이상(anomaly)이 생기거나 모순이 생기는 것을 방지함.
  • 정합성을 지원함.

• 정규화의 목표 중 성능 향상은 목표가 아니며, 데이터를 옳바르게 저장하고 문제가 생기는 것을 회피하는데 도움을 주는 것이 목표임.
• 테이블이 정규화 규칙을 만족하면, 그 테이블을 정규형(normal form)이라고 하며, 정규화의 각 단계를 나타내는 전통적인 정규형은 5개가 존재함.
• 각 정규형은 관계를 설계함에 있어 특정 형태의 중복이나 이상을 제거하는데, 보통 테이블이 한 정규형을 만족하면, 그 테이블은 이전의 정규형을 모두 만족함.

1) 제 1 정규형

• 제 1 정규형의 가장 기본적인 요구사항은 테이블이 관계여야 함.
• 그 다음 요구사항은 테이블이 반복 그룹(repeating group)을 가지지 않아야 함.

2) 제 2 정규형

• 제 2 정규형은 테이블이 복합 PK를 가지지 않으면 제 1 정규형과 동일함.

CREATE TABLE BugsTags (
  bug_id  BIGINT NOT NULL,
  tag     VARCHAR(20) NOT NULL,
  tagger  BIGINT NOT NULL,
  coiner  BIGINT NOT NULL,
  PRIMARY KEY (bug_id, tag),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
  FOREIGN KEY (tagger) REFERENCES Accounts(account_id),
  FOREIGN KEY (coiner) REFERENCES Accounts(account_id)
);

• 만약, 위와 같은 테이블을 생성한 후 누군가 TAG 컬럼의 한 행만 COINER 값을 변경하고(충돌) 같은 TAG에 대한 다른 행은 변경하지 않을 경우 이상이 생길 수 있음.
• 제 2 정규형을 만족하려면, 주어진 태그에 대한 COINER를 한 번만 저장해야 하며, 이는 TAG 컬럼이 PK가 되는 테이블을 따로 정의해서 각 TAB당 행이 하나씩만 생기도록 해야 한다는 뜻임.
• 그리고 해당 TAG의 COINER도 BUGSTAGS 테이블 대신 새로운 테이블에 저장해야 이상을 방지할 수 있음

CREATE TABLE Tags (
  tag     VARCHAR(20) PRIMARY KEY,
  coiner  BIGINT NOT NULL,
  FOREIGN KEY (coiner) REFERENCES Accounts(account_id)
);

CREATE TABLE BugsTags (
  bug_id  BIGINT NOT NULL,
  tag     VARCHAR(20) NOT NULL,
  tagger  BIGINT NOT NULL,
  PRIMARY KEY (bug_id, tag),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
  FOREIGN KEY (tag) REFERENCES Tags(tag),
  FOREIGN KEY (tagger) REFERENCES Accounts(account_id)
);

3) 제 3 정규형

• BUGS 테이블에 버그를 수정하는 엔지니어의 이메일 주소를 저장하고 싶어서 아래처럼 테이블을 만들어보자.

CREATE TABLE Bugs (
  bug_id SERIAL PRIMARY KEY
  -- . . .
  assigned_to BIGINT,
  assigned_email VARCHAR(100),
  FOREIGN KEY (assigned_to) REFERENCES Accounts(account_id)
);

• 이 경우, 이메일은 할당된 엔지니어의 속성이지, 버그의 속성이 아니며, 이런 식으로 이메일을 저장하는 것은 중복이고, 제 2 정규형을 만족하지 못했던 테이블에서와 같은 이상이 발생할 위험이 있다.
• 제 2 정규형은 규칙을 깨는 컬럼은 최소한 복합 PK의 일부와 관계가 있지만, 제 3 정규형을 위반하는 이 예제에서는 규칙을 깨는 컬럼은 PK와 전혀 관계가 없다.

• 이를 수정하려면, 이메일 주소를 ACCOUNTS 테이블로 옮겨야 한다. 이메일은 중복 없이 ACCOUNTS 테이블의 PK에 직접 대응되기 때문에 여기가 있어야 할 옳바른 곳이다.

4) 보이스-코드 정규형

• 제 3 정규형보다 조금 강력한 버전을 보이스-코드 정규형(BCNF, Boyce-Codd Normal Form)이라 함.
• 제 3 정규형에서는 키가 아닌 모든 속성은 키에 종속적이어야 하지만, 보이스-코드 정규형에서는 키 컬럼 또한 이 규칙을 따라야 한다는 것임.

• 예를 들어, 총 3개의 태그(버그의 심각성을 나타내는 태그, 버그의 영향을 받는 서브시스템에 대한 태그, 버그의 수정 방법을 설명하는 태그)가 있을 경우, 각 버그는 각 타입의 태그를 하나씩만 가질 수 있다고 정의할 수 있음.
• 여기서 키 후보는 'BUG_ID + TAG'일 것이지만, 'BUG_ID + TAG_TYPE'이 될 수 있다( 두 가지 컬럼 쌍 모두 각 행을 하나씩 식별할 수 있으므로)
• 그림 5에서, 제 3 정규형을 만족하지만 보이스-코드 정규형은 아닌 테이블과 이를 어떻게 수정할 수 있는지를 볼 수 있음.

5) 제 4 정규형

• 데이터베이스를 변경해, 각 버그가 여러 사용자로부터 보고되고, 여러 개발자에게 할당되고, 여러 품질 엔지니어가 검증할 수 있게 하려 한다.

CREATE TABLE BugsAccounts (
  bug_id       BIGINT NOT NULL,
  reported_by  BIGINT,
  assigned_to  BIGINT,
  verified_by  BIGINT,
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
  FOREIGN KEY (reported_by) REFERENCES Accounts(account_id),
  FOREIGN KEY (assigned_to) REFERENCES Accounts(account_id),
  FOREIGN KEY (verified_by) REFERENCES Accounts(account_id)
);

• 만약 위와같은 테이블을 만들었을 경우, 4개 컬럼 모두 결합된 것으로 PK를 구성해야 함.
• 하지만, 버그는 할당되고 검증되기 전에 보고될 수 있으므로, ASSIGNED_TO, VERIFIED_BY는 NULL이 들어갈 수 있으므로 PK에 위반됨.
• 그러므로 세가지 목적을 달성하기 위해, 다대다 관게를 하나의 교차 테이블로 나타내려 할 떄, 제 4 정규형을 위반함
• 해결방안은, 테이블을 나눠 각 다대다 관계마다 교차테이블을 가지도록 해야 함.

CREATE TABLE BugsReported (
  bug_id       BIGINT NOT NULL,
  reported_by  BIGINT NOT NULL,
  PRIMARY KEY (bug_id, reported_by),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
  FOREIGN KEY (reported_by) REFERENCES Accounts(account_id)
);

CREATE TABLE BugsAssigned (
  bug_id       BIGINT NOT NULL,
  assigned_to  BIGINT NOT NULL,
  PRIMARY KEY (bug_id, assigned_to),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
  FOREIGN KEY (assigned_to) REFERENCES Accounts(account_id)
);

CREATE TABLE BugsVerified (
  bug_id       BIGINT NOT NULL,
  verified_by  BIGINT NOT NULL,
  PRIMARY KEY (bug_id, verified_by),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
  FOREIGN KEY (verified_by) REFERENCES Accounts(account_id)
);

6) 제 5 정규형

• 보이스-코드 정규형의 조건을 만족하고 복합 PK를 가지지 않는 테이블은 이미 제 5 정규형이지만, 추가 예제를 통해 제 5 정규형을 살펴봄.
• 특정 제품에 대해서만 작업을 하는 어떤 엔지니어가, 어떤 제품에 어떤 버그를 작업하고 있는지 알 수 있도록 하면서, 중복을 최소화하려고 함.
• 이를 위해, BUGSASSIGNED 테이블에 컬럼을 추가해 어떤 엔지니어가 어떤 제품에 대해 작업하는지 볼 수 있도록 하려고 함.

CREATE TABLE BugsAssigned (
  bug_id       BIGINT NOT NULL,
  assigned_to  BIGINT NOT NULL,
  product_id   BIGINT NOT NULL,
  PRIMARY KEY (bug_id, assigned_to),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
  FOREIGN KEY (assigned_to) REFERENCES Accounts(account_id),
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

하지만, 이 경우 엔지니어는 어떤 제품의 버그를 해결하는지 종속적인 관계만 확인만 할 수 있으며, 중복으로 저장할 수 있다.

이를 해결하는 방법은 각 관계를 분리된 테이블로 격리해야 함.

CREATE TABLE BugsAssigned (
  bug_id       BIGINT NOT NULL,
  assigned_to  BIGINT NOT NULL,
  PRIMARY KEY (bug_id, assigned_to),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
  FOREIGN KEY (assigned_to) REFERENCES Accounts(account_id),
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

CREATE TABLE EngineerProducts (
  account_id   BIGINT NOT NULL,
  product_id   BIGINT NOT NULL,
  PRIMARY KEY (account_id, product_id),
  FOREIGN KEY (account_id) REFERENCES Accounts(account_id),
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

문서에 대하여

• 최초작성자 : ~xsoft
• 최초작성일 : 2012년 01월 14일
• 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
• {*}이 문서의 내용은 인사이트(insight) 에서 출간한 'SQL AntiPatterns : 개발자가 알아야 할 25가지 SQL 함정과 해법'를 참고하였습니다.*