Class::DBI has_a() relationships with multi-value keys

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

I've been working in my spare time on an e-commerce site. I always try to dabble with something completely new in each project, and in this one, it was Class::DBI. And it has been pretty cool and useful :) I've had significant enough success with it that I decided to start using Class::DBI for my projects at my Real Job. And that is where the "fun" started.

In my side-project, every one of my child tables is connected to its parent table by a single ID value. Unfortunately, I am not so lucky with my projects at work. To elaborate, our database tracks information about taxable property, and it tracks it by year. So our master table looks something like this:

parcel_year
-------------
parcel_number
parcel_year
...
[download]

parcel_number and parcel_year makes up the primary key for this table.

Our child tables then look like this:

site_address
------------
parcel_number
parcel_year
site_id
...
[download]

where parcel_number and parcel_year is a foreign key to our parcel_year table.

I've been over the POD for Class::DBI, super-searched the archives, and Googled for a while, but I haven't been able to find (or extrapolate what I need from what I've read) how to make Class::DBI's has_a() function inflate two columns to a single object. Has anyone done this? Is it possible? If so, how is it done?

Before someone offers the suggestion, it is not at all feasible to change the structure of the tables. It's been a consideration for future versions of our product, but it is simply not feasible or an option at this time.

Thanks for whatever help you can offer! :)

MrCromeDome

Comment on Class::DBI has_a() relationships with multi-value keys Select or Download Code

Replies are listed 'Best First'.
Re: Class::DBI has_a() relationships with multi-value keys by Ovid (Cardinal) on Oct 29, 2004 at 17:02 UTC
As noted, this is not supported. As a side note, albeit an unhelpful one, it should be pointed out that databases in third normal form with tables having simple (single column) primary keys, are automatically in fifth normal form. You can read the proof, if you like. Everyone who is serious about database design should read this paper. How this applies to your work is obvious: with simple primary keys, your problem is solved. Of course, this also makes updating and deleting data a snap. It also simplifies lookup tables tremendously. I wish more DBAs knew about this. Cheers, Ovid New address of my CGI Course.	[reply]
Re^2: Class::DBI has_a() relationships with multi-value keys by MrCromeDome (Deacon) on Oct 29, 2004 at 21:15 UTC
Would have been nice for us to have read this say, oh, 8 years ago when we started all this ;) Changing our primary and foreign key columns at this point would be a ghastly thought, especially because we're just trying to throw some web-based lookups on top of our database. That being said, it is something we hope to address with the next version of our apps, whenever that may come to pass. We already have some ideas going though. Thanks for the link and the response. That was an excellent, informative read! MrCromeDome	[reply]
Re: Class::DBI has_a() relationships with multi-value keys by Arunbear (Prior) on Oct 29, 2004 at 16:02 UTC
The documentation does say that multi-column foreign keys are not supported. :(	[reply]
Re^2: Class::DBI has_a() relationships with multi-value keys by MrCromeDome (Deacon) on Oct 29, 2004 at 16:15 UTC
Dammit, didn't look hard enough! Thanks Arunbear!	[reply]
Re: Class::DBI has_a() relationships with multi-value keys by perrin (Chancellor) on Oct 29, 2004 at 19:21 UTC
Don't worry, it's easy. `sub foreign_obj { my $self = shift; my $foreign_obj = Foreign::Class->retrieve( key1 => $self->key1(), key2 => $self->key2(), ); return $foreign_obj; }` [download]	[reply] [d/l]
Re^2: Class::DBI has_a() relationships with multi-value keys by MrCromeDome (Deacon) on Oct 29, 2004 at 21:20 UTC
So do I do that in my parent class? If so, what does it look like in the child's definition? Or did I miss something? ;) MrCromeDome	[reply]
Re^3: Class::DBI has_a() relationships with multi-value keys by perrin (Chancellor) on Oct 29, 2004 at 21:33 UTC
You just do that in the parent class, i.e. the one that is holding a copy of other class' primary key in its table. If you want a has_many method in the child class that goes the other way, you could code one of those too, using a search() instead of retrieve().	[reply]
Re: Class::DBI has_a() relationships with multi-value keys by bart (Canon) on Oct 29, 2004 at 16:32 UTC
Tony Bowden, the maintainter of Class::DBI, has simply said he thinks multicolumn keys are evil. So Class::DBI won't ever support them, if it's up to him. (And why wouldn't it be.)	[reply]
Re^2: Class::DBI has_a() relationships with multi-value keys by diotalevi (Canon) on Oct 29, 2004 at 17:02 UTC
Bah. It must be time for a fork then.	[reply]
Re^3: Class::DBI has_a() relationships with multi-value keys by Ovid (Cardinal) on Oct 29, 2004 at 17:31 UTC
I struggle with that. My gut reaction is to say "see my post below" about why using single-column keys is important. However, I can totally understand why retrofitting this module onto an existing schema is a good thing. Rather than a fork, perhaps a patch or an optional module that makes Class::DBI work with multi-value keys? Cheers, Ovid New address of my CGI Course.	[reply]


Syntactic Confectionery Delight
	PerlMonks