[sql] 관계형 데이터베이스에 계층 적 데이터를 저장하기위한 옵션은 무엇입니까?



2 Answers

이 질문에 대한 답변은 매우 부분적이지만 아직 유용하길 바랍니다.

Microsoft SQL Server 2008은 계층 적 데이터 관리에 매우 유용한 두 가지 기능을 구현합니다.

  • HierarchyId 데이터 형식
  • with 키워드를 사용하여 공통 테이블 표현식.

시작을 위해 MSDN의 Kent Tegels의 "SQL Server 2008을 사용하여 데이터 계층 구조 모델링"을 살펴보십시오. 또한 내 자신의 질문을보십시오 : SQL Server 2008의 재귀 동일한 테이블 쿼리

Question

좋은 개요

일반적으로 빠른 읽기 시간 (예 : 중첩 세트) 또는 빠른 쓰기 시간 (인접성 목록)을 결정합니다. 일반적으로, 귀하는 귀하의 필요에 가장 적합한 아래 옵션의 조합으로 끝납니다. 다음은 몇 가지 심도있는 독서를 제공합니다.

옵션

내가 아는 기능과 일반적인 기능 :

  1. 인접 목록 :
    • 열 : ID, ParentID
    • 쉽게 구현할 수 있습니다.
    • 저렴한 노드 이동, 삽입 및 삭제
    • 비용이 많이 드는 레벨 (계산 된 열로 저장할 수 있음), 조상 및 자손 (레벨 열과 결합 된 브리지 테이블), 경로 (계보 열은 해결할 수 있음)를 찾습니다.
    • 통과를 지원하는 데이터베이스에서 공통 테이블 표현식 을 사용하십시오.
  2. 중첩 세트 (일명 수정 된 선매 트리 순회)
    • 조 셀코 (Joe Celko)가 많은 기사와 그의 저서 " 스마티 (Smarties)를위한 SQL의 트리와 계층 구조"에서 대중화 됨
    • 열 : 왼쪽, 오른쪽
    • 저렴한 수준, 조상, 자손
    • 휘발성 인코딩 - 이동, 삽입, 삭제가 더 비쌉니다.
    • 특정 정렬 순서가 필요합니다 (예 : created). 따라서 모든 자손을 다른 순서로 정렬하려면 추가 작업이 필요합니다.
  3. 중첩 된 간격
    • 중첩 된 집합과 비슷하지만 real / float / decimal로 인코딩이 휘발성이 아니므로 (저렴한 이동 / 삽입 / 삭제)
    • 실수 / 실수 / 십진수 표시 문제를 처리해야합니다.
    • 보다 복잡한 행렬 인코딩 변형 은 "자유"에 대한 구체화 된 경로와 같은 조상 (ancestor) 인코딩의 이점을 추가합니다.
  4. 브리지 테이블 (일명 Closure Table :이 접근 방식을 유지하기 위해 트리거를 사용하는 방법에 대한 좋은 아이디어)
    • 열 : 조상, 자손
    • 그것이 묘사하는 테이블과는 별개입니다.
    • 하나 이상의 계층 구조에 일부 노드를 포함 할 수 있습니다.
    • 싸구려 조상과 자손 (비록 순서가 다르지는 않지만)
    • 계층 구조에 대한 완전한 지식은 다른 옵션과 결합되어야합니다.
  5. 플랫 테이블
    • 레벨 및 순위 (예 : 순서) 열을 각 레코드에 추가하는 인접 목록 수정.
    • 비싼 이동 및 삭제
    • 싼 조상 및 자손
    • 좋은 사용 : 스레드 토론 - 포럼 / 블로그 주석
  6. 계보 열 ( 구체화 경로 , 경로 열거라고도 함)
    • 칼럼 : 혈통 (예 : / 부모 / 자식 / 손자 / etc ...)
    • 계층이 얼마나 깊은지를 제한하십시오.
    • 자손 저렴 (예 : LEFT(lineage, #) = '/enumerated/path' )
    • 가계 (데이터베이스 특정 쿼리)
  7. 여러 계보 열
    • 열 : 각 계보 수준마다 하나씩, 루트까지 모든 부모를 참조하고, 항목의 수준에서 아래쪽 수준은 NULL로 설정됩니다.
    • 계층이 얼마나 깊은 지에 대한 제한
    • 저렴한 조상, 자손, 수준
    • 저렴한 삽입, 삭제, 나뭇잎의 이동
    • 값 비싼 삽입, 삭제, 내부 노드 이동

데이터베이스 관련 노트

MySQL

신탁

  • 인접 목록을 탐색하려면 CONNECT BY 를 사용하십시오.

PostgreSQL

SQL 서버

  • 일반 요약
  • 2008 제공 HierarchyId 데이터 형식은 리니지 컬럼 접근 방식을 지원하고 표현할 수있는 깊이를 확장합니다.



이것은 정말로 둥근 구멍 질문입니다.

관계형 데이터베이스와 SQL이 당신이 가지고 있거나 사용하고자하는 유일한 망치라면 지금까지 게시 된 해답이 적절합니다. 그러나 계층 적 데이터를 처리하도록 설계된 도구를 사용하는 것이 좋습니다. 그래프 데이터베이스 는 복잡한 계층 적 데이터에 이상적입니다.

관계형 모델의 비효율 성과 관계형 모델에 그래프 / 계층 모델을 매핑하는 코드 / 쿼리 솔루션의 복잡성은 그래프 데이터베이스 솔루션이 동일한 문제를 해결할 수있는 용이성과 비교할 때 그만한 가치가 없습니다.

BOM을 공통의 계층 적 데이터 구조로 생각하십시오.

class Component extends Vertex {
    long assetId;
    long partNumber;
    long material;
    long amount;
};

class PartOf extends Edge {
};

class AdjacentTo extends Edge {
};

두 개의 하위 어셈블리 간의 최단 경로 : 간단한 그래프 탐색 알고리즘. 허용 가능한 경로는 기준에 따라 자격이 부여 될 수 있습니다.

유사성 : 두 어셈블리 간의 유사도는 얼마입니까? 두 하위 트리의 교차와 결합을 계산하는 두 하위 트리 모두에서 순회를 수행합니다. 비슷한 비율은 교차를 노동 조합으로 나눈 값입니다.

일시적 폐쇄 : 하위 트리를 이동하여 관심 분야 (예 : "알루미늄이 하위 어셈블리에 얼마나 있습니까?")를 합산합니다.

예, SQL 및 관계형 데이터베이스로 문제를 해결할 수 있습니다. 그러나 작업에 적합한 도구를 기꺼이 사용하려는 경우 훨씬 더 나은 방법이 있습니다.




인접 모델 + 중첩 세트 모델

트리에 새 항목을 쉽게 삽입 할 수 있으므로 (분기 항목에 새 항목을 삽입하기 만하면됩니다) 신속하게 쿼리 할 수 ​​있기 때문에갔습니다.

+-------------+----------------------+--------+-----+-----+
| category_id | name                 | parent | lft | rgt |
+-------------+----------------------+--------+-----+-----+
|           1 | ELECTRONICS          |   NULL |   1 |  20 |
|           2 | TELEVISIONS          |      1 |   2 |   9 |
|           3 | TUBE                 |      2 |   3 |   4 |
|           4 | LCD                  |      2 |   5 |   6 |
|           5 | PLASMA               |      2 |   7 |   8 |
|           6 | PORTABLE ELECTRONICS |      1 |  10 |  19 |
|           7 | MP3 PLAYERS          |      6 |  11 |  14 |
|           8 | FLASH                |      7 |  12 |  13 |
|           9 | CD PLAYERS           |      6 |  15 |  16 |
|          10 | 2 WAY RADIOS         |      6 |  17 |  18 |
+-------------+----------------------+--------+-----+-----+
  • 부모의 모든 자식이 필요할 때마다 parent 열에 쿼리하면됩니다.
  • 부모의 모든 자손이 필요하면 부모의 lftrgt 사이에 lft 가있는 항목을 쿼리하십시오.
  • 트리의 루트까지 모든 노드의 부모가 필요하면 노드의 rgt 보다 작은 노드와 노드의 rgt 보다 큰 항목을 쿼리하고 parent 노드 rgt 정렬합니다.

필자는 삽입물보다 빠르게 트리에 액세스하고 질의 할 필요가있었습니다. 그래서 이것을 선택했습니다.

유일한 문제는 새 항목을 삽입 할 때 leftright 열을 고정하는 것입니다. 잘 나는 그것에 대한 저장 프로 시저를 만들었고 내 케이스에서는 드문 새 항목을 삽입 할 때마다 그것을 호출했지만 정말 빠릅니다. Joe Celko의 저서에서 아이디어를 얻었습니다. 저장 프로 시저와 그 방법은 DBA SE https://dba.stackexchange.com/q/89051/41481 에서 설명합니다.




Related