如何设计用于导航具有菱形结构的分层区域的表

Question

我们的解决方案需要我们在区域层次结构中工作，如下所示。

                 STATE
                   |
                DISTRICT
                   |
                 TALUK
                /    \
               /      \
            HOBLI     PANCHAYAT
                \      /     
                 \    /
                  \  /
                VILLAGE

有两种方法可以从 Taluk 导航到村庄。通过 HOBLI 或通过 PANCHAYAT。

我们需要每个 STATE、DISTRICT、TALUK、HOBLI、PANCHAYAT、VILLAGE 的 PK（非商业 KEY）和 SERIAL_NUMBER/ID；然而，每个村庄都有 8 个额外属性。

如何在 PostgreSQL 8.4 中设计这个结构？

我之前的经验是在 Oracle 上，所以我想知道如何在 PostgreSQL 8.4 中导航层次结构？如果有的话，解决方案应该对读取/导航速度友好。

 ================================================================

Quassnoi：这是一个示例层次结构

                KARNATAKA
                    |
                    |
              TUMKUR (District)
                    |
                    |
                    |
              KUNIGAL (Taluk)
             /              \
            /                \
           /                  \
      HULIYUR DURGA(Hobli)   CHOWDANAKUPPE(Panchayat)
           \                         /
            \                       /
             \                     /
              \                   /
               \                 /
          Voddarakempapura(Village)
          Ankanahalli(Village)
          Chowdanakuppe(Village)
          Yedehalli(Village)

NAVIGATE：现在，我将展示 2 个单独的 UI 屏幕，每个屏幕都有单独的可导航层次结构

#1 使用 HOBLI 和 因此，对于#1，我需要从州、区、TALUK、HOBLI、VILLAGE 开始的整个树。使用上面的树，我需要

     KARNATAKA (State)
        |
        |
        |---TUMKUR (District)
                 |
                 |
                 |-----KUNIGAL(Taluk)
                                 |
                                 |
                               **|----HULIYUR DURGA(Hobli)**
                                               |
                                               |
                                               |---VODDARAKEMPAPURA(Village)
                                               |
                                               |---Yedehalli(Village)
                                               |
                                               |---Ankanahalli(Village)

#2 using PANCHAYAT。 因此，对于#2，我需要从州、区、TALUK、PANCHAYAT、VILLAGE 开始的整个树

     KARNATAKA (state)
        |
        |
        |---TUMKUR (District)
                 |
                 |
                 |-----KUNIGAL(Taluk)
                                 |
                                 |
                               **|----CHOWDANAKUPPE (Panchayat)**
                                               |
                                               |
                                               |---VODDARAKEMPAPURA(Village)
                                               |
                                               |---Ankanahalli(Village)
                                               |
                                               |---Chowdanakuppe(Village)

ResultSet

应该能够使用以下详细信息创建上述树。 我们需要每个 STATE、DISTRICT、TALUK、HOBLI、PANCHAYAT、VILLAGE 的 PK（非商业 KEY）和 SERIAL_NUMBER/ID 以及关系的名称和级别（类似于 ORACLE 的 LEVEL） 。

现在，获取上面的ResultSet就可以了。但将来，我们需要能够在 HOBLI/PANCHAYAT/TALUK 级别进行报告（一些聚合）。

=======================================

@Quassnoi #2, 非常感谢，

“如果您计划添加更多层次结构轴，则可能值得创建一个单独的表来存储层次结构（添加了轴字段），而不是将字段添加到表中。”

实际上，我简化了现有的要求，以免让任何人感到困惑。 实际层次结构如下所示 此类

                 STATE
                   |
                DISTRICT
                   |
                 TALUK
                /    \
               /      \
            HOBLI     PANCHAYAT
                \      /     
                 \    /
                  \  /
             REVENUE VILLAGE
                   |
                   |
               HABITATION

层次结构的示例数据如下所示

                KARNATAKA
                    |
              TUMKUR (District)
                    |
              KUNIGAL (Taluk)
             /              \
            /                \
      HULIYUR DURGA(Hobli)   CHOWDANAKUPPE(Panchayat)
             \                     /
              \                   /
               Thavarekere(Revenue Village)
             /                             \
 Bommanahalli(habitation)             Tavarekere(Habitation)

您下面的解决方案中的任何内容是否会因上述修改而改变？

另外，您是否建议我创建另一个如下所示的表来存储 Habitats 的 7 个属性？有没有更好的方法来存储此类信息？

CREATE TABLE habitatDetails
(
        id BIGINT NOT NULL PRIMARY KEY,
        serialNumber BIGINT NOT NULL,
        habitatid BIGINT NOT NULL, -- we will add these details only for habitats
        CONSTRAINT "habitatdetails_fk" FOREIGN KEY ("habitatid")
           REFERENCES "public"."t_hierarchy"("id")
        prop1 VARCHAR(128) ,
        prop2 VARCHAR(128) ,
        prop3 VARCHAR(128) ,
        prop4 VARCHAR(128) ,
        prop5 VARCHAR(128) ,
        prop6 VARCHAR(128) ,
        prop7 VARCHAR(128) ,
);

谢谢你，

Answer 1

CREATE TABLE t_hierarchy
(
        id BIGINT NOT NULL PRIMARY KEY,
        type VARCHAR(128) NOT NULL,
        name VARCHAR(128) NOT NULL,
        tax_parent BIGINT,
        gov_parent BIGINT,
        CHECK (NOT (tax_parent IS NULL AND gov_parent IS NULL))
);

CREATE INDEX ix_hierarchy_taxparent ON t_hierarchy (tax_parent);

CREATE INDEX ix_hierarchy_govparent ON t_hierarchy (gov_parent);

INSERT
INTO    t_hierarchy
VALUES  (1, 'State', 'Karnataka', 0, 0),
        (2, 'District', 'Tumkur', 1, 1),
        (3, 'Taluk', 'Kunigal', 2, 2),
        (4, 'Hobli', 'Huliyur Durga', 3, NULL),
        (5, 'Panchayat', 'Chowdanakuppe', NULL, 3),
        (6, 'Village', 'Voddarakempapura', 4, 5),
        (7, 'Village', 'Ankanahalli', 4, 5),
        (8, 'Village', 'Chowdanakuppe', 4, 5),
        (9, 'Village', 'Yedehalli', 4, 5)

CREATE OR REPLACE FUNCTION fn_hierarchy_tax(level INT, start BIGINT)
RETURNS TABLE (level INT, h t_hierarchy)
AS
$
        SELECT  $1, h
        FROM    t_hierarchy h
        WHERE   h.id = $2
        UNION ALL
        SELECT  (f).*
        FROM    (
                SELECT  fn_hierarchy_tax($1 + 1, h.id) f
                FROM    t_hierarchy h
                WHERE   h.tax_parent = $2
                ) q;
$
LANGUAGE 'sql';

CREATE OR REPLACE FUNCTION fn_hierarchy_tax(start BIGINT)
RETURNS TABLE (level INT, h t_hierarchy)
AS
$
        SELECT  fn_hierarchy_tax(1, $1);
$
LANGUAGE 'sql';

CREATE OR REPLACE FUNCTION fn_hierarchy_gov(level INT, start BIGINT)
RETURNS TABLE (level INT, h t_hierarchy)
AS
$
        SELECT  $1, h
        FROM    t_hierarchy h
        WHERE   h.id = $2
        UNION ALL
        SELECT  (f).*
        FROM    (
                SELECT  fn_hierarchy_gov($1 + 1, h.id) f
                FROM    t_hierarchy h
                WHERE   h.gov_parent = $2
                ) q;
$
LANGUAGE 'sql';

CREATE OR REPLACE FUNCTION fn_hierarchy_gov(start BIGINT)
RETURNS TABLE (level INT, h t_hierarchy)
AS
$
        SELECT  fn_hierarchy_gov(1, $1);
$
LANGUAGE 'sql';

SELECT  ht.level, (ht.h).*
FROM    fn_hierarchy_tax(1) ht;

SELECT  ht.level, (ht.h).*
FROM    fn_hierarchy_gov(1) ht;

主要思想是将两个父级保留在两个不同的字段中，并使用 CONNECT BY 模拟（而不是递归 CTE）功能来保留顺序。

如果您计划添加更多层次结构轴，则可能值得创建一个单独的表来存储层次结构（添加了轴字段），而不是将字段添加到表中。

更新：

上述修改会改变您下面的解决方案中的任何内容吗？

不，它会正常工作的。

我所说的“轴”是指层次链。目前，有两个轴：政治等级制度（通过 hablis）和税收等级制度（通过 panchayats）。如果您计划添加更多轴（这当然不可能），您可以考虑将层次结构存储在另一个表中并向该表添加“轴”字段。再说一次，你想这样做的可能性很小，我只是为可能有类似问题的其他读者提到了这种可能性。

此外，您是否建议我创建另一个如下所示的表来存储 Habitats 的 7 个属性？有没有更好的方法来存储此类信息？

是的，将它们放在单独的表中是一个好主意。