Arjen van der Meijden wrote:
> Tony,
>
> That'll work, but you have to mind the first row/toprow you insert.
> Will it have no parent (make the field nullable) or will it be its own
> parent (you'll have to test whether that works, I don't know, foreign
> keys are deferrable, so it should be possible if you specify that).
A more traditional way to have hierarchical relationships in the
relational model is to have two relations (and not use NULLs):
CREATE TABLE categories (
CatID bigint PRIMARY KEY NOT NULL,
CatName text NOT NULL
);
CREATE TABLE category_parents (
CatID bigint UNIQUE NOT NULL REFERENCES categories(CatID),
ParentID bigint NOT NULL REFERENCES categories(CatID)
CHECK (CatID <> ParentID)
);
The top category would be the only tuple in categories that did not
exist in category_parents.
HTH,
Mike Mascari
mascarm@mascari.com
