FW: Proper nesting of hierarchical objects - Mailing list pgsql-general

We've actually implemented this kind of thing in a different DBMS. The
physical design consists of a single "organization" table that's something
like:

    Org_key (primary key)
    Org_type (group, company, etc.)
    Org_level (group is 1, company is 2, etc.)
    Org_parent_key (foreign key to org that encompasses this org; this is a
"recursive relationship", i.e., a foreign key to the same table)
    Org name, address, etc.

Advantages of this design:
- Its normalized, with the exception of org_level which could be derived by
counting how far down this organization is in the hierarchy
- Re-orgs are pretty easy, even promotions/demotions (level 3 becomes level
4, etc.)
- If a department moves to a different branch, its simply a matter of
changing the org_parent_key
- Easy to add another level below department (pretty common in my
organization)

My programmers hate it, but I'm not certain why. It seems easy to me to
create views that hide the recursion. There might be performance issues ...

Actually, a more flexible design has 2 tables. Table 1 is the org table:

    Org_key (primary key)
    Org_type
    Org_level
    Org name, address, etc.

Table 2 is the org relationship table (see below). The primary key is
org_key + org_parent_key.

    Org_key
    Org_parent_key
    Relationship_type

Relationship type could be R for "responsible to", B for "budgets for",
etc., if organizations can have more than one hierarchy (yes it does happen
in ours).

Sorry if I didn't completely answer your question. Also, I don't know what
an "adjacency list" is.

-----Original Message-----
From: Michael Glaesemann [mailto:grzm@myrealbox.com]
Sent: Monday, November 08, 2004 2:40 AM
To: 'pgsql-general@postgresql.org' General
Subject: [GENERAL] Proper nesting of hierarchical objects

Hi all.

I'm working (well, rather, reworking) a database schema that, in part,
models a company organizational structure. For example:

group
    company
        division
            head office
                department
                department
            branch
                department
                department
            branch
                department
                department
        division
    company
        division ...

I would like to model each node of this hierarchy as a generic "org",
as they will all share a lot of characteristics, such as each will have
an address, phone numbers, email addresses (most departments have one
email address rather than an email address for each person... but
that's not my problem :). I'd prefer to model this with nested sets
rather than an adjacency list for easy summaries, but either way, I'd
like to make sure they nest properly, so I don't end up with companies
as children of departments, for example.

What I've done so far is assign an org_type (e.g., group, company,
division) to each org. My first thought was to assign each org_type a
number, and set the numbers such that parents had numbers higher than
children (or vice versa), and enforce that with triggers. One drawback
was that I might want to use department as a catchall for anything
relatively small, so a department could be a parent of another
department. Enforcing this could be implemented by requiring the parent
org_type number to be greater than or equal to the child org_type
number, but that would also allow, for example, companies to nest in
companies, which is undesirable.

My second thought was to set up a table that mapped allowable
parent-child relations, and again, enforce immediate parent-child
relationship validity using triggers. This is beginning to feel a bit
hackish to me, so I thought I'd ask if anyone had some advice, words of
encouragement, or pointers to where I might find information on
modeling this.

Comments, suggestions, ideas, hints, criticism appreciated!

Regards,

Michael Glaesemann
grzm myrealbox com


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings