[Gnumed-devel] Best way to implement organization heirarchy

[sjtan]

What's the preferred way for org heirarchy?
- e.g.
1. for no change to current tables,  use path names in description e.g.
box hill/cardiology for
cardiology org of type dept with parent org of box hill with type hospital.
   Disadvantage - the separator character needs to be escaped in user
input.
                           - parsing code

or 2.   one parent - child  - add a field id_parent_org to org

or 3.  separate link table - id_parent_org , id_child_org

create table lnk_org_org( id_child integer references org, id_parent
integer references org check ( id_child <> id_parent), primary
key(id_child, id_parent) );

executes ok,


gnumed=# \d lnk_org_org
  Table "public.lnk_org_org"
 Column   |  Type   | Modifiers
-----------+---------+-----------
id_child  | integer | not null
id_parent | integer | not null
Indexes: lnk_org_org_pkey primary key btree (id_child, id_parent)
Check constraints: "lnk_org_org_id_parent" (id_child <> id_parent)
Foreign Key constraints: $1 FOREIGN KEY (id_child) REFERENCES org(id) ON
UPDATE NO ACTION ON DELETE NO ACTION,
                        $2 FOREIGN KEY (id_parent) REFERENCES org(id)
ON UPDATE NO ACTION ON DELETE NO ACTION

What about option 3? Can this be added in to the demographics tables?

- problems: No way to check for a cycle in sql constraints?