Class::DBI and - possibly - complex data structures

Evil Attraction has asked for the wisdom of the Perl Monks concerning the following question:

I've been recommended Class::DBI by so many now, that I fell down on my kneed and tried it out properly. I've been fiddling with it previously too, but didn't like the outcome performance-wise. Anyway - I'm ready to give a new try.

My little test project deserves some help, though, as I'm having problems dealing with "complex" data structures. They aren't that complex, really, but putting them into a Class::DBI context seems more than trivial.

I have these tables:

person

person_id    mediumint unsigned auto_increment primary key,
sex          char(1) default '?' not null,
modified     date not null
[download]

name

name_id      mediumint unsigned auto_increment primary key,
name         varchar(255) not null
[download]

person_name

person       mediumint unsigned not null, /* references 'person' */
firstname    mediumint unsigned not null, /* references 'name' */
lastname     mediumint unsigned not null  /* references 'name' */
[download]

As you can see, one Person can have more than one name. One extra question; it seems like Class::DBI forces you to name references without any ending '_id' if you want some nifty named methods in your classes?

Anyways - here are my classes;

Person.pm

package Person;
use strict;
use warnings;
use base 'My::DBI';

Person->table( 'person' );
Person->columns( All => qw(person_id sex modified) );
Person->has_many( names => 'PersonName' );

1;
[download]

Name.pm

package Name;
use strict;
use warnings;
use base 'My::DBI';

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

1;
[download]

PersonName

package PersonName;
use strict;
use warnings;
use base 'My::DBI';

PersonName->table( 'person_name' );
PersonName->columns( All => qw(person firstname name) );
PersonName->has_a( firstname => 'Name' );
PersonName->has_a( lastname => 'Name' );
[download]

My script might look something like this:

#!/usr/bin/perl
#
use strict;
use warnings;

use Person;

my $Person = Person->retrieve( 1 );

my $names = $Person->names();
while ( my $Name = $names->next() ) {
    print $Name->lastname()->name() . ', ' . $Name->firstname()->name(
+) . "\n";
}
[download]

It works almost, except that even though the person has 2 different names, only one of them gets listed (twice).

Any ideas?

Comment on Class::DBI and - possibly - complex data structures Select or Download Code

Replies are listed 'Best First'.
Re: Class::DBI and - possibly - complex data structures by Ovid (Cardinal) on Sep 05, 2003 at 19:58 UTC
Your PersonName class has a bug: `PersonName->columns( All => qw(person firstname name) );` Since you don't have a 'name' field defined, that should be 'lastname'. Why does each person have more than one name, though? If it's the case that one person can be "John Smith" and "Jane Doe", it's appropriate to have names in a separate table. If, however, you mean that one person has a first and a last name, then you probably want to move those into your person table. If that's the case, you can collapse this down to one table. `package Person; use strict; use warnings; use base 'My::DBI'; __PACKAGE__->table( 'person' ); __PACKAGE__->columns( All => qw(person_id sex modified first_name last +_name) );` [download] If, however, a person can really have more than one name (aliases), then two tables can suffice. `package Person; use strict; use warnings; use base 'My::DBI'; Person->table( 'person' ); Person->columns( All => qw(person_id sex modified) ); Person->has_many( names => 'PersonName' ); package PersonName; use strict; use warnings; use base 'My::DBI'; PersonName->table( 'person_name' ); PersonName->columns( All => qw(person_name_id person_id first_name las +t_name) );` [download] And in your actual code: `my $person = Person->retrieve( $person_id ); while ( my $name = $person->names ) { printf "%s, %s\n", $name->last_name, $name->first_name; }` [download] It also looks like you could use a brush-up on database design. Microsoft has a decent article on Database normalization basics. It's fairly concise and easy to understand -- as much as the topic is easy to understand, that is. Cheers, Ovid New address of my CGI Course.	[reply] [d/l] [select]
Re: Re: Class::DBI and - possibly - complex data structures by Evil Attraction (Novice) on Sep 06, 2003 at 00:06 UTC
The reason I've modelled the database like this, is to save space. I've created a utility which converts genealogical data from the Gedcom format to a MySQL-driven database. When dealing with genealogy it's fairly common to have individuals with more than one name; either you're not sure about the person's name, and want to refer to more than one names, or the person have a marriage name etc. That `name` vs. `lastname` "bug" was a typo, and it doesn't affect the real problem here. For simplicity, I could have had a `name` table looking like this: `person_id mediumint unsigned not null, /* references 'person' / firstname varchar(255) not null, lastname varchar(255) not null` [download] ...but that's not really what I want. As I said, I want to save disk space, and by not duplicating data in the database I save a lot of space in this example. However, after some reading I discovered `Class::DBI::Join` which seems* to do what I'm after. The only problem is: I still can't get it to work. Any examples on using that class would have been great, as the example doesn't do it for me. Thanks again!	[reply] [d/l]
Re: Class::DBI and - possibly - complex data structures by perrin (Chancellor) on Sep 05, 2003 at 20:29 UTC
I agree with Ovid -- this is a very strange schema design. However, assuming you did it this way just to see if Class::DBI can handle something strange, there is a much more elegant way to deal with this. You are trying to use a linking table here, which is supported by Class::DBI using has_many (see the style refs example in the docs) but it's confusing. Another approach is to write the query directly in the names class, like this: `package Name; ... __PACKAGE__->set_sql(by_person => q/ SELECT n.name FROM name n, person_name pn WHERE (n.name_id = pn.firstname OR n.name_id = pn.lastname) AND pn.person = ? /); package Person; ... sub names { my $self = shift; Name->search_by_person($self); }` [download] it seems like Class::DBI forces you to name references without any ending '_id' if you want some nifty named methods in your classes? No, you can name things whatever you want. Look at the section on changing accessor names in the docs.	[reply] [d/l]
(OT)Re: Class::DBI and - possibly - complex data structures by LameNerd (Hermit) on Sep 05, 2003 at 20:39 UTC
Just curious about the data modeling of your tables ... Why not define table person_name like so ... `person mediumint unsigned not null, /* references 'person' / name mediumint unsigned not null, / references 'name' / seq mediumint unsigned not null / determines firstname middlenam +e lastname */` [download]	[reply] [d/l]