[Dbix-class] table referencing itself

[Darren Duncan]

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