Re: [HACKERS] Tree type, how best to impliment?

Terry Mackintosh <terry@terrym.com> writes:
> CREATE TABLE categories (
>    category       char(30)    NOT NULL,
>    pcatid         char(255)   NOT NULL,
>    cat_id         char(255)   PRIMARY KEY,
>    nidsufix       int4        DEFAULT 1 NOT NULL,
>    UNIQUE ( category, pcatid ));

OK, let me get this straight ...

1. cat_id is the unique object identifier for the current table row.  You provide an index on it (via PRIMARY KEY) so
itcan be used for  fast lookup.
 
2. pcatid is a child node's back-link to its parent node.
3. nidsufix exists to allow easy generation of the next child ID for  a given node.
4. category is what?  Payload data?  It sure doesn't seem related to  the tree structure per se.

Why is "category, pcatid" unique?  This seems to constrain a parent
to have only one child per category value --- is that what you want?
If so, why not use the category code as the ID suffix, and not have to
bother with maintaining a next-ID counter?

In theory pcatid is redundant, since you could form it by stripping the
last ".xxx" section from cat_id.  It might be worth storing anyway to
speed up relational queries --- eg you'd doSELECT ... WHERE pcatid = 'something'
to find the children of a given node.  But without an index for pcatid
it's not clear that's a win.  If you make a SQL function parent_ID() to
strip the textual suffix, then a functional index on parent_ID(cat_id)
should be as fast as an indexed pcatid field for searches, and it'd save
storage.

> The only limit on both depth and width is the amount of numbers and dots
> that will fit into a char(255) field.

If you use type text instead of a fixed-width char() field, there's no
limit to the depth ... and for normal not-too-deep trees it'd save
much storage compared to a fixed-width char(255) field...

A purely stylistic suggestion: IDs of the form "1.2.3.4" might be
mistaken for IP addresses, which of course they ain't.  It might save
confusion down the road to use a different delimiter.  Not slash either
unless you want the things to look like filenames ... maybe comma or
colon?
        regards, tom lane