DB 계층 구조 설계 - 클로저 테이블 패턴

계층 구조인 조직도 테이블을 설계하게 되었다.

 

0. 기존에 접하던 테이블 구조

• 테이블 하나
• id 컬럼  |  부모_id 컬럼  |  기타 컬럼
• 계층형 쿼리 사용

 

프로젝트에서 자주 보던 계층형 쿼리는 한 번에 이해하기에 어렵다고 느꼈었다. 계층이 깊은 경우는 더더욱.

나는 이해하기 쉬운 코드가 좋다.

이번에 테이블 설계를 하면서 이해하기 쉬운 쿼리를 쓸 수 있는 방법이 없나 찾아봤다.

그러다 빌 카윈 Bill Karwin이라는 개발자의 아래 자료를 보게 되었다.

자료에 대한 간단한 요약과 적용한 결과(MySQL)를 공유하고자 한다.

 

https://www.slideshare.net/billkarwin/models-for-hierarchical-data

Models for hierarchical data

 

 

1. 인접 리스트 Adjacency List

출처:https://www.slideshare.net/billkarwin/models-for-hierarchical-data

• 0번 기존에 접하던 테이블 구조이다 그만큼 흔히 사용되는 구조
• 오라클에선 주로 CONNECT BY 계층형 쿼리를 사용, 대부분의 DB는 WITH RECURSIVE 재귀형 쿼리를 사용
• MySQL은 8.0부터 재귀형 쿼리 지원

 

 

2. 경로 나열 Path Enumeration

출처:https://www.slideshare.net/billkarwin/models-for-hierarchical-data

• *브레드크럼 Breadcrumbs에 용이
• 쿼리 예: SELECT * FROM Comments Where path LIKE '1/4/%';
• '/'를 처리하는 비용이 발생할 것으로 예상됨
• 경로에 대한 정보 관리가 필요한 경우 용이할듯 

*브레드크럼: 헨젤과 그레텔에 나오는 빵부스러기. 경로를 알려주는 네비게이션.

 

 

3. 중첩 집합 Nested Sets

출처:https://www.slideshare.net/billkarwin/models-for-hierarchical-data

• left 숫자는 모든 자식 노드의 숫자보다 작아야 함
• right 숫자는 모든 자식 노드의 숫자보다 커야 함
• 노드의 숫자는 부모 노드들의 숫자 사이에 있어야 함
• 쿼리 예: SELECT * FROM Comments parent JOIN Comments descendants ON descendants.nsleft BETWEEN parent.nsleft AND parent.nsright WHERE parent.comment_id = 4;

 

 

4. 클로저 테이블 Closure Table

출처:https://www.slideshare.net/billkarwin/models-for-hierarchical-data

• 다른 구조와 다른 점: 테이블을 하나 더 만든다
• 자기 자신을 포함(ancestor = descendant)하여 자식 노드 정보를 모두 저장
• 깊은 계층 구조에서 쿼리 이해와 사용이 쉬울 거라 판단하여 해당 구조 선택

 

아래는 데이터 추가 과정이다.

INSERT INTO Comments VALUES (8, 'Fran', 'I agree!);

INSERT INTO TreePaths (ancestor, descendant)

SELECT ancestor, 8 FROM TreePaths   // 5 노드의 부모 노드들을 복사하여 저장 (아래 그림의 연두색 화살표)

WHERE descendant = 5   // 5 노드 하위에 노드를 추가

UNION ALL SELECT 8, 8;   // 자기 자신 노드 정보

출처:https://www.slideshare.net/billkarwin/models-for-hierarchical-data

즉, 자기 자신 노드 정보 + 부모 노드의 부모 노드들의 정보를 추가하는 셈이다.

 

만약 5 노드와 자식 노드들을 삭제를 하려면 ancestor = 5 조건을 주면 된다.

 

 

5. 클로저 테이블 적용

MySQL, MyBatis, 화면은 jstree 라이브러리를 사용했다.

/* 조회 */
SELECT
  d.department_id, d.department_name, h.department_depth, h.ancestor_department_id, h.descendant_department_id
FROM
  department d
LEFT OUTER JOIN
  department_hierarchy h ON (d.department_id = h.descendant_department_id)
WHERE
  h.department_depth = 1 OR h.descendant_department_id = 1 /* 화면 라이브러리에 맞춰 조건을 넣어줬다 */
ORDER BY
  h.department_depth
  
/* 조직도 삽입 */
<selectKey resultType="int" keyProperty="department_id" order="BEFORE">
  SELECT NEXTVAL('seq_department_id')
</selectKey>
INSERT INTO department (
  department_id,
  department_name
)
VALUES (
  #{department_id},
  #{department_name}
)

/* 조직도계층 삽입 */
INSERT INTO department_hierarchy (
  ancestor_department_id,
  descendant_department_id,
  department_depth
)
SELECT
/* underThisDepartmentId 노드 아래에 추가, underThisDepartmentId의 부모 정보 복사하여 삽입 */
  ancestor_department_id, #{department_id}::integer, department_depth + 1
FROM
  department_hierarchy
WHERE
  descendant_department_id = #{underThisDepartmentId}::integer
/* 자기 자신 노드 정보 */
UNION ALL SELECT #{department_id}::integer, #{department_id}::integer, 0

 

클로저 테이블은 장단점이 명확하다. 쿼리 사용이 쉽다. 근데 테이블이 두 개 필요하다.

아래는 직접 사용하면서 느낀 장단점이다.

• 쿼리 이해가 비교적 쉬웠으면 했고, 쉬워졌다. 
• 조직도 테이블에는 조직도의 정보만, 조직도 계층 테이블에는 계층 정보만 있어 깔끔하다
• 테이블이 두 개이므로 DML 시 트랜잭션 필요

 

출처:https://www.slideshare.net/billkarwin/models-for-hierarchical-data