Class::DBI multiple column primary and foreign keys

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

Hello Everyone,

I'm trying to accomplish something that seems entirely normal to me, but I can't easily find a way of accomplishing this with a standard Class::DBI.

I have three tables. The first (abbreviated) is something like:

create table baseitems (
        id integer primary key,
        data text
);
[download]

This table is the base for a bit of one-to-many inheritance for my second table:

create table items (
        id integer,
        subid integer,
        data text,
        primary key (id, subid),
        foreign key (id) references baseitems (id)
);
[download]

I believe this structure is pretty self explanitory, and represents a simple inheritance type of relationship. So far, so good. The has_a and has_many relationships for this structure were easy to come up with. The problem I have comes in when I want to make a "third-level" table that references my "second-level" table 'items'. This third table is:

create table codes (
        id integer,
        subid integer,
        data text,
        unique (id, subid, data),
        foreign key (id, subid) references items(id, subid)
);
[download]

I have no problem writing SQL to link all this stuff together in meaningful ways, and the approach seems valid... I haven't read much into database design, I sorta wing it, which may very well be my problem here.

I do have a problem explaining to Class::DBI that the foreign key that links a 'code' back to an item isn't a single column; the has_a method call only seems to accept a single column.. and I don't see any other way to establish this relationship with the interface provided.

I get the feeling that I've either overlooked pertinent information in the obvious places, or I haven't been looking in the right places at all. However, it's late for me, and I'm not too proud to ask for help. How do I make multiple column primary keys and foreign key references work with Class::DBI, or do I need to redesign my schema? Thank you...

Comment on Class::DBI multiple column primary and foreign keys Select or Download Code

Replies are listed 'Best First'.
Re: Class::DBI multiple column primary and foreign keys by perrin (Chancellor) on May 21, 2004 at 12:25 UTC
Why does items have a multi-column primary key? This allows you to have multiple baseitems that connect to the same subid, but not the same item. Is this what you intended? It looks like you've built two-thirds of a many-to-many mapping. UPDATE: By the way, the simplest way to do it is to add your own method like this: `package MyCDBI::Code; use MyCDBI::Item; sub item { my ($self, $item_id, $sub_id) = @_; my ($item) = MyCDBI::Item->search( id => $item_id, subid => $sub_id, ); return $item; }` [download]	[reply] [d/l]
Re: Re: Class::DBI multiple column primary and foreign keys by hangareighteen (Monk) on May 21, 2004 at 20:20 UTC
perrin, 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.	[reply]
Re: Re: Re: Class::DBI multiple column primary and foreign keys by perrin (Chancellor) on May 21, 2004 at 20:53 UTC
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.	[reply]
Re: Re: Re: Re: Class::DBI multiple column primary and foreign keys by hangareighteen (Monk) on May 21, 2004 at 21:53 UTC
Re: Re: Re: Re: Re: Class::DBI multiple column primary and foreign keys by perrin (Chancellor) on May 21, 2004 at 22:33 UTC
Re: Re: Re: Re: Class::DBI multiple column primary and foreign keys by Anonymous Monk on May 21, 2004 at 21:18 UTC
Re: Class::DBI multiple column primary and foreign keys by monkey_boy (Priest) on May 21, 2004 at 12:24 UTC
From the documentation , i dont think Class::DBI supports multi-column foriegn keys. I should really do something about this apathy ... but i just cant be bothered	[reply]


Perl Monk, Perl Meditation
	PerlMonks