Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id B91EC2E2E78
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  8 Feb 2008 17:04:59 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 20734-07 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  8 Feb 2008 17:04:46 -0400 (AST)
Received: from www01.kensystem.com (www01.kensystem.com [72.19.170.120])
	by postgresql.org (Postfix) with ESMTP id A33C22E2F44
	for <pgsql-sql@postgresql.org>; Fri,  8 Feb 2008 17:04:46 -0400 (AST)
Received: from 192.168.0.3 by www01.kensystem.com (SMTPD);
	id s20080208210438.32692; Fri, 08 Feb 2008 21:04:38
Message-ID: <47ACC3E5.2060207@kensystem.com>
Date: Fri, 08 Feb 2008 14:04:37 -0700
From: Ken Johanson <pg-user@kensystem.com>
User-Agent: Thunderbird 1.5.0.14 (Windows/20071210)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: What are the (various) best practices/opinions for
	table/column/constraint naming?
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/64
X-Sequence-Number: 30155

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?