[Dbix-class] Better way? Transparent child/parent relationships

[Brandon Black]

On 5/24/07, Mike Friedman <friedo at friedo.com> wrote:
> Greetings,
>
> I have a tree of categories that looks like this:
>
> CREATE TABLE `categories` (
>   `id`          int(11)       NOT NULL auto_increment,
>   `name`        varchar(255)  NOT NULL,
>   `description` text          NOT NULL,
>   PRIMARY KEY  (`id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>
> CREATE TABLE `category_map` (
>   `id`          int(11)       NOT NULL auto_increment,
>   `child`       int(11)       NOT NULL,
>   `parent`      int(11)       NOT NULL,
>   PRIMARY KEY  (`id`),
>   FOREIGN KEY  (`child`)      REFERENCES `categories` (`id`),
>   FOREIGN KEY  (`parent`)     REFERENCES `categories` (`id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>
>
>
> I found this structure a lot easier than dealing with a self-joining
> table, even though my application has no need for multiple parents

Should categories.name be UNIQUE? (And should category_map have a
UNIQUE on (child, parent))?  Otherwise you can have dupe category
names and dupe relationships.  Personally, I'd make them the PKs in
both cases as I dislike the idea of artificial autoincrement PKs when
unnecessary, but others disagree :)  Most data has naturally unique
primary keys.

In any case, if you really don't need multiple parents, you could
switch back to just having a "parent_id"  (or parent_category) field
in "categories" (you'll need to define the FK separately with an ALTER
TABLE statement probably), and use DBIx::Class::Tree::AdjacencyList
from CPAN to make it work magically in DBIC.

http://search.cpan.org/~bluefeet/DBIx-Class-Tree-0.01000/lib/DBIx/Class/Tree/AdjacencyList.pm

I suspect your m:m solution will work fine though, so long as you
don't care that the database is allowing multiple parents.

-- Brandon