Re: Naming Convention for Columns

Dave Mullen <dave.mullen_at_emjay.com> wrote in article <6dnbes$6r5_at_newsops.execpc.com>...
> Hello all,

Howdy doo!

> We are setting up new database naming conventions, and I'd like to
> hear other people's thoughts on what they consider to be important
> considerations.

First off, I hate naming conventions altogether. Most important: do one thing, and do it consistently. But don't have heartburn if someone doesn't quite come up to snuff.

We had three different spellings of "invoice number":

   invc_nbr, invc_no, invc_num.

Ouch.

The problem in writing code for this mess was remembering which table had which spelling, particularly in joins. You compile and test something three times before you get it right. We fixed the problem before it got too far.

But I still hate naming conventions, because there's always going to be the one situation where you're going to be the only person on the planet who cares how something is named: you, that is, and the anal-retentive co-worker or code-auditor who's only joy in life is pointing out your failure to "follow the rules."

One rule you might want to consider, and the reason for it: name the columns with long, self-descriptive, correctly spelled words. Ie, instead of "invc_nbr", it should be "InvoiceNumber". Or, perhaps, "Invoice_Number". Again, do it consistently: that way, you don't have to *remember* whether or not an underline is in the word: it either always is or always isn't.

In retrospect, I really wish we'd done something like this. Too late now.

The reason is simple: we're going to be converting most of our code from running on the sparc server to running on the PC client. We'll be using Delphi. Guess what? You can place a "matrix" of query result sets right on the screen, and the default is to use the *actual* *column* *names*!!

Well, imagine the column names are "invc_no." Real easy for a user to figure out, yes? No? No. So, you have to add extra work to change the column names on the screen. Work that could have been saved if the column name was "Invoice_Number".

> In particular, I'd like to know how people feel about prefixing the
> names of columns in a relational data base with a short prefix
> representing the table to which the column belongs. (Foreign keys
> would retain the prefix of the foreign table, i.e., the table being
> referred to. This would also be true for denormalized, redundant
 data:
> the redundant columns would carry the prefix of the "owner"/master
> table.) The lead developer for this project is strongly in favor of
> using such prefixes; they were used here in the past in the DBAM data
> base being replaced. He likes not having to specify the name of the
> table when he mentions the column in code, and also feels that the
> prefix helps remind the developer/user of which table they're working
> with.
>
> I prefer not to use such prefixes. The reasons I have given are:
>
> 1) It's redundant. If a column exists on more than one table and the
> reference is ambiguous, use "table.column". It also actually makes
 the
> column name longer than necessary.

Agreed. 100%. Your lead developer must have come fresh out of a COBOL/ISAM shop. Or worse. Get with it, people, it's almost the 21st century for pete's sake!! We don't flip switches in binary on the front panel anymore! I haven't used my hex/decimal calculator in *years*!!

> 2) When the same column appears on more than one table, it should
 have
> the same name. This is clearer to users (both end users and
> developers), and many query-building tools will infer relationships
> based on column names which match, which can be very helpful.

Agreed. 100%.

> 3) Views should not have to rename all the underlying columns, which
> they would need to in order to adhere to the standard.

Only if your lead developer is anal-retentive ...

> 4) Having columns on one table carry the prefix of another table is
> confusing (except perhaps in the case of foreign keys).

Foreign keys is the only place to prefix a table name, and even there, it needs to *make* *sense*.

For example, you have a company table; keyed by "alias" or "code" or what-have-you. Referenced, as you've mentioned, as "company.code". Then you have an invoice table, and you need to store both the buying company and the selling company. (Your company might be one of several subsidiaries, all kept in the same database.)

Do you name the column, "buyer_company_code" and "seller_company_code"? Not in my shop you don't! "buy_company" and "sell_company", thank you very much. Only an idiot will have trouble figuring out the dual join to the company table.

> What other arguments can be made for or against these column
prefixes?

The names don't need to be system-wide unique, which is the only possible reason for prepending any sort of code at all. Long time ago, I worked in a real-time environment where each running task had to have a unique name: Ok, in that case, maybe you need a rigid naming convention. But I doubt if anyone is still doing a whole lot of development work on PDP-11's.

Nowadays, memory is cheap, and programmer time is expensive. The less time it takes for someone to *figure* *out* what's going on, the better. Rigid naming conventions that require a person to learn a new "language" before becoming useful are counter-productive.

Just my two cents worth.

A. Received on Fri Mar 06 1998 - 00:00:00 CST