From: Ken Johanson <pg-user@kensystem.com>
To: pgsql-sql@postgresql.org
Subject: What are the (various) best practices/opinions for table/column/constraint naming?
Date: Fri, 08 Feb 2008 14:04:37 -0700
Message-ID: <47ACC3E5.2060207@kensystem.com> (raw)
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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pg-user@kensystem.com
Subject: Re: What are the (various) best practices/opinions for table/column/constraint naming?
In-Reply-To: <47ACC3E5.2060207@kensystem.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox