SQL 정규화(Normalization)

관계형 데이터베이스의 설계에서 중복을 최소화하게 데이터를 구조화 하는 프로세스를 정규화(Normalization)라고 한다. 데이터베이스 정규화의 목표는 이상이 있는 관계를 재구성하여 작고 잘 조직된 관계를 생성하는 것에 있다. 일반적으로 정규화란 크고, 제대로 조직되지 않은 테이블들과 관계들을 작고 잘 조직된 테이블과 관계들로 나누는 것을 포함한다.

데이터베이스 디자인의 표준은 데이터베이스가 완전히 정규화되게 디자인되어야한다. 그 뒤 일부가 성능상의 이유로 비정규화될 수는 있다.

지금부터 예제를 통해 정규화해 나가면서 설명한다.

나는 작은 쇼핑 사이트의 주문 처리 시스템을 구축했다. 그에 앞서 간단한 주문 데이터로는 어떤 것이 필요한지 생각해 볼 필요가 있다.

MySQL를 이용하여 다음과 같은 테이블을 구성한다.

CREATE TABLE order_table(
	주문번호 INTEGER NOT NULL,
	날짜 DATE,
	고객이름 VARCHAR(10),
	연락처 VARCHAR(11),
	주문상품 VARCHAR(100)
	);
	
INSERT INTO order_table VALUES(1, "2020-01-01", "박상민", "01012345678", "0001 OO 1개, 0002 XX 10개");
INSERT INTO order_table VALUES(2, "2020-01-02", "강윤석", "01011112222", "0001 OO 2개, 0002 XX 3개");
INSERT INTO order_table VALUES(3, "2020-02-01", "구본효", "01022223333", "0001 OO 3개, 0003 YY 1개");

해당 테이블에서 ‘주문 상품’ 속성은 특별한 형식 없이 대충 만들어진 것처럼 보여 문제가 될 수 있다고 생각했다. 따라서 상품은 상품코드를 이용해 다루기로 했다.

‘0001 OO 1개’라고 되어있는 부분은 상품코드가 0001인 상품 OO를 1개 주문했다는 뜻이다. 물론 고객이 한 번 주문할 때 여러 개의 상품을 주문할 수도 있다. 주문번호 1의 튜플을 살펴보면 ‘0001 OO이 1개, 0002 XX가 10개’로 두 가지 종류의 상품을 주문했다는 것을 알 수 있다. 이제, 이렇게 대충 만들어진 데이터를 정규화해 데이터베이스의 테이블로 생성한다.

SQL에서 정규화는 단계적으로 실시한다. 가장 첫 번째 단계는 제 1 정규화로 불리며, 이를 시행하면 제 1 정규형 테이블을 생성할 수 있다.

제 1 정규형

관계형 데이터베이스의 테이블에는 하나의 셀에 하나의 값만 저장할 수 있다는 제약이 존재한다. 따라서 주문상품의 데이터를 그대로 테이블로 만들 수는 없다. 적어도 상품명, 개수 데이터를 담은 세 개의 열로 나누어야한다.

CREATE TABLE order_list CREATE TABLE order_list SELECT 주문상품, 
	SUBSTRING_INDEX(주문상품, ', ', 1) AS 상품,
	SUBSTRING_INDEX(주문상품, ' ', 1) AS 상품코드,
	SUBSTRING_INDEX(SUBSTRING_INDEX(주문상품,' ', 2), ' ', -1) AS 상품명,
	SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(주문상품, ' ', 3), '개', 1),' ',-1) AS 구매개수
	FROM order_table

UNION

SELECT 주문상품,
	SUBSTRING_INDEX(주문상품, ', ', -1) AS 상품,
	SUBSTRING_INDEX(SUBSTRING_INDEX(주문상품, ', ', -1), ' ', 1) AS 상품코드,
	SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(주문상품,', ', -1), ' ', 2), ' ', -1) AS 상품명,
	SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(주문상품, ', ', -1), ' ', -1), '개',1) AS 구매개수
	FROM order_table;

주문상품 분할

주문상품 분할에 대한 테이블을 order_list로 정의하고 기존의 order_table과 조인을 수행한다. 이 때, 조인 key는 주문상품이다. 구현 코드는 다음과 같다.

CREATE TABLE order_table_NF1 SELECT order_table.주문번호, order_table.날짜, order_table.고객이름, order_table.연락처,
	order_list.상품코드, order_list.상품명, order_list.구매개수  FROM order_table 
	LEFT JOIN order_list
		ON order_table.주문상품 = order_list.주문상품
		ORDER BY 주문번호;

SELECT * FROM order_table_NF1;

제 1 정규화를 수행한 간단한 주문 데이터

위 표처럼 구분하면 하나의 셀에는 하나의 값만 저장되므로 테이블화 할 수 있다. 주문상품 데이터를 상품코드와 개수로 분할했기 때문에 열이 두 개 더 추가 되었고 행도 추가 되었다는 것을 알 수 있다. 이렇게 하나의 셀에 하나의 값만 저장할 수 있도록 하고, 반복되는 부분은 행 방향으로 늘려나가는 것이 제 1 정규화의 1단계이다.

제 1정규화에서는 중복을 제거하는 테이블의 분할도 수행한다. 뒤 테이블을 확인해보면, 주문번호, 날짜, 고객이름, 연락처 등이 동일한 값을 가지는 행이라는 것을 알 수 있다. 따라서 동일한 값을 가지는 행이 여러 개 존재하지 않도록 하나로 정리하는 작업을 수행한다.

먼저, 주문 테이블을 주문상품 테이블과 주문 테이블로 분할한다. 주문 테이블은 주문번호, 날짜, 성명, 연락처로 구성한다. 주문상품 테이블은 상품코드, 상품명, 개수로 구성하고 테이블 조인을 위하여 주문번호 열을 추가한다.

# 주문(order_num) 테이블 생성
CREATE TABLE order_num SELECT DISTINCT(주문번호), 날짜, 고객이름, 연락처
	FROM order_table_nf1;

# 주문상품(order_item) 테이블 생성
CREATE TABLE order_item SELECT 주문번호, 상품코드, 상품명, 구매개수
	FROM order_table_nf1;

주문

결과적으로, 반복되는 부분이 하나로 정리되었다. 따라서 주문 데이터가 변경되더라도 한 군데만 수정하면 된다. 또한 분할 전 상태의 데이터를 원할 때에도 결합하면 되므로 문제가 발생하지 않는다.

분할 이후의 주문 테이블을 확인해보면, 주문번호에는 중복된 값이 존재하지 않기 때문에 기본키(Primary Key)로 지정할 수 있다. 또한 주문상품 테이블에서는 주문 번호와 상품코드를 묶어 기본키로 지정할 수 있다.

ALTER TABLE order_num
	ADD CONSTRAINT pkey_order_num
		PRIMARY KEY(주문번호);

ALTER TABLE order_item
	ADD CONSTRAINT pkey_order_item
		PRIMARY KEY(주문번호, 상품코드);

지금까지 제 1 정규화에 대해 예시를 들어 살펴보았다. 제 1정규화의 특징은 반복되는 부분을 찾아내어 테이블을 분할하고 기본키가 될 열을 작성할 수 있는 형식으로 변환하는 과정이라고 볼 수 있을 것이다.

제 2 정규형

제 1 정규화에서 테이블에서 테이블에 기본키(Primary Key)를 작성한 것과 같은 방법으로, 제 2 정규화에서는 데이터가 중복하는 부분을 찾아내고 테이블로 분할해 나간다. 이때 기본키에 의해 특정되는 열과 그렇지 않은 열로 나누는 것으로 정규화를 수행한다.

주문상품

주문상품의 기본키는 주문번호와 상품코드 두 개의 열로 이루어져있다. 주문번호 1에 상품코드가 0001인 상품 주문량은 하나의 행이라는 것을 알 수 있다. 따라서 기본키를 바탕으로 특정되는 데이터이다. 상품명은 주문번화와 관계없이 상품코드만으로 특정할 수 있다. 상품코드는 기본키의 일부이긴 하지만 단독으로 기본키의 역할을 할 수 없다.

이처럼 두 가지로 분류할 수 있으므로 테이블 분할을 수행한다.

ALTER TABLE order_item DROP 상품명;

주문상품(order_item)

CREATE TABLE item_code SELECT DISTINCT(상품코드), 상품명  FROM order_item;

ALTER TABLE item_code
	ADD CONSTRAINT pkey_item_code
		PRIMARY KEY(상품코드);

상품(order_item)

상품 테이블은 상품코드만 기본키(Primary Key)를 지정했다. 이러한 방법을 부분 함수종속성을 찾아내서 테이블을 분할한다고 하고 제 2 정규화라고 부른다. 여기서 부분 함수종속성은 키 값을 이용해 데이터를 특정지을 수 있는 것을 가리킨다.

제 3 정규형

제 3정규화 역시 기본 논리는 중복하는 부분을 찾아내여 테이블을 분할하는 과정이다. 제 2 정규화의 경우는 기본키에 중복이 없는지 조사했던 반면 제 3 정규화에서는 기본키 이외의 부분에서 중복이 없는지 조사한다.

주문

분할하기 전의 테이블을 살펴보면 데이터가 중복되어 있다는 것을 확인할 수 있다. 고객이름 ‘박상민’이 여러 번 주문하는 경우가 존재하기 때문이다. 이때 주문 테이블에서 이름을 기준으로 연락처를 특정지을 수 있다. 여기서 중요한 점은 기본키는 주문번호로, 이름은 기본키와 관계가 없다는 것이다.

테이블을 분할하여 새로 만들 테이블의 이름을 ‘고객’으로 생성한다. 여기서 이름을 기본키로 지정하게 되면 동명이인의 경우 데이터를 제대로 저장할 수 없으므로 고객 번호를 기본키로 지정하여 테이블을 작성한다.

주문

고객

마치며

지금까지 데이터베이스 정규화(Normalization)에 대해 예시를 들며 알아봤다. 데이터베이스 정규화의 목적은 주로 두 가지로 볼 수 있다.

1. 불필요한 데이터를 제거한다.
2. 데이터 저장을 “논리적으로” 한다.

여기서 2번 데이터 저장을 논리적으로 한다는 것은 데이터 테이블의 구성이 논리적이고 직관적이어야 한다는 것이다.

정규화는 크게 세 가지로 나눌 수 있다.

1. 제 1 정규화

   반복되는 데이터를 열 방향이 아닌 행 방향으로 늘리는 작업

2. 제 2 정규화

   기본키 값을 이용해 데이터를 인덱싱할 수 있는 구조로 변경하는 작업

3. 제 3 정규화

   기본키 이외의 부분에서 중복이 없는지 조사하는 작업

하나의 데이터가 반드시 한 곳에먼 저장되어 있다면 데이터를 변경하더라도 한 곳만 변경하는 것으로 끝낼 수 있다는 장점이 존재한다. 반면 정규화되지 않은 경우에는 여기저기 중복 저장된 데이터를 검색하고 일일이 변경해야한다는 단점이 존재한다. 이것은 매우 번거로운 작업이고 인덱스로 지정된 열의 데이터가 변경되는 경우에는 인덱스도 재구축해야한다.