On 5/24/07, Mike Friedman <[EMAIL PROTECTED]> 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
_______________________________________________
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/[email protected]/
