hangareighteen has asked for the wisdom of the Perl Monks concerning the following question:
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 );
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) );
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) );
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...
|
---|
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 | |
by hangareighteen (Monk) on May 21, 2004 at 20:20 UTC | |
by perrin (Chancellor) on May 21, 2004 at 20:53 UTC | |
by hangareighteen (Monk) on May 21, 2004 at 21:53 UTC | |
by perrin (Chancellor) on May 21, 2004 at 22:33 UTC | |
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 |