CREATE TABLE `user` (
  `user_id` int(11) NOT NULL auto_increment,
  `firstname` varchar(50) NOT NULL default '',
  `lastname` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`user_id`)
) TYPE=MyISAM;
insert  into `user` values 
(1, 'John', 'Day'), 
(2, 'Jacqui', 'Burke'), 
(3, 'Brenda', 'Day'), 
(4, 'Cassie', 'Cat'), 
(5, 'Steven', 'Burke'), 
(6, '', '');

CREATE TABLE `role` (
  `role_id` int(11) NOT NULL auto_increment,
  `rolename` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`role_id`)
) TYPE=MyISAM;
insert  into role values 
(1, 'Typist'), 
(2, 'Programmer'), 
(3, 'Cleaner');

CREATE TABLE `user_role` (
  `user_id` int(11) NOT NULL default '0',
  `role_id` int(11) NOT NULL default '0'
) TYPE=MyISAM;
insert  into user_role values 
(1, 1), 
(1, 2), 
(1, 3), 
(2, 2), 
(3, 1), 
(3, 2), 
(0, 0);

##</code><code>##

package Test::DBI;
use base 'Class::DBI';

Test::DBI->set_db('Main', 'DBI:mysql:appsys2', '****', '****');

package TestDBI::userrole;
use base 'Test::DBI';
__PACKAGE__->table('user_role');
__PACKAGE__->columns(All => qw(user_id role_id));
__PACKAGE__->has_a( role_id => TestDBI::role );
__PACKAGE__->has_a( user_id => TestDBI::user );

package TestDBI::user;
use base 'Test::DBI';
__PACKAGE__->table('user');
__PACKAGE__->columns(All => qw(user_id firstname lastname));

__PACKAGE__->has_many(haveroles => ['TestDBI::userrole' => 'role_id'] );

package TestDBI::role;
use base 'Test::DBI';
__PACKAGE__->table('role');
__PACKAGE__->columns(All => qw(role_id rolename));

1;

##</code><code>##

#!/usr/local/bin/perl;

use warnings;
use strict;

use Data::Dumper;
use Test::DBI;

my $testuser = TestDBI::user->retrieve( 1 );
print "user with id 1 is: ". $testuser->firstname ."\n";
print "and has roles:\n";
my @roles = $testuser->haveroles;

print Dumper( \@roles );
#foreach (@roles) {
#	print "role: " . $_->id . " and " . $_->rolename . "\n";
#}
exit;

##</code><code>##

user with id 1 is: John
and has roles:
 is not a column of TestDBI::userrole at test.pl line 12
[root@posiedon3 www]#