Re: Tables Referencing themselves As Foreign Keys

Mike Mascari wrote:

> 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.

What you're modelling here is a general graph, not a tree.
This model allows to have multiple parents for children, parents to be
their childrens child, etc.

The singletable model is just a tree, nothing more. If you want the
above model to resemble a tree, you'd make sure that a tuple cannot be
the child of any of its children and a child can have only one parent.
And that would force you to create triggers, while the other model just
enforces that due to its structure :)
If you *need* a graph, then yes, that's the most traditional way.

Best regards,

Arjen