pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWhat are the (various) best practices/opinions for table/column/constraint naming?
2+ messages / 2 participants
[nested] [flat]
* What are the (various) best practices/opinions for table/column/constraint naming?
@ 2008-02-08 21:04 Ken Johanson <pg-user@kensystem.com>
2008-02-09 10:58 ` Re: What are the (various) best practices/opinions for table/column/constraint naming? Karsten Hilbert <Karsten.Hilbert@gmx.net>
0 siblings, 1 reply; 2+ messages in thread
From: Ken Johanson @ 2008-02-08 21:04 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: What are the (various) best practices/opinions for table/column/constraint naming?
2008-02-08 21:04 What are the (various) best practices/opinions for table/column/constraint naming? Ken Johanson <pg-user@kensystem.com>
@ 2008-02-09 10:58 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
0 siblings, 0 replies; 2+ messages in thread
From: Karsten Hilbert @ 2008-02-09 10:58 UTC (permalink / raw)
To: pgsql-sql
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
in such 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 bad design).
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2008-02-09 10:58 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-08 21:04 What are the (various) best practices/opinions for table/column/constraint naming? Ken Johanson <pg-user@kensystem.com>
2008-02-09 10:58 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
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