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.

As someone who has designed many product databases, let me give you a piece of advice: do not tie your database IDs to anything in the real world, and do not try to make them human friendly. You will hate yourself for it later when you discover that some new requirement breaks the scheme and renders IDs that look like they have meaning into something meaningless. For example, you will want to sell 5-gallon plastic containers of feul too, and those will end up with the ID 1000-72, which means nothing.

If you want to help people enter this stuff, the easiest approach is to let them pick from a list, possibly by selecting the baseitem first and then the item. If you are really stuck with manual command-line entry, just add "sku" or something as a unique key in your items table, and make it a well-chosen string like "5-gal-fuel".

The way to express the relatiosnship you're describing (one product type to many products) in a RDBMS is what I desccribed. What you have set up is what you would do if you wanted to make it possible for one item to be a part of multiple baseitems, i.e. this drum of feul is sold as part of the feul baseitem and also as part of the drums baseitem. That isn't what you want here. This may seem like an insignificant distinction, but designing your database to describe your data rules correctly really is very important. As a bonus, Class::DBI's built-in relationship methods would work for you then.

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]