Re: Tables Referencing themselves As Foreign Keys

Arjen van der Meijden wrote:

> Mike Mascari wrote:
>
>> 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

The UNIQUE constraint prevents that.

> parents to be their childrens child, etc.

Nothing in the single-relation design prevents that, either:

INSERT INTO categories (CatID, ParentID) VALUES (1, 1); <- Root Node
INSERT INTO categories (CatID, ParentID) VALUES (2, 1);
INSERT INTO categories (CatID, ParentID) VALUES (3, 2);

UPDATE categories SET ParentID = 3 WHERE CatID = 2;

A CHECK constraints composed of a recursive function (transitive closure
check) will be necessary in *both* designs. If you are just growing a
tree and children never change parents, in both designs, one might be
able to achieve the goal with a check constraint:

CHECK (CatID > ParentID)

> 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 :)

Not quite. See above. In addition, there's nothing, without the use of a
partial + functional index, or some other hackery, to prevent:

INSERT INTO categories (CatID, ParentID) VALUES (1, 1);
INSERT INTO categories (CatID, ParentID) VALUES (2, 2);

which may or may not be what you want. The same holds true of the
two-relation variable design as well, of course.

> If you *need* a graph, then yes, that's the most traditional way.

I'm in the Christmas spirit, so I will add some weight to your argument.
:-)

If the purpose of the two-relation variable design is to eliminate the
use of NULLs and/or the self-referencing parent, I fail to see how one
could have an ON DELETE CASCADE RI constraint to delete the descendants
of a parent which has been deleted, unless the constraint is added after
the first generation of children have been created. A minor detail the
anti-NULL adherents, and I usually include myself in that category,
haven't expounded upon...

Mike Mascari
mascarm(at)mascari(dot)com