RDB 1:M 관계 (3)

1:M 관계 중 특이한 경우인 재귀적 관계에 대해 알아보자.

1:M 재귀적 관계

1:M 재귀적 관계란 무엇일까?

회사와 부서가 존재할 때, 부서 아래 하위 부서가 존재하는 경우 1:M 재귀적 관계로 볼 수 있다. 상위 부서와 하위 부서도 1:M 관계로 볼 수 있다.

1:M 재귀적 관계의 또 다른 예로 디렉토리가 있다. 1:M 관계가 반복적으로 이어지는 관계이다.

아래 이미지는 회사와 상위 부서, 하위 부서의 관계도를 나타낸 이미지이다.

재귀적 관계에 놓인 테이블을(부서) 중심으로 상위, 하위 관계를 살펴보자.

우선, 재귀적 관계 테이블을 아래와 같이 설계했다고 가정해보자.

이때, 위 테이블의 상위 부서 ID와 하위 부서 ID가 테이블에 포함되어도 문제가 없을지 고민해보자.

하위 부서의 경우 M의 위치에 있기 때문에 재귀적 관계 테이블에 포함시킬 수 없다. 그 이유는 하위 부서 ID(FK)를 포함시키면 부서 ID(자기 자신, PK)의 중복이 발생하기 때문이다.
또한 하위 ID 여러 개를 하나의 행으로 저장할 수 없다. 각 속성은 원자값을 가져야 한다는 DB 설계 대원칙을 지켜야 하기 때문이다.

최상위 부서의 경우 상위 부서 ID는 1(자기 자신)의 위치에 있다. 따라서 문제 없이 테이블에 포함시킬 수 있다. 즉, 자기 자신을 참조하는 것이다.(self join)
1:M 재귀적 관계에 놓인 중간 위치의 테이블이라 하더라도 상위 부서(부모)는 단 하나이기 때문에, 재귀적 관계 테이블에 포함시켜도 아무 이상 없다.

재귀적 관계 테이블의 상하 관계를 따져서 문제가 없도로 수정한 테이블은 다음과 같다.

⭐️ SELF JOIN

1:M 재귀적 관계처럼 트리 구조를 갖는 테이블 스키마는 반드시 SELF JOIN 처럼 PK와 FK 모두 본인이 갖는, 즉 자기 자신을 참조하는 스키마로 설계해야 한다.

가능하다.

예를 들어, FK(상위 부서 ID)가 없는 경우에는 재귀적 관계에서 최상위 위치로 볼 수 있다.

즉, 최상위 부서의 경우에는 상위 부서 ID가 NULL 이 된다.

INSERT INTO 부서1 VALUES (1, '총무부', NULL);
INSERT INTO 부서1 VALUES (2, '총무1과', 1);
INSERT INTO 부서1 VALUES (3, '총무2과', 1);

SELECT * FROM 부서1;

최상위 부서인 총무부 아래에 총무1과와 총무2과가 존재한다고 했을 때 아래와 같은 재귀적 관계 테이블이 생성된다.

1:M 재귀적 관계(트리 구조)를 SQL문의 셀프 조인으로 보면 아래와 같다.

SELECT * FROM 부서1 a
JOIN 부서1 b on a.부서ID = b.상위부서ID

	부서ID	부서명	상위부서ID	부서ID	부서명	상위부서ID
1	1	총무부	NULL	2	총무1과	1
2	1	총무부	NULL	3	총무2과	1

여기서 총무1과에 또 재귀적으로 총무1팀, 총무2팀이 있다고 가정해보자.

INSERT INTO 부서1 VALUES (4, '총무1팀', 2);
INSERT INTO 부서1 VALUES (5, '총무2팀', 2);

마찬가지로 셀프조인하여 트리 구조로 살펴보면 아래와 같다.

	부서ID	부서명	상위부서ID	부서ID	부서명	상위부서ID
1	1	총무부	NULL	2	총무1과	1
2	1	총무부	NULL	3	총무2과	1
3	2	총무1과	1	4	총무1팀	2
4	2	총무1과	1	5	총무2팀	2
5	3	총무2과	1	NULL	NULL	NULL
6	4	총무1팀	2	NULL	NULL	NULL
7	5	총무2팀	2	NULL	NULL	NULL

위 셀프 조인한 테이블을 보고 다음과 같은 사실을 알 수 있다.

왼쪽 테이블의 상위 부서 ID가 NULL 이면 최상위 부서이다. 반대로 오른쪽 테이블의 부서 ID가 NULL인 경우 최하위 부서이다.

💡 자기 자신이 부모 테이블이자, 자식 테이블인 것을 1:M 재귀적 관계로 본다.

만약 총무1과의 관계도(트리 구조)를 보고 싶다면?

셀프 조인에서 총무1과의 부서 ID를 WHERE절에 적용하여 조회하면 된다.

SELECT * FROM 부서1 a
JOIN 부서1 b on a.부서ID = b.상위부서ID
WHERE a.부서ID = 2;

	부서ID	부서명	상위부서ID	부서ID	부서명	상위부서ID
1	2	총무1과	1	4	총무1팀	2
2	2	총무1과	1	5	총무2팀	2

회사 테이블의 회사 ID(PK)를 부서 재귀적 테이블의 FK로 추가하면 된다.

최종적으로 재귀적 관계(상위 부서 - 하위 부서)를 포함한 회사 - 부서 간의 1:M 관계 완성이다.