Re: Tables Referencing themselves As Foreign Keys

This is a fine approach. The FK will work fine. You'll probably want CatID
to be NOT NULL and CatParent to allow nulls. Having a Null parent
indicating root is easier for traversals.

Common other features to add include:
a "path" column that is maintaned by insert/update triggers. Quite
easy to do and very helpful.
Once you have that you can do a simple test for circularity also on
insert/update, like:
IF "path" ~ '(^|\\.)' || "CatID"::text || '(\\.|$)' THEN
RAISE EXCEPTION ''circular hierarchy detected...'';
END IF;
There's also a short-cut way to do this since you use Serial for the CatIDs.
Just do a CHECK (CatParent < CatID) -- of course it makes an assumption
about the CatIDs really come in serially...

== Ezra Epstein

""Tony (Unihost)"" <tony(at)unihost(dot)net> wrote in message
news:3FE6CE27(dot)5080102(at)unihost(dot)net(dot)(dot)(dot)
> Hi,
>
> I'm still new to this so if I'm sounding dumb or my premise is flawed
> please forgive me. I have a DB design which contains a table which has
> categories, each category has a parent category, and is recursed until
> the top category is reached, in order to create breadcrumbs. Is there
> any problem with using foreign keys to reference the same table? So a
> when category is added the CatParent MUST be present as a CatID
>
> CatID - Serial
> CatParent - int4 - References CatID
> CatName - Text
>
> Am I likeley to come unstuck with this?
>
> Cheers
>
> T.
>
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>