代码之家 › 专栏 › 技术社区 › anjanb

如何设计菱形结构层次区域导航表

hierarchical-data hierarchy navigation postgresql

anjanb · 技术社区 · 15 年前

我们的解决方案需要我们按照以下区域的层次结构工作。

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

从塔鲁克到村庄有两种导航方式。无论是通过霍布里还是通过潘查亚特。

我们需要一个pk(非商业密钥)和一个序列号/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)

导航:现在,我将展示两个独立的UI屏幕,每个屏幕都有单独的可导航层次结构。

霍布里第1 和因此,对于1,我需要整棵树,从州、区、塔鲁克、霍伯利、村开始。使用上面的树,我需要

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

#2使用Panchayat . 因此,对于2,我需要整棵树,从州、区、塔鲁克、潘查亚特、村开始。

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

结果集

应该能够创建具有以下详细信息的以上树。我们需要一个pk(非商业密钥)和一个序列号/id,用于每个州、地区、Taluk、Hobli、Panchayat、村以及关系的名称和级别(类似于Oracle的水平 )

目前,获取上述结果集是可以的。但在未来,我们需要能够在Hobli/Panchayat/Taluk级别进行报告(某些聚合)。

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

@库萨尼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)

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

另外,您是否建议我创建下面这样的另一个表来存储栖息地的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) ,
);

谢谢您,

1 回复 | 直到 15 年前

Quassnoi 15 年前

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 )保留订单的功能。

如果计划添加更多层次结构轴,则可能需要创建单独的表来存储层次结构(添加了轴字段),而不是将字段添加到表中。

更新:

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

不,它可以用。

“轴”是指层次链。目前,你有两个轴:政治等级(虽然哈布利斯)和税收等级(通过潘查雅特)。如果计划添加更多轴(这当然是不可能的),可以考虑将层次结构存储在另一个表中,并将“轴”字段添加到该表中。同样,你不可能想这样做,我只是为其他读者提到了这种可能性,他们可能也有类似的问题。

另外,您是否建议我创建下面这样的另一个表来存储栖息地的7个属性?有没有更好的方法来存储这样的信息?

是的,把它们放在单独的桌子上是个好主意。