그동안 일이 있어서... 블로그 포스팅도 미루고 코테 문제 푸는것도 미루고 ㅠㅠ 계획이 너무 꼬였다
암튼 이번엔 정규화와 반정규화에 대해 알아보자. "SQL 전문가 가이드"라는 책을 참고했다.
정규화
일단 쉽게 비유하자면 여러개로 뭉쳐있는 테이블을 여러개로 쪼개는 작업을 진행하는 것이다. 이 정규화도 여러 단계가 있는데, 하나씩 따라가보자.
제 1 정규형
모든 속성은 반드시 하나의 값을 가져야 한다
다음과 같은 테이블이 있다고 가정하자. 노란색으로 칠해진 컬럼은 PK이다.
그리고 이 테이블에 다음과 같이 레코드가 존재한다고 생각해보자.
이 테이블의 연락처 컬럼에 하나의 값만 들어가있는게 아니라, 여러개의 값이 들어가있다. 연락처에는 집전화와 휴대전화 등 다른 속성의 값들이 들어가있다. 이러면 여러 속성이 혼재되어 원하는 속성 값을 추출하기가 힘들다.
이를 분리하여 하나의 컬럼에는 하나의 속성만 갖게 하는것이 제 1 정규형이다. 고객 테이블을 고객과 고객연락처로 분리해보자.
그러면 고객연락처는 고객번호를 외래키로 사용하면서 주 키로 사용중이다. 이러한 관계를 "식별관계" 라고 하는데, 부모가 있어야만 자식이 존재할수 있는 강한 의존성을 나타낸다.
이렇게 되면 고객이 연락처를 아무렇게나 많이 만들어도 상관없다. 같은 고객번호에 순번만 하나씩 올려주면 된다. 집전화와 휴대폰을 구분하고 싶으면 고객연락처 테이블에 "연락처구분코드" 같은것을 추가해두면 된다. 이 컬럼을 추가하면 연락처에 이메일도 추가해서 구분해줄 수도 있다.
제 1 정규형은 단순히 하나의 컬럼에 여러개의 값이 들어가는 것 뿐만 아니라, 다른 유형의 중복 데이터를 의미할수도 있다. 다음은 정규화 이전의 테이블과 정규화 이후의 테이블이다.
정규화 이전의 테이블을 보자. 이런 테이블을 사용한다면 무슨 문제가 발생할까? 주문을 할 때 상품을 2개 초과해서 주문할수가 없다. 3개이상의 상품을 주문하고 싶다면, 상품번호3 / 상품명3 이라는 컬럼을 주문 테이블에 추가해야한다. 이는 정말 끔찍한 일이다. 서비스를 중단하고 DB를 마이그레이션해야하는 상황이 발생한다는 것이다.
이를 정규화하여 주문과 주문상세로 두면? 각 주문에 상품이 몇개가 들어가든 상관이 없다.
제 2 정규형
엔티티의 일반속성은 주식별자 전체에 종속적이어야 한다
위의 정규화 이후 주문상세 테이블에 다음과 같이 데이터가 저장돼있다고 하자.
자세히 보면 무엇인가 이상하다. 이 데이터에서 중복되는 데이터는 상품번호와 상품명이다. 하지만 상품번호는 주문번호와 같이 주 키에 속하는 식별자이기 때문에 중복되는 데이터라고 볼 수는 없다. 하지만 상품명은 주문번호와는 관계없이 오직 상품번호에 의해서만 결정된다. 이를 '종속적이다' 라고 표현한다. 상품명이 주 키인 <주문번호, 상품번호> 가 아니라 상품번호에서만 관리된다는 것이다. 상품명이 상품번호에 함수 종속성을 가지고 있다.
함수 종속성
함수 종속성은 데이터들이 어떤 기준값에 의해 종속되는 현상이다. 이 때 기준값을 결정자, 종속되는 값을 종속자라고 한다.
위 예시에서는 상품번호가 결정자, 상품명이 종속자가 될 것이다.
근데 상품번호도 결국 식별자의 일부이긴 하다. 상품명의 식별자의 일부에 종속적이기에 "부분 종속적" 이라고 표현한다. 이는 주 식별자 전체에 종속적이어야 한다는 제 2 정규형을 위배하는것이다.
그러면 다시 돌아와서, 제 2 정규형을 위배하면 어떤 일이 발생할까? 상품번호는 그대로인데, 상품명이 바뀐다면? 주문상세 테이블의 모든 레코드에 있는 상품명르 다 바꿔주어야 할것이다. 정말 귀찮고 쓸데없는 일이 아닐수 없다.
이를 정규화해보자.
이렇게 고치면 될것같다. 이러면 상품명이 바뀐다고 해도 일일히 다 찾아서 바꿔줄 필요가 없다.
하지만 join 문을 사용해야 하기 때문에 조회 시에는 살짝 성능이 안좋아질수는 있다.
제 3 정규형
이제 주문 테이블을 다시 보자. 고객번호는 주문번호에 종속적이고, 고객명은 고객번호에 종속적이다. 이는 계층적으로 봤을 때, 고객번호가 주문번호에 종속적이라는 것을 의미한다. 이를 이행적 종속이라 한다. 제 2 정규형에서 나타난 부분 종속과 무엇이 다르냐 할 수 있겠지만, 부분 종속은 기본 키의 일부에 종속적이고, 이행적 종속은 기본 키가 아닌 일반 컬럼에 종속적이라는 것을 의미한다.
제 2 정규형과 유사하게 고객명이 바뀌었다면, 주문 테이블에서 해당 고객명을 모두 찾아 바꿔주어야 한다.
이제 이것도 분리해보자
고객을 따로 분리해내고, 고객에는 새로운 컬럼을 추가했다. 만약 분리하지 않고 주문에 고객 관련 컬럼을 추가했다면, 주문 테이블은 본연의 정보가 아닌 고객의 정보 때문에 락이 걸렸을 것이다.
이 다음 정규형으로는 BCNF가 있다. BCNF의 경우에는 "릴레이션의 모든 결정자가 후보키" 여야 한다는 규칙이 있는데, DB 교수님께서 이건 잘 안쓴다고 했으니까 추후에 다시 포스팅해보곘다.
반정규화
그러면 정규화를 해놓으면 무조건 좋은걸까? 그건 또 아니다. 이렇게 테이블을 다 찢어놓았으니, join이 엄청나게 걸릴 것이다. 예를 들어 3 정규화를 해놓은 저 구조에서 "고객이 주문한 상품명"을 전부 가져오려면 어떻게 해야할까? 고객과 상품 사이에는 관계가 없다. 따라서 고객과 주문을 join하고, 주문과 주문상세를 join하고, 주문상세와 상품을 join 하는 식으로 조회해야할 것이다. 그렇다면 위의 경우는 반정규화를 해야할까?? 자신의 서비스를 잘 생각해보자, 고객이 과연 자신의 모든 주문내역을 자주 조회하는가? 특정 고객뿐만 아니라 모든 고객이 이러한 기능을 엄청나게 사용하는가? 그렇다면 반정규화가 필요할 것이다.
그러면 반정규화가 필요한 예시를 하나 들어보자.
다음과 같은 테이블이 있다고 가정하자.
그리고 이러한 테이블을 구성한 서비스에서 주문을 할 때 최근 결제 정보를 미리 세팅해두어 사용자 경험에 편의성을 주고 싶다고 할 때, 어떤 SQL 쿼리가 나갈까? 예를 들어 신용카드 정보를 미리 세팅할 때 말이다.
SELECT A.결제수단번호
FROM ( SELECT B.결제수단번호
FROM 주문 A, 결제 B
WHERE A.주문번호 = B.주문번호
AND A.고객번호 = 1234
AND B.결제수단구분코드 = '신용카드'
ORDER BY B.결제일시 DESC
) A
WHERE ROWNUM = 1;아마 이런 쿼리가 발생하지 않을까? 서브쿼리에서 주문의 주문번호와 결제의 주문번호를 JOIN한 다음 고객번호가 1234인 것과 결제수단이 신용카드인 컬럼들을 결제일시로 정렬해서 가져온다. 그 다음 서브쿼리를 통해 만들어진 테이블에서 첫번째 컬럼의 결제수단번호만 가져와 해당 결제수단의 정보를 채워주면 되는것 아닐까?
(책에서는 편의를 위해 fk를 결제수단번호에 걸어두지 않은 것 같은데, 결제수단이라는 테이블이 하나 더 있다고 생각하자.)
이러면 어떤 점이 문제일까? 고객번호가 1234인 고객의 주문이 많아지면 많아질수록 점점 성능이 나빠지는 문제가 생긴다. 이를 한번 반정규화해보자.
이런 식으로 반정규화를 했을 때, 위와 똑같이 조회를 한다면 SQL 쿼리가 어떻게 날아갈까?
SELECT A.결제수단번호
FROM ( SELECT A.결제수단번호
FROM 결제 A
WHERE A.고객번호 = 1234
AND A.결제수단구분코드 = '신용카드'
ORDER BY A.결제일시 DESC
) A
WHERE ROWNUM = 1;이렇게 join 자체가 빠져버리게 된다 !! 이렇게 해도 충분히 빨라지지만, 인덱스를 (고객번호 + 결제수단구분코드 + 결제일시)로 걸어버리면? 훨씬 빠르게 될 것 같다.
이렇듯 개발자는 정합성과 성능을 잘 조율하여 정규화와 반정규화 둘 중 하나를 선택해야한다.
그런데 요새 개발하면서 느낀거지만, 제 1 정규형이 불편할때가 많다. 하나의 컬럼 안에 여러개의 정보를 저장해두지를 못하니까... 예를 들면 게시글이 하나 있는데, 이 게시글에는 여러개의 사진이 들어가야 할 때가 있다. 하지만 여러개를 넣을수는 없다. 1 정규형을 위배하니까!! 요새 ORM을 보니까 이를 교묘하게 문자열로 바꾸어서 집어넣은 다음 자동으로 변환해주는 기능이 있는 것 같다. 나도 잘 써먹었고...