ansaurus

Question

Howto design Tables for Navigating Hierarchical Regions with Diamond Structures

Answer 1

+2 A:

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;

The main idea is to keep two parents in two different fields, and use CONNECT BY emulation (rather than recursive CTE) functionality to preserve the order.

If you are planning to add some more hierarchy axes, it may be worth creating a separate table to store the hierarchies (with the axis field added) rather than adding the fields to the table.

Update:

Will anything in your solution below change by the above modification?

No, it will work alright.

By "axes" I mean hierarchy chains. Currently, you have two axes: political hierarchy (though hablis) and tax hierarchy (through panchayats). If you are planning to add some more axes (which is of course improbable), you may consider storing the hierarchies in another table and adding "axis" field to that table. Again, it's very improbable that you want to do this, I just mentioned this possibility for the other readers who may have a similar problem.

Also, would you recommend that I create another Table like below to store the 7 properties of the Habitats ? Is there a better way to store such info ?

Yes, keeping them in a separate table is a good idea.

Quassnoi 2010-07-11 19:45:42

Quassnoi : Thank you. I will try it out and let you know.Axes and Fields : Not sure I understand correctly. I have appended a clarification to my question above called Quassnoi #2. Pls let me know if that is what you mean ?thank you

anjanb 2010-07-12 04:41:00

@Quassnoi : I tried to award you the bounty but stackoverflow responded saying that I have 9 more hours before I can do the same.

anjanb 2010-07-12 07:45:41

@Quassnoi : Thank you. I have accepted your answer. 2 more hours to go before I grant you the bounty.<br>I have asked another clarification of you at Quassnoi #3 above. Can you pls take a look ? Thank you.

anjanb 2010-07-12 14:45:35

ansaurus

tags:

views:

answers:

Howto design Tables for Navigating Hierarchical Regions with Diamond Structures

ResultSet

related questions