[Dbix-class] table referencing itself

Daniel McBrearty danielmcbrearty at gmail.com
Thu Dec 21 01:02:13 GMT 2006


that's neat.  In my app, it would also make sense to have the 2 FK's
in the relation db as a PK, because a 'whatever' can only belong to a
node once. I think I tried something like this at some point, but pg
wouldn't let me make a table with a PK and no other cols. I had a
"dummy" col for a bit ... :-)

But I already tried the single-table way, and it is quite clean. If
you say that eiher 'null' or self.id in the table means 'I am root of
this node', there's no ambiguity. As I have it going and it seems
simple and workable I'll stick with that, I think.

cheers.

On 12/21/06, Darren Duncan <darren at darrenduncan.net> wrote:
> At 3:51 PM -0600 12/20/06, Brandon Black wrote:
> >On 12/20/06, Daniel McBrearty <danielmcbrearty at gmail.com> wrote:
> >>Is there a way to get the id of the object being created in the
> >>statement doing the creation? I don't even know if the db (postgresql)
> >>can do that ... ?
> >
> >Of course, if you really are making a tree or graph, and
> >self-reference is just a way to say "root-level node", I'd say just
> >make it nullable and treat nulls as root-level nodes and have an
> >easier life.
>
> I have a better idea.  Split the table into 2 tables, such as
> "whatever" and "whatever_relation", where the former has details
> about "whatever" themselves and the latter has 2 foreign keys into
> the former, indicating a parent-child relationship.  Then you can
> have both full referential integrity plus no nulls, and the situation
> is generally more flexible to your needs.
>
> Moreover, you can switch between a tree and a graph based on whether
> the child-pointing field in the second table has a uniqueness
> constraint on it.  Its easy to recognize root nodes as they are
> "whatever" records which are not referred to by a child-pointing
> field of "whatever relation".
>
> -- Darren Duncan
>
> _______________________________________________
> List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
> Searchable Archive: http://www.mail-archive.com/dbix-class@lists.rawmode.org/
>


-- 
Daniel McBrearty
email : danielmcbrearty at gmail.com
www.engoi.com : the multi - language vocab trainer
BTW : 0873928131



More information about the Dbix-class mailing list