Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?

Class::DBI and DB design

by v_thunder (Scribe)
on Jun 06, 2003 at 15:40 UTC ( #263724=perlquestion: print w/replies, xml ) Need Help??
v_thunder has asked for the wisdom of the Perl Monks concerning the following question:


I use a postgres DB for an application of mine, which I am redesigning and at the same time switching to Class::DBI (from DBI). It seems from the docs like a fairly nice package, at least to non-db-savvy people like me.

I am finding the primary key restrictions of Class::DBI rather annoying, though, and I'm wondering if I'm lacking some nugget of wisdom that will make this make sense.

I have some tables like this:

users: has uid, name, login. capabilities: has capid, name, and a default (bool). user_capabilities: has uid, capid, and a bool.

If I read the Class::DBI docs right, I need to add a "relation_id" column to the user_capabilities table, even if I'm never going to need it. Why? What was the reasoning behind that restriction?

Also, what about this case: I add another table, like:

user_logins:  has a uid, and a timestamp.

I'd add one more row each time the user logs in, and I'd query on uid to get a list of all the user's logins in the past. Would I have to add another "id" column to that table as well?

Lastly, have I picked a bad solution? I did some research before going with class::DBI, most people seemed reasonably happy with it (and also mentioned the primary key thing being annoying). I'm just wondering if there are any horror stories I should be aware about before spending a lot of time re-writing chunks of my app to work with Class::DBI.

Thanks for any help you can provide :)

Replies are listed 'Best First'.
Re: Class::DBI and DB design
by perrin (Chancellor) on Jun 06, 2003 at 15:51 UTC
    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.

      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?

        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.
Re: Class::DBI and DB design
by Ovid (Cardinal) on Jun 06, 2003 at 15:59 UTC

    This issue gets periodically brought up on the Class::DBI list, but for the most part, not too many people mind this annoyance for all of the convenience that Class::DBI provides.

    Interestingly, though, a research paper by Ron Fagin and CJ Date asserts that you can ensure 5nf (fifth normal form) in a database by being in 3nf and having simple keys (i.e., non-compound keys). Unfortunately, I don't quite understand everything about it, but seeing that the paper's ten years old and has survived rebuttals, it seems workable. As a result, I try to avoid compound keys and I never seem to have a problem (or maybe I do have problems but I don't recognize them ... who knows?).

    If any monks who are more savvy on the DB issues care to chime in (and maybe explain that paper in human terms :) ...


    New address of my CGI Course.
    Silence is Evil (feel free to copy and distribute widely - note copyright text)

Re: Class::DBI and DB design
by tunaboy (Curate) on Jun 06, 2003 at 16:27 UTC

    I am also using Class::DBI (although with MySQL) and I had to add a primary key to all my "join" tables to satisfy Class::DBI's requirements.

    This modification of the schema simply to be able to use a module bothered me at first, until a recent discussion on the Class::DBI mailing list in which Ovid made pretty much the same comment as in the post above.

    My database-fu is not very strong but I can say that I have experienced no problems as a result of this change and I have had no problems with Class::DBI.

    my $0.02

Re: Class::DBI and DB design
by tunaboy (Curate) on Jun 06, 2003 at 22:15 UTC

    I have a little more time now so I figured I might as well show how I would alter your schema to work with Class::DBI (I am not claiming this is the way you should do it, merely how I would):

    package My::User; use base 'My::ClassDBI'; __PACKAGE__->table ('user'); __PACKAGE__->columns ( All => qw( uid name login ) ); __PACKAGE__->has_many( 'capabilities', 'My::UserCapability' => 'user' +); ### package My::Capability; use base 'My::ClassDBI'; __PACKAGE__->table ('capability'); __PACKAGE__->columns ( All => qw( capid name default ) ); __PACKAGE__->has_many( 'users', 'My::UserCapability' => 'capability' ) +; ### package My::UserCapability; use base 'My::ClassDBI'; __PACKAGE__->table ('user_capability'); # note the added primary key idx __PACKAGE__->columns ( All => qw( idx user capability ) ); __PACKAGE__->has_a( 'user' => 'My::User' ); __PACKAGE__->has_a( 'capability' => 'My::Capability' );

    You could now print out all the capabilities for a user with:

    foreach ( $user->capabilities() ) { print $_->capability->name(), "\n"; }
    or all the users with a certain capability:
    foreach ( $capability->users() ) { print $_->user->name(), "\n"; }
    The users and capabilities methods might be better named as they don't really return a list of users or capabilites but rather return a list of My::UserCapability objects. But you could write a custom get_capabilities method such as:
    sub get_capabilities { my ( $self ) = @_; my @capabilities = $self->capabilities(); return map { $_->capability->name() } @capabilities; }

    It is a bit of work getting around the single primary key limitation of Class::DBI but IMHO well worth it.

    NOTE: I think all the code is correct as given but I could be drastically wrong as it is late on a Friday afternoon.

Re: Class::DBI and DB design
by edoc (Chaplain) on Jun 06, 2003 at 17:17 UTC

    apologies if I'm way off base here,

    Update: Yup, way off base.. nothing to see here people.. move along..

    If I read the Class::DBI docs right, I need to add a "relation_id" column to the user_capabilities table, even if I'm never going to need it. Why? What was the reasoning behind that restriction?

    I don't think you do have to. You just want to make 'uid' the primary key for the table. Are you after something like this maybe?

    package My::Class::DBI::User; use base 'Class::DBI'; __PACKAGE__->table('user'); __PACKAGE__->columns( All => qw/ uid name login / ); __PACKAGE__->has_many('logins', My::Class::DBI::UserLogin => 'uid'); package My::Class::DBI::UserLogin; use base 'Class::DBI'; __PACKAGE__->table('user_login'); __PACKAGE__->columns( All => qw/ uid timestamp / ); 1;



      I was under the impression that a primary key was equivalent to "unique not null", and indexed. In the user_login case the uid would not be unique, since there would be multiple rows with the same uid, one for each login from the same user.

      Am I wrong here? Can I really use uid as the primary key in that example?

        ahhh.. doh.. thought I must've be missin something.. maybe I didn't need that extra beer..