Self-referencing tables

I found what I was looking for in this thread:

http://groups.kasei.com/mail/arc/cdbi-talk/2003-11/msg00128.html

Basically, you have a table that references itself:

CREATE TABLE employee (  id int(10) unsigned not null auto_increment primary key,   name varchar(255) not null,   manager int(10) unsigned -- points back to id in another record in this table );

In your class you would define a has_a like so:

package Employee; ... __PACKAGE__->has_a( manager => __PACKAGE__ );

And then use it like so:

if ( my $manager = Employee->retrieve(42)->manager ) { print $manager->name, "\n"; }

You can also add this:

__PACKAGE__->has_many( underlings => __PACKAGE__ );

Then you can find all the employees that are managed by a particular employee:

for my $underling ( Employee->retrieve(42)->underlings ) { print $underling->name, "\n"; }

Note: the cascading delete mechanism then works the same as in ordinary relations: deleting an employee also deletes all his underlings! This may not be what you want.