Perl snippet for the weekend: getting “normalized” column names to act like proper object identifiers with DBIx::Class::Schema::Loader

I understand why some give foreign key columns names like user_id or order_id. I don’t like it though and I think any duplication of information in code is bad. Postfixing an FK column with _id is like saying something like, “This is my dog mammal.” The information is implicit. An FK is an id. Labeling it so is annoying and redundant. (So are plural table names. Do not name your table “users” please. Everyone—everyone—knows that a table named “user” contains users.)

Still, it’s done—it’s even considered a best practice by some folks who spend more time reading white papers than writing and fixing code—and often by someone other than the dev. It means in auto-generated DBIC schemata that you now get an order’s user object with the accessor user_id. How lame is this?

my $order = $schema->resultset("order")->find($order_id);
my $user = $order->user_id;

Each user_id is not an id in the context of our code, it’s a user object/record. We should be able to ask for it that way.

Fortunately this is Perl and the designers of DBIC are quite sane so this is easy to fix if you know how. Finding out how… not so easy; for me. Easy for you. You’re already done–

inflect_singular => sub { $_[0] =~ /\A(.+?)(_id)?\z/; $1 },

Pertinent docs to help you figure out where to put that snippet include DBIx::Class::Schema::Loader and DBIx::Class::Schema::Loader::Base.

digg stumbleupon del.icio.us reddit Fark Technorati Faves

Discussion

Comments


Adam

Re: Perl snippet for the weekend: getting “normalized” column names to act like proper object identifiers with …

I like using *_id for the field and '*' for the accessor.

So for instance, if I have a car table and a make table, calling $car->make_id will give me the id (and allow me to set it) of the related make-entry, while calling $car->make will give me an object representing the make-entry.

I don't use ::Loader, though.

By Adam on 14 June 2009 · 03:38
comment link · reply to this

Ashley

Re^2: Perl snippet for the weekend: getting “normalized” column names to act like proper object identifiers with …

I like that, it could be nice to have both. I don’t mind hopping through the hoop of an extra object access/lookup in $car->make->id and I adore using ::Loader ([re]generating the class files, not dynamically) so I probably won’t start doing it that way but it’s a nice thing to consider.

By Ashley on 14 June 2009 · 09:57
comment link · reply to this

Theory

Re: Perl snippet for the weekend: getting “normalized” column names to act like proper object identifiers with …

Perhaps I'm revealing my ignorance of DBIx::Class here, but from a relational perspective, naming a foreign key column something like user_id makes perfect sense. The reasons for this are:

* Relations are not objects. A foreign key reference to user.id is referencing the id column of the user table and nothing else.
* You could name it just user, but then you don't know what it's pointing to unless you actually look up the constraint. It could be any key column in the user table.

It is therefore good practice to identify the table name and column (or columns!) in a foreign key column name.

That said, in addition to providing a user_id() accessor, DBIx::Class should provide a user() accessor that returns the object. Sometimes all you want is the ID, so it'd be pointless to actually load all of the user data from the database. But when you do want the object, getting it should be transparently provided by the ORM.

—Theory

PS: I always used to prefer singular table names, myself, but switched to plural table names when I quit using ORMs altogether and wanted to avoid reserved words (such as "user" on PostgreSQL).

By Theory on 14 June 2009 · 13:55
comment link · reply to this

Ashley

Re^2: Perl snippet for the weekend: getting “normalized” column names to act like proper object identifiers with …

I don’t entirely disagree that it makes sense. I argue that it is redundant and redundancy is “evil.” A table’s id column might actually be named `pkid` or `guid_id` or `user_id` which I’ve seen done in two different shops. That would then, logically, lead to an FK named `user_id_id`—or even something like `users_user_id`—which is obviously wrong but consistent with the idea.

You’re right, relations aren’t objects but the _id disguises, logically, the relationship. To me. And it couldn’t really be any key in the user table because a table can only have one primary key. Though we haven’t really touched on compound keys… which is good because I wouldn’t know what to argue. :)

I have also done naming based on reserved word problems in the past. I think proper quoting, top to bottom, in the application is the right answer though. DBIC (well, SQL::Translator in particular) has had some problems doing this right in the past but it’s got much better lately.

By Ashley on 14 June 2009 · 15:18
comment link · reply to this

Matt S Trout

Re^3: Perl snippet for the weekend: getting “normalized” column names to act like proper object identifiers with …

I always use ${relname}_${f_col_name} for FK columns.

It's only redundant if you always have single column primary keys. In any database of more than a handful of tables I tend to have some composite PKs, at which point suddenly you can't just use ${relname} for the FK col because there's more than one.

At that point, the ${relname}_${f_col_name} for single col FKs as well becomes consistent rather than redundant.

By Matt S Trout on 16 June 2009 · 06:08
comment link · reply to this

Ashley

Re^4: Perl snippet for the weekend: getting “normalized” column names to act like proper object identifiers with …

I thought I blocked you, Matt, damnit!

Or, I mean, yeah… I was half-way to seeing that above when I said I wouldn’t know what to argue on multi-column keys.

All y’all may have made a convert.

By Ashley on 16 June 2009 · 17:53
comment link · reply to this

Ashley

Oh, it’s you

I thought I recognized you. I just wanted to say I really enjoyed your unit testing your DB slides. I was looking at them a week or so ago and was really impressed with both the idea and the direct/clean way you got it to work.

By Ashley on 14 June 2009 · 15:35
comment link · reply to this

Your information (required) Name*
Email*
Website

* Indicates required fields; email is used for validation and is not displayed on the site.

Your comment
Commenting on Perl snippet for the weekend: getting “normalized” column names to act like proper object identifiers with DBIx::Class::Schema::Loader
Title

Body is limited to ≈1,000 words. Paragraphs—but not line breaks—are automatically inserted. Valid XHTML is required. These are the allowed tags–

<a href=""></a> <br/> <acronym title=""></acronym> <abbr title=""></abbr> <code></code> <pre></pre> <tt></tt> <ins></ins> <del></del> <hr/> <cite></cite> <b></b> <i></i> <sup></sup> <sub></sub> <strong></strong> <em></em> <h1></h1> <h2></h2> <h3></h3> <q></q> <blockquote></blockquote>