정규화

제1정규형 : 모든 속성은 반드시 하나의 값을 가져야한다.

 

연락처라는 컬럼에 집번호, 핸드폰 번호, 직장번호를 섞어서 넣는다면 값을 구별하기 어려워지고,

불안정한 데이터 구조를 양산하고, 개발 오류 및 데이터 품질 문제까지 야기할 수 있다.

 

다른 예로 주문이라는 테이블을 가진 모델을 본다면, 컬럼에 Product1,Product2 가 있을 경우 

상품을 2개까지만 주문할 수 있고 갯수를 늘리고 싶다면

컬럼을 추가해야하는데, 이는 대부분 DBMS 에서 lock 을 발생시키기 때문에 이런 작업은 트랜젝션이 적은 시간대에 서비스 중단을 감행하고 실행한다. 또, 상품1,상품2를 빠르게 조회하기 위해 index 추가를 고려해야하는데 이는 조회 기능 외 나머지 수정,입력,삭제 속도가 느려진다는 것을 고려해야한다.

 

주문상세 엔티티를 추가해 아래와 같은 구조로 만들면 이런 제약에서 벗어날 수 있다.

 

제2정규형 : 엔티티의 일반속성은 주식별자 전체에 종속적이어야 한다.

 

위에 주문 상세 테이블의 데이터이다. 상품명이 중복된 걸 볼 수 있다.

주문번호와 상품번호는 엔티티의 식별자 의미를 갖고 있는데, 

상품명은 오직 상품번호에 의해서 결정된다.

함수 종속성은 데이터들이 어떤 기준값에 의해 종속되는 현상을 말한다.

여기서 상품번호는 결정자, 상품명은 종속자이며,

PK 는 주문번호와 상품번호인데 상품번호가 주문번호와는 무관하고, 상품번호에만 종속된 것을 부분 종속이라고 한다.

 

나중에 상품번호에 대한 상품명이 변경되었을 때 부하를 줄이고, 일관된 데이터 조회를 위해서는 

테이블을 분리하는 것이 좋다.

 

상품엔티티를 추가한 모델

 

제 3정규형: 엔티티의 일반속성 간에는 종속적이지 않는다.

위에 주문 테이블에서 고객번호는 주문번호에 종속적이다. 

그리고 고객명은 고객번호에 종속적이다. 

그렇기 때문에 고객명은 주문번호에 종속적이라고 할 수 있다.

만일 고객명이 변경되었다면 주문 엔티티의 고객명을 찾아 갱신해야한다.

이는 주문과 무관한 트랜젝션이다.

데이터 중복으로 인해 발생하는 문제는 성능 부하 및 정합성 오류로 제2차 정규형과 동일하다.

고객명이 아니라 고객주소하고 생각하면 주소는 이름보다 더 자주 바뀔 수 있다.

그러므로 아래와 같이 고객 엔티티를 분리해 관리해야 한다.

 

---

반정규화와 성능

정규화는 데이터의 중복을 최소화했다면, 반정규화는 성능을 위해 데이터 중복을 허용하는 것이다.

하지만 반정규화가 항상 성능을 향상시키는 것은 아니다.

조회성능을 향상시킬 수는 있겠지만, 그로 인해 입력,수정,삭제 성능은 저하될 수 있는 것을 염두해두어야한다.

 

1. 반정규화로 성능에 도움이 되는 경우

 

고객의 편의를 위해 주문서 작성 시 최근 결제 정보를 미리 세팅해 보여주고자 할 때.

최근 시용카드를 미리 세팅하는 요건일 경우의 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;

이럴 때 고객번호 1234 인 사람의 주문내역이 많을수록 주문테이블과 결제테이블의 조인 건수가 증가하게 돼

부하가 증가하고 성능이 나빠진다.

 

아래와 같이 결제 엔티티에 고객번호를 중복으로 추가하면 조인에 대한 성능 부하를 개선할 수 있다.

select A.결제수단번호
	from (select A.결제수단번호
    	from 결제 A
               	and A.고객번호 = 1234
                and A.결제수단구분코드 = '신용카드'
                order by A.결제일시 desc
           )A
where ROWNUM = 1;

 

결제테이블에 고객번호 + 결제수단구분코드 + 결제일시로 인덱스를 생성하고 

Index Range Scan Descending 으로 최종 1건만 읽어 가져올 수도 있다. 최적의 SQL로 성능 부하를 극적으로 개선할 수 있다.

 

2. 반정규화를 적용해 모델에서 성능이 저하되는 경우 

 

주문내역에 대한 배송 정보를 조회할 때.

주문정보는 주문 엔티티, 송장번호는 배송 엔티티에서 가져올 수 있고,

주문과 배송을 함께 조인해야한다.

하지만 성능을 위해 주문엔티티에 송장번호를 넣는 반정규화를 했다고 하자.

송장번호는 택배사에서 발송준비가 될 때 부여하는 번호로, 주문 완료 당시에는 나오지 않는다.

그러면 주문 엔티티에 데이터가 들어갈 땐 송장번호는 NULL 이며, 나중에 UPDATE 쿼리를 통해 데이터를 갱신해줘야한다.

이런 경우 배보다 배꼽이 더 큰 경우이다. 특히 AWS 와 같은 클라우드 환경에서 운영되는 시스템이라면 이런 불필요한 로직으로 인해 과금이 늘어나게 된다는 것도 유의해야한다.

반정규화는 데이터 불일치로 인한 정합성 문제뿐만 아니라, 불필요한 트랜젝션으로 인한 성능 문제를 만들어내기 때문이다.

---

관계와 조인

관계를 맺는 것은 부모의 식별자를 자식에게 상속시키는 행위이다.

이 때 부모의 식별자를 자식의 식별자에 포함하면 식별관계,

부모의 식별자를 자식의 일반속성으로 상속하면 비식별관계라고 하였다.

즉 관계를 맺는다는 것은 식별자를 상속하고, 상속된 속성을 매핑키로 활용하여 데이터를 결합해 볼 수 있다는 의미이다. 이를 SQL 에서는 조인이라고 한다.

 

계층형 데이터 모델이란 계층 구조를 가진 데이터를 지칭하는 것이다.

 

아래 쿼리는 직원 테이블이 자기 자신과 관계를 맺고 SMITH 라는 직원의 매니저를 알아내는 쿼리이다.

흔하게 보이는 셀프 조인 예시이다.

select B.ENAME
	from EMP A, EMP B
    where A.ENAME = 'SMITH'
    	AND A.MRG = B.EMPNO

 

상호배타적관계

주문 테이블에 개인이나 법인 둘 중 하나만 넣을 수 있다.

주문번호가 100인 주문의 주문자명을 보여주고자 할 때는 쿼리를 아래와 같이 작성해야한다.

select B.개인고객명
	from 주문 A, 개인고객 B
    where A.주문번호 = 100
    	and A.고객구분코드 = 1
    	and A.개인/법인번호 = B.개인번호
UNION ALL
select B.법인명
	from 주문 A, 법인고객 B
    where A.주문번호 = 100
    	and A.고객구분코드 = 2
    	and A.개인/법인번호 = B.법인번호
    

 

고객구분코드를 정확하게 알고 있다면 하나의 테이블만 조인해 찾을 수 있을 것이다.

또, 개인번호와 법인번호가 중복되지 않는다는 보장이 된다면 아래와 같은 SQL 도 가능하다.

select COALESCE(B.개인고객명,C.법인명) 고객명
	from 주문 A LEFT JOIN 개인고객 B
    ON (A.개인/법인번호 = B.개인번호) LEFT OUTER JOIN 법인고객 C
    ON (A.개인/법인번호 = C.법인번호)
    where A.주문번호 = 100;

---

모델이 표현하는 트랜젝션의 세계

주문을 할 때 주문 상세가 필수적으로 생성되는 관계라면 하나의 트랜젝션으로 묶어서 관리해야한다.

하나의 트랜젝션이라는 것은 ALL or NOTHING 이라는 원자성이 보장되도록 개발을 해야하는 것이다.

즉 커밋의 단위로 묶어 전체가 실행되거나 전체가 취소되어야 한다.

API 를 개발할 때 재사용성이라는 이유로 트랜젝션을 낱개로 뜯으면 안된다. 

잘못된 트랜젝션 처리는 데이터 정합성의 문제를 야기하고 데이터 품절에도 큰 영향을 미친다.

---

출처 

SQL 전문가 가이드 2020 개정판 -한국데이터 산업진흥원