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.