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