Thanks for the solution.. I really failed to consider the fact that Class::DBI allows you to add your own methods, but I was really hoping that the relationships could be setup programatically through the interface and have everything work together very nicely. The lack of multiple column foreign keys really smacks me as a shortcoming, as it's an idiom I use pretty frequently.

I'm not sure what you mean about the two-thirds many-to-many mapping. I'm writing this database for a store of sorts, as a way to help them track inventory and other items of interest. The idea of the baseitems table is that it tracks the basic item information, it's name, weight, supplier and wholesale price. The items table turns the baseitems into "packages" of items, so to speak.

So, you have an item in the baseitems table like a "#5 bolt" and all it's information, then you have a retail package in the items table like "package of 10 #5 bolts" or "package of 50 #5 bolts" that contains pricing and and unit count information.

So, each baseitem relates to several members in the item table, and each member of the item table relates back to exactly one baseitem.

In that case, the primary key of the items table should be just subid, which would be a unique auto-incrementing key. itemid should be a foreign key to the baseitems table (as you have it now), but not part of the primary key for items. Then you set up a has_many in your basitems class and a has_a in your items class.

perrin,

I had considered that, but it didn't really seem pertinent to my original post. The two reasons I discounted that solution were: 1) I didn't necessarily need the subid to be unique unto itself, which leads to 2) I wanted the keys to be small, and easy to enter for an operator, or easy to encode into a barcode.

The specific item that launched me along this idea was Fuel. The person I'm writing this for sells a lot of fuel to pit crews on local race tracks. He sells it by pump, and also in pre-prepared 5, 6.8, 15 and 20 gallon drums. I wanted a way to reference these pre prepared amounts without having to create seperate items.

So, the way I've envisioned it.. you have Fuel in the baseitems table. It has an 'id' of 1000, which is easy to remember. Then, in the items table, you'd have a row with an 'id' of 1000 to reference it's a fuel item, and then a subid of 5: this would reference the 5 gallon drum. The subid is arbitrary, and really isn't meant as a unique identifier as it is a suppliment that should be easy to remember and relates to the packaging.

So, when someone gets to a prompt for what item the customer is bying, they can just type '1000-5' or '1000-15' or '1000-68'.. which would be just a composition of the baseitem key, and the subid. It all lines up rather nicely, and it makes barcoding the products easier too.

Regardless, I provide all of this as an aside. I'll just have to code up the linking methods and the triggers and all that happy stuff by hand, or come up with a programmatic way to do it and jam it into the Class::DBI space. I appreciate your input and suggestions.

For what its worth, this is what sqlt comes up with

package DBI;

# 
# Created by SQL::Translator::Producer::ClassDBI
# Created on Fri May 21 14:05:57 2004
# 

use strict;
use base 'Class::DBI::mysql';

DBI->set_db('Main', 'dbi:mysql:_', '', '');

# -------------------------------------------------------------------
package baseitems;
use base 'DBI';
use Class::DBI::Pager;

baseitems->set_up_table('baseitems');


#
# Primary key accessor
#
sub baseitems {
    shift->id
}

sub itemss {
    return shift->items_id
}

baseitems->has_many(
    'items_id', 'items' => 'id'
);

# -------------------------------------------------------------------
package items;
use base 'DBI';
use Class::DBI::Pager;

items->set_up_table('items');


#
# Primary key accessor
#
sub items {
    shift->id
}

items->has_a(
    id => 'baseitems'
);

sub items {
    return shift->id
}

sub codess {
    return shift->codes_id
}

items->has_many(
    'codes_id', 'codes' => 'id'
);

items->has_many(
    'codes_subid', 'codes' => 'subid'
);

# -------------------------------------------------------------------
package codes;
use base 'DBI';
use Class::DBI::Pager;

codes->set_up_table('codes');


codes->has_a(
    id => 'items'
);

sub codes {
    return shift->id
}

codes->has_a(
    subid => 'items'
);

sub codes {
    return shift->subid
}

1;
[download]