Thread: PostgreSQL Developer Best Practices

On Sat, 2015-08-22 at 15:15 +0000, Melvin Davidson wrote:

> I've been searching for a "PostgreSQL Developer Best Practices" with not
> much luck,
> so I've started my own. At the risk of stirring up a storm of controversy,
> I would appreciate additional suggestions and feedback.
>

You might add: Create all relation names as plurals.

Or, if your site uses predominantly singular names, make that the
standard.  Consistency within the site is more important than any
dogmatic belief about whether singular or plural forms is better.  If
you don't put it in the standard, someone will eventually create tables
with names that don't gel with everything else.

__
Marc

On 26/08/15 04:33, Marc Munro wrote:
> On Sat, 2015-08-22 at 15:15 +0000, Melvin Davidson wrote:
>
>> I've been searching for a "PostgreSQL Developer Best Practices" with not
>> much luck,
>> so I've started my own. At the risk of stirring up a storm of controversy,
>> I would appreciate additional suggestions and feedback.
>>
> You might add: Create all relation names as plurals.
>
> Or, if your site uses predominantly singular names, make that the
> standard.  Consistency within the site is more important than any
> dogmatic belief about whether singular or plural forms is better.  If
> you don't put it in the standard, someone will eventually create tables
> with names that don't gel with everything else.
>
> __
> Marc
>
>
>
>
>
Actually I would suggest standardising on singular names, not JUST
because that this the standard I prefer!  :-)

But (also) because:

 1. Singular words tend to be shorter

 2. plurals are more ambiguous wrt spelling

 3. there other good reasons, that I've forgotten for now :-(
    (but I remember having them!!!)


Cheers,
Gavin

On 8/25/2015 1:38 PM, Joshua D. Drake wrote:
> But a VIN is in fact, UNIQUE so it is useful as a PK.
>
> JD
>
But a VIN is *not* guaranteed to exist, nor is it guaranteed never to
change, and I regard those as pretty important characteristics in a PK.

VINs were not required in the U.S. until 1954, and were not in a
standardized format until 1981; other countries have different dates. If
you are dealing with [or ever might deal with] pre-war classics, early
imports, kit cars, or other out-of-the-mainstream vehicles, you have to
deal with the possibility of a vehicle that doesn't have a traditional
VIN, certainly not one in the 'expected' 17-character  format. Changing
VINs likewise are very very rare but not impossible (perhaps the most
common instance would be something like an antique where they used the
engine serial number as the VIN, only now it has had the engine replaced
and the DMV insists it have a new number). A lot of "natural" PKs have
similar oddities and corner cases that 99.99% of us will never
encounter, but you don't want to be in the 0.01%. Artificial keys don't
suffer these problems.

--christine desmuke

On 08/25/2015 04:27 PM, Gavin Flower wrote:
> On 26/08/15 04:33, Marc Munro wrote:
>> On Sat, 2015-08-22 at 15:15 +0000, Melvin Davidson wrote:
>>
>>> I've been searching for a "PostgreSQL Developer Best Practices" with
>>> not
>>> much luck,
>>> so I've started my own. At the risk of stirring up a storm of
>>> controversy,
>>> I would appreciate additional suggestions and feedback.
>>>
>> You might add: Create all relation names as plurals.
>>
>> Or, if your site uses predominantly singular names, make that the
>> standard.  Consistency within the site is more important than any
>> dogmatic belief about whether singular or plural forms is better.  If
>> you don't put it in the standard, someone will eventually create tables
>> with names that don't gel with everything else.
>>
>> __
>> Marc
>>
>>
>>
>>
>>
> Actually I would suggest standardising on singular names, not JUST
> because that this the standard I prefer!  :-)
>
> But (also) because:
>
> 1. Singular words tend to be shorter
>
> 2. plurals are more ambiguous wrt spelling
>
> 3. there other good reasons, that I've forgotten for now :-(
>    (but I remember having them!!!)
>
>
4. Each tuple is an instance of entity, not entities :)
> Cheers,
> Gavin
>
>

On Wednesday 26 August 2015 10:27:04 Gavin Flower wrote:
> Actually I would suggest standardising on singular names, not JUST
> because that this the standard I prefer!  :-)
>
> But (also) because:
>
>  1. Singular words tend to be shorter
>
>  2. plurals are more ambiguous wrt spelling
>
>  3. there other good reasons, that I've forgotten for now :-(
>     (but I remember having them!!!)

Oh, so you name your tables 'fish' and 'crow' instead of 'school' and
'murder' ? Such wasted opportunities :p

--
Vincent de Phily

On 27/08/15 00:03, Vincent de Phily wrote:
> On Wednesday 26 August 2015 10:27:04 Gavin Flower wrote:
>> Actually I would suggest standardising on singular names, not JUST
>> because that this the standard I prefer!  :-)
>>
>> But (also) because:
>>
>>   1. Singular words tend to be shorter
>>
>>   2. plurals are more ambiguous wrt spelling
>>
>>   3. there other good reasons, that I've forgotten for now :-(
>>      (but I remember having them!!!)
> Oh, so you name your tables 'fish' and 'crow' instead of 'school' and
> 'murder' ? Such wasted opportunities :p
>
???

On 26/08/2015 19:54, Gavin Flower wrote:
> On 27/08/15 00:03, Vincent de Phily wrote:
>> On Wednesday 26 August 2015 10:27:04 Gavin Flower wrote:
>>> Actually I would suggest standardising on singular names, not JUST
>>> because that this the standard I prefer!  :-)
>>>
>>> But (also) because:
>>>
>>>   1. Singular words tend to be shorter
>>>
>>>   2. plurals are more ambiguous wrt spelling
>>>
>>>   3. there other good reasons, that I've forgotten for now :-(
>>>      (but I remember having them!!!)
>> Oh, so you name your tables 'fish' and 'crow' instead of 'school' and
>> 'murder' ? Such wasted opportunities :p
>>
> ???

A school of fish and a murder of crows... wonderfully evocative
collective nouns.

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

----- Original Message -----
From: Gavin Flower


On 27/08/15 00:03, Vincent de Phily wrote:
> On Wednesday 26 August 2015 10:27:04 Gavin Flower wrote:
>> Actually I would suggest standardising on singular names, not JUST
>> because that this the standard I prefer!  :-)
>>
>> But (also) because:
>>
>>   1. Singular words tend to be shorter
>>
>>   2. plurals are more ambiguous wrt spelling
>>
>>   3. there other good reasons, that I've forgotten for now :-(
>>      (but I remember having them!!!)
> Oh, so you name your tables 'fish' and 'crow' instead of 'school' and
> 'murder' ? Such wasted opportunities :p
>
>???

Dish - Dishes
Fish - School

Bow - Bows
Crow - Murder

Goose - Geese
Moose - Moose

House - Houses
Mouse - Mice

and so on...

Cheers,
George



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 27/08/15 06:59, Raymond O'Donnell wrote:
> On 26/08/2015 19:54, Gavin Flower wrote:
>> On 27/08/15 00:03, Vincent de Phily wrote:
>>> On Wednesday 26 August 2015 10:27:04 Gavin Flower wrote:
>>>> Actually I would suggest standardising on singular names, not JUST
>>>> because that this the standard I prefer!  :-)
>>>>
>>>> But (also) because:
>>>>
>>>>    1. Singular words tend to be shorter
>>>>
>>>>    2. plurals are more ambiguous wrt spelling
>>>>
>>>>    3. there other good reasons, that I've forgotten for now :-(
>>>>       (but I remember having them!!!)
>>> Oh, so you name your tables 'fish' and 'crow' instead of 'school' and
>>> 'murder' ? Such wasted opportunities :p
>>>
>> ???
> A school of fish and a murder of crows... wonderfully evocative
> collective nouns.
>
> Ray.
>
>
Hmm...

Reminds me about a story I was told where a programmer had used names of
his favourite footballers as labels in an assembler program!  :-)


-Gavin