Proper nesting of hierarchical objects
От | Michael Glaesemann |
---|---|
Тема | Proper nesting of hierarchical objects |
Дата | |
Msg-id | 82162AB2-3172-11D9-BC33-000A95C88220@myrealbox.com обсуждение исходный текст |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: