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

I missed the original post, but I think you both might want to do some
googling on nested set.

Robert Treat

On Tue, 2004-11-09 at 11:40, Garris, Nicole wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org