Trouble with Class::DBI

[Tore Aursand]

Hi!

After being recommended this module for the past few weeks, I gave it
another look. The module itself looks very good, but I have a few
problems making it work with my "complex" data structures (which
aren't all that complex, really).

I have created a utility which converts Gedcom (genealogical) data to
a MySQL database. As it's pretty common in genealogy that one
individual can have more than one name, and I wanted to save as much
space as possible, I came up with the following tables;

individual
----------
individual_id mediumint unsigned not null,
sex char(1) default '?' not null,
modified date not null

individual_name
---------------
individual_id mediumint unsigned not null, /* references
'individual' */
given_names_id mediumint unsigned not null, /* references 'name'
*/
surname_id mediumint unsigned not null, /* references 'name'
*/
is_primary tinyint unsigned default 0 not null

name
----
name_id mediumint unsigned auto_increment primary key,
name varchar(255) not null

Getting an individual's name from this is fairly simple with an SQL
query:

SELECT gn.name as given_names,
sn.name as surname
FROM name gn,
name sn,
individual_name ina
WHERE ina.individual_id = ?
AND ina.given_names_id = gn.name_id
AND ina.surname_id = sn.name_id

Trying to represent these tables by using Class:BI seems harder,
though, and I've been forced to look into Class:BI::Join to make
this 'many-to-many' relationship work.

However - it doesn't work, and I really don't have a clue what to do.
Maybe I should try with something easier, I won't. Here are the
Class:BI classes I've made so far:

package Individual;
use strict;
use warnings;
use base 'TestDBI';

Individual->table( 'individual' );
Individual->columns( All => qw(individual_id sex modified) );


package Name;
use strict;
use warnings;
use base 'TestDBI';

Name->table( 'name' );
Name->columns( All => qw(name_id name) );


package IndividualName;
use strict;
use warnings;
use base 'TestDBI';

IndividualName->table( 'individual_name' );
IndividualName->columns( All => qw(individual_id given_names_id
surname_id) );

Can anyone please help me out there? All help is greatly appreciated!

Thanks!