Re: Class::DBI and DB design

[perrin]

First of all, Class::DBI will be getting support for composite keys soon. Join the mailing list if you're interested. SPOPS already has support for composite keys.

Keep in mind that when Class::DBI's built in relationships fail you, you can always add a method to it that runs an arbitrary SQL query and returns one or more objects. That might be the best thing to do in your case. There will always be things that an O/R mapping tool doesn't handle well, and that's when the ability to add hand-coded SQL transparently becomes important.

[v_thunder]

Let me see if I understand this correctly. If I have a table like user_capabilities in my original question, could I use something like this?

package My::UserCapabilities;
use base 'My::ClassDBI';
__PACKAGE__->table ("user_capabilities");
__PACKAGE__->columns (All => (""));

And then define methods for each of the columns myself? i.e., does defining methods myself provide a way around the single primary key restriction, or did you just mention it as a way of providing extra functionality later?

Now that I think about it, maybe what you're suggesting is that I not define a class for the non-primary-key tables, and just provide a hand-coded method in a different class (e.g., "My::User") to get to that information. Is that what you meant?

[perrin]

I meant the latter. Something like the example shown in the Class::DBI docs here. This example shows how to do a query for objects of the current class, but you can use it to get objects of another class as well. Just call sth_to_objects() on the other class instead.