Proper nesting of hierarchical objects

Поиск
Список
Период
Сортировка
Искать
От
Michael Glaesemann
Тема
Proper nesting of hierarchical objects
Дата
Msg-id
82162AB2-3172-11D9-BC33-000A95C88220@myrealbox.com
Список
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 по дате отправления
От: Morten K. Poulsen
Дата:
От: Masse Jacques
Дата:
FAQ