Re: Naming Convention for Columns

I do not consider the prefixes redundant. They make the joins for the foreign keys self documenting which is a very great asset for a database. It also makes it easier to read a SELECT statement in that you *know* which table columns *without* table names come from.

Finally...

We have several examples where the columns 'addr_1' (or whatever you use for first address line) will by it's nature occur in several tables. Again prefixes help reduce the amount of typing when using several of them in a SELECT statment.

In article <6dsrbk$52f$1_at_lal.interserv.com>, Louis Cohen <louiscoh_at_sprynet.com> writes
>I have done this both ways, and I feel strongly about it:
>
>Column prefixes are just stupid:
>
>- All good code should always use a table alias in every reference to any
>column, so the column prefix is not needed:
>
> select ai.invoice_number, pv.vendor_name
> from ap_invoices ai, po_vendors pv
> where ai.pv_vendor_id = pv.vendor_id
> etc.
>(Define standard table aliases in a CASE tool)
>
>- It's a lot of extra typing for no reason; column prefixes contribute
>nothing to maintainability
>
>- FK columns should include the alias of the table that the column points to
>(see above)
>
>Regards
>
>Louis Cohen
>Dave Mullen wrote in message <6dnbes$6r5_at_newsops.execpc.com>...
>>Hello all,
>>
>>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.
>>
>>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.
>>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.
>>3) Views should not have to rename all the underlying columns, which
>>they would need to in order to adhere to the standard.
>>4) Having columns on one table carry the prefix of another table is
>>confusing (except perhaps in the case of foreign keys).
>>
>>What other arguments can be made for or against these column prefixes?
>>
>>
>>BTW, our primary development data base is Sybase, but we have (and
>>will have, I'm sure) others (Oracle, MS Access, et al); I'd like to
>>address this globally.
>>
>>Thanks!
>>
>>--------------------------------------------------------------------------
>>dave.mullen_at_emjay.com ==> Kyrie eleison <==
 

-- 
Surfer!
http://www.nevis-vieww.demon.co.uk
Hopeful anti-spam: alter double 'w' to single 'w' to view site & send Email.