[Spring] RDB 에서 계층적인 데이터 구조 관리 전략 - Closure table

Closure table

관계형 데이터베이스에서 계층적인 데이터 구조 관리

관계형 데이터베이스 (RDB) 는 계층적인 데이터 구조를 표현하기에 적합하지 않은 관계형 모델을 기초로 설계되어 있습니다. 반면에 관계형 데이터베이스를 활용하는 서버는 주로 객체지향 언어로 작성되며 내부적으로 이진트리와 같은 계층적인 데이터 구조를 자주 활용합니다. 따라서 서버에서 그래프 구조의 데이터를 계층적인 데이터 구조 관리에 적합하지 않은 관계형 데이터베이스에 어떻게든 저장하려면 아래와 같은 전략이 필요합니다.

관계형 데이터베이스에 그래프를 표현하는 전략 4 가지

• Adjacency list
• Path enumeration
• Nested set
• Closure table

계층적인 데이터 구조를 관계형 데이터베이스에 저장하게 되면 서버가 데이터베이스의 무결성을 책임져야 합니다. 관계형 데이터베이스는 그래프 구조의 데이터의 무결성을 체크하는 기능이 없기 때문에 의도치 않게 데이터가 오염되는 사태를 맞이하게 될 수도 있으니 주의해야 합니다.

Closure table

Closure table 전략은 다른 전략과 다르게 계층 간의 관계를 다른 테이블에서 관리합니다. 마치 다대다 관계를 일대다 관계와 다대일 관계로 푸는 것과 비슷합니다.

예제 테이블

CREATE TABLE family (
  id SMALLINT(5) NOT NULL AUTO_INCREMENT,
  role VARCHAR(50) NOT NULL,
  PRIMARY KEY(id)
);

CREATE TABLE family_relation (
  ancestorId SMALLINT(5) NOT NULL,
  descendantId SMALLINT(5) NOT NULL,
  depth SMALLINT(5) NOT NULL,
  PRIMARY KEY(ancestorId, descendantId)
);

family_relation 테이블에 family 관계를 저장하여 계층적인 데이터 구조를 관리합니다. depth 로 계층 높이를 지정합니다.

예제 데이터

-- 부모와 자식 간의 관계
INSERT INTO family_relation VALUES (@PARENTS, @PARENTS, 0);
INSERT INTO family_relation VALUES (@PARENTS, @PAR_DAUGHTER, 1);
INSERT INTO family_relation VALUES (@PARENTS, @PAR_SON, 1);
INSERT INTO family_relation VALUES (@PARENTS, @PAR_DAU_GRAND1, 2);
INSERT INTO family_relation VALUES (@PARENTS, @PAR_DAU_GRAND2, 2);
INSERT INTO family_relation VALUES (@PARENTS, @PAR_SON_GRAND1, 2);
INSERT INTO family_relation VALUES (@PARENTS, @PAR_SON_GRAND2, 2);

-- 딸과 자식 간의 관계
INSERT INTO family_relation VALUES (@PAR_DAUGHTER, @PAR_DAUGHTER, 0);
INSERT INTO family_relation VALUES (@PAR_DAUGHTER, @PAR_DAU_GRAND1, 1);
INSERT INTO family_relation VALUES (@PAR_DAUGHTER, @PAR_DAU_GRAND2, 1);

-- 아들과 자식 간의 관계
INSERT INTO family_relation VALUES (@PAR_SON, @PAR_SON, 0);
INSERT INTO family_relation VALUES (@PAR_SON, @PAR_SON_GRAND1, 1);
INSERT INTO family_relation VALUES (@PAR_SON, @PAR_SON_GRAND2, 1);

-- 손자와 그 자식 간의 관계
INSERT INTO family_relation VALUES (@PAR_DAU_GRAND1, @PAR_DAU_GRAND1, 0);
INSERT INTO family_relation VALUES (@PAR_DAU_GRAND2, @PAR_DAU_GRAND2, 0);
INSERT INTO family_relation VALUES (@PAR_SON_GRAND1, @PAR_SON_GRAND1, 0);
INSERT INTO family_relation VALUES (@PAR_SON_GRAND2, @PAR_SON_GRAND2, 0);

Closure table 전략은 다른 전략과 다르게 관계를 다른 테이블에서 관리하기 때문에 무결성을 검증하기 편리합니다. 실질적인 데이터는 family 에 저장되고 관계에 대한 표현은 family_relation 테이블에 저장되기 때문에, family_relation 에 외래키를 사용하여 무결성 유지하는데 활용할 수도 있고, 무결성을 검증하는 논리도 family_relation 만 신경쓰면 됩니다. 또한 관계형 데이터베이스 설계 원칙도 준수하기 편리한 전략입니다.

Closure table 에서 데이터를 검색하는 방법

Closure table 전략은 테이블이 나뉘어 있으므로 아래와 같이 SQL 구문에 JOIN 절을 사용하여 데이터를 검색합니다.

SELECT f.* FROM family f JOIN family_relation fr ON f.id = fr.descendantId WHERE fr.ancestorId = 3

특정한 부모 밑에 특정한 자식 계층도 depth 필드로 쉽게 검색할 수 있습니다.

SELECT f.* FROM family f JOIN family_relation fr ON f.id = fr.descendantId WHERE fr.ancestorId = 3 AND fr.depth == 1

Closure table 에서 데이터를 조작하는 방법

특정 부모와 자식을 삭제하는 SQL 구문은 아래와 같습니다.

-- 기본키가 6 인 데이터와 하위 데이터를 모두 삭제합니다.
DELETE FROM family WHERE id = 6 OR id IN (SELECT descendantId FROM family_relation WHERE ancestorId = 6);
DELETE FROM family_relation WHERE descendantId = 6 OR ancestorId = 6;

다른 전략과 비슷하게 Closure table 전략이 무결성을 유지하는데 서버가 신경을 써줘야 하는 부분은 데이터를 추가하거나 삭제할 때 family_relation 테이블처럼 데이터 간 관계를 저장하는 테이블이 무결성을 위반하지 않도록 세심한 주의가 필요합니다.