Skip site navigation (1) Skip section navigation (2)

design & available tricks: traversing heterogeneous tree (table-level + linked list)

From: Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>
To: pgsql-general(at)postgresql(dot)org
Subject: design & available tricks: traversing heterogeneous tree (table-level + linked list)
Date: 2008-12-22 13:52:44
Message-ID: 20081222145244.7780e4a6@dawn.webthatworks.it (view raw or flat)
Thread:
Lists: pgsql-general
I've such a structure:

create table catalog_fam (
  famid int primary key,
  name varchar(255),
  action smallint
);
create table catalog_macro (
  macroid int primary key,
  famid int references catalog_fam (famid),
  name varchar(255),
  action smallint
);
create table catalog_cat (
  catid int primary key,
  macroid int references catalog_macro (macroid),
  name varchar(255),
  action smallint
);
create table catalog_group (
  groupid int primary key,
  catid int references catalog_cat (catid),
  groupup references catalog_group (groupid),
  action smallint
);
create table catalog_categoryitem(
  itemid int references catalog_item (itemid),
  famid int references catalog_fam (famid),
  macroid int references catalog_macro (macroid),
  catid int references catalog_cat (catid),
  groupid references catalog_group (groupid),
  action smallint
);

where action is (update, delete, insert).

And I've to build up functions that eg.
- tell me what are the categories (where category may stand for
famid, macroid, catid, groupid) have the same parent
- tell me which are the items that belong to the same category
- tell me which are the categories that belong to the same level of
a parent...

And I'd like to:
- offer a uniform interface to the client (php) through plpsql
functions
- avoid to get crazy writing and maintaining such functions.

I could even implement a completely different schema to solve the
above, but then I'll have to face the problem of importing the data.

I've just a constraint if I'll have to change the schema: there is
already some logic depending on:

create table catalog_categoryitem(
  itemid int references catalog_item (itemid),
  famid int references catalog_fam (famid),
  -- ... the rest doesn't matter
);

and

catalog_fam

but refactoring this could be worth since:
a) there is no item that belong to more than one fam
b) famid is frequently accessed when items are and I could avoid a
join

Still there may be some interest in having fast access/grouping and
traversing of stuff with same catid and macroid in the future.
So a nested set (mptt) to represent the hierarchy may not be optimal
and wring a safe and *fast* import function may not be trivial.

I was wondering if there is some cool feature or cool contrib
(8.3) that could make the choice much easier to take or just some
suggestion.

thanks

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


pgsql-general by date

Next:From: Jonah H. HarrisDate: 2008-12-22 14:04:34
Subject: Re: How are locks managed in PG?
Previous:From: Alvaro HerreraDate: 2008-12-22 13:22:08
Subject: Re: How are locks managed in PG?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group