Quote:> Hello all,
Quote:> 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
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.
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
> 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
> 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
> 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
Quote:> 2) When the same column appears on more than one table, it should
> 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.
Quote:> 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 ...
Quote:> 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.
Quote:> What other arguments can be made for or against these column
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.