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