Thread: What are the (various) best practices/opinions for table/column/constraint naming?

I'd like to see a list o t he various approaches, and a poll as to which 
are best and why, for naming table and columns and constraints. We've 
all seen several variations, but the most common (and pg used) seems to be:

columns:
primary key: <table-name>_pk OR <table-name>_id OR <table-name>_rowid
foreign key: <foreign-table-name>_fk OR <foreigh-table-name>_join

indexes:
<table-name>_<column_name>_idx

sequences:
<table-name>_<column_name>_seq

constraints:
<table-name>_<column_name>_req OR <table-name>_<column_name>_constr (etc)



The most used variations seem to be removing the '_', and/or to remove 
the table/column prefix from objects where it is implied (when there is 
seen to be no point trying to make the names unique, e.g when a label is 
needed):

columns:
primary key: pk

example:
SELECT person.pk [AS person_id], person.named, company.pk, company.named
FROM contact AS person
JOIN contact AS company ON person.companyid=company.pk



Other variations suggest putting the type at the start of the object name:

columns:
primary key: pk_<table-name> etc
foreign key: fk_<foreign-table-name> etc



And other names which don't necessarily represent constraints or indexes 
and are only meaningful to the apps:

columns:
<name>_id (integer numbers or alpha-num, abstract/machine meaningful: 
uuids, base-36 etc)
<name>_no (integer numbers, human meaningful)
<name>_nm OR <name>_name (named value, e.g user_name, app_name, etc)
<name>_date OR <name>_ts (datetime/timestamp, e.g created_date, 
modified_date etc)
<name>_info (informational value)


And other naming conventions suggest using mixed/camel case 
(quoted-identifiers) instead of '_' delimiters, or no delimiters at all...



Has anyone seen articles or iso/ansi endorsed best-practices for naming, 
or otherwise have an opinion about the variations?

Speaking on behalf of the GNUmed schema.

The type of thing comes first for consistency.

primary key: pk
  Some might argue pk_<table_name> is preferrable such that  in joins one ist not forced to use column aliases. We do
insuch cases. The "... where table.pk = ..." just seems  soo intuitive.
 

foreign key: fk_<foreign_table_name>
  This then affords fk_<foreign_table_name>_<foreign_table_column>  should that ever be needed (likely an indication of
baddesign).
 

indices: idx_<table>_<column>

sequences: so far we relied on PG giving us a name

constraints: named by what they *do*, such as ensure_..._consistency

functions: f_<purpose>

trigger functions: trf_<purpose>

trigger: tr_<purpose>

> And other naming conventions suggest using mixed/camel case 
> (quoted-identifiers)
We don't use mixed case as that would *require* quoting which is
liable to being forgotten.

Not much help, just our convention.

Karsten
-- 
Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen! 
Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer