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?