[Dbix-class] Re: doing a search

[A. Pagaltzis]

* John Napiorkowski <jjn1056 at yahoo.com> [2007-01-21 00:00]:
> * A. Pagaltzis <pagaltzis at gmx.de> [2007-01-20 22:45]:
> > Am I the only one who uses ${desc}_${othertable} as the name
> > for FK columns? Eg. a table with weblog posts/comments in a
> > system I design might look like this:
> > 
> >     CREATE TABLE entry (
> >         id          INTEGER PRIMARY KEY,
> >         author_user INTEGER REFERENCES user(id),
> >         body        TEXT
> >     );
> 
> I usually make the FK column the same as the column name of the
> referencing table. Then again, I've often been accused of
> having too little imagination :) 

That’s how I started, but then sometimes I had several FKs to the
same table, such as a trial’s director, secretary and the
record’s editor, all referencing the user table. So I got into
the habit of prefixing the foreign table name with an additional
descriptive label, which turned out to have many small benefits.
(See below.)


* Bill Moseley <moseley at hank.org> [2007-01-21 00:00]:
> How is that helpful to you?

It’s a habit from manual SQL writing to make it immediately
obvious when a join is operating on incompatible data types.
The name clearly calls out *that* the column is an FK and *where
to*, so that it’s impossible to write bad joins or subselects
simply because the naming scheme makes any such error instantly
obvious: correct joins always involve the id column from one
table and a column ending in its name from the other table.

It’s the only consistent naming scheme I found for doing this
that covers the scenario with multiple FKs to the same table.

> Does that let you know it's a column you can call other methods
> on?
> 
>     $entry->author      -- not sure if can call other methods
>     $entry->author_user -- clear that can call "user" class methods
> 
> Or some other reason?

I hadn’t thought of that actually! I like it though. A good
reason to keep doing it even with an ORM.

> I like brief names.  I'm working with a MySQL database now
> called "webcast" where tables are prefixed by the database
> name, and then columns are prefixed by their table name.
> Unfortunately, there's a table "webcast" (hence table
> "webcastWebcast") so a column looks like:
> 
>     webcast.webcastWebcast.webcastTimestamp
> 
> Not that I know exactly *what* that time represents.  That
> database is part of a PHP application where it seems that
> CamelCase is common.

Ugh, yeah, not my intent at all. This scheme is in fact carefully
chosen to avoid redundant information. F.ex. most people name
their FK columns something like `${othertable}_id` – but the only
purpose the `_id` bit serves is to call out the column as an FK.
It doesn’t have any descriptory value of its own. That’s not so
bad as long as you don’t have multiple FKs to the same table in
one table, but if you need to, those `_id` bits become really
obvious clutter.

So that’s how I got to that naming scheme; to cover that case
with minimum redundancy. And then I found that it is actually
really nice for the purposes of self-documentation if you keep
this scheme even when there’s only a single FK to that particular
table, as in the `entry` table above. In highly normalised
schemata you often have tables which are used quite widely, and
it’s nice to use the column name to document not only *that* one
table is related to another, but also *how*, eg. not just
“entries are related to users” but more precisely “entries have
an author relationship with users.”

Admittetly it can still be a bit of clutter, but I react by
keeping my table names short, which is beneficial in more ways
than just this one anyway.

Overall, having put it through its paces for a while, I’ve found
it very sane and sensible after all the previous naming schemes
I tried.

Regards,
-- 
Aristotle Pagaltzis // <http://plasmasturm.org/>