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:[EMAIL PROTECTED] > Sent: Monday, November 08, 2004 2:40 AM > To: '[EMAIL PROTECTED]' 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 ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
