From: | Mike Mascari <mascarm(at)mascari(dot)com> |
---|---|
To: | Arjen van der Meijden <acmmailing(at)vulcanus(dot)its(dot)tudelft(dot)nl> |
Cc: | "Tony (Unihost)" <tony(at)unihost(dot)net>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Tables Referencing themselves As Foreign Keys |
Date: | 2003-12-22 14:48:21 |
Message-ID: | 3FE70435.3030407@mascari.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Lucas Lain | 2003-12-22 14:51:58 | restoring database |
Previous Message | Oleg Bartunov | 2003-12-22 13:55:47 | Re: questions about tsearch2 (for czech language) |