Thread: PostgreSQL Developer Best Practices

PostgreSQL Developer Best Practices

From
Marc Munro
Date:
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





Re: PostgreSQL Developer Best Practices

From
Gavin Flower
Date:
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


Re: PostgreSQL Developer Best Practices

From
Christine Desmuke
Date:
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


Re: PostgreSQL Developer Best Practices

From
Rob Sargent
Date:
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
>
>



Re: PostgreSQL Developer Best Practices

From
"David G. Johnston"
Date:
On Tue, Aug 25, 2015 at 6:27 PM, Gavin Flower <GavinFlower@archidevsys.co.nz> 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!!!)


Because my first college IS professor taught it this​
 
​way...I do like these other reasons though.

The fact that a table can have multiple rows is pretty much a given - and I wouldn't suggest plural adherents name any singleton tables using the singular form - so no information is lost.  Having since learned OO the class of something is labelled in the singular form and in many ways a relation definition is equivalent to a class definition.

​David J.​

Re: PostgreSQL Developer Best Practices

From
Vincent de Phily
Date:
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



Re: PostgreSQL Developer Best Practices

From
John McKown
Date:
On Wed, Aug 26, 2015 at 7:03 AM, Vincent de Phily <vincent.dephily@mobile-devices.fr> 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

​OTOH, either is better than some insane DBA who calls them "TableOf????" (camel case in quotes to preserve case)​ to be "self documenting and easy to read" (Likely a COBOL programmer in a former life). Oh, and then "to make it simple", all the SQL uses the AS to "alias" the table name to a single alphabetic character in [a-z].
 

--
Vincent de Phily

 
--

Schrodinger's backup: The condition of any backup is unknown until a restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

Re: PostgreSQL Developer Best Practices

From
Gavin Flower
Date:
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
>
???


Re: PostgreSQL Developer Best Practices

From
Raymond O'Donnell
Date:
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


Re: PostgreSQL Developer Best Practices

From
"George Weaver"
Date:
----- 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


Re: PostgreSQL Developer Best Practices

From
Gavin Flower
Date:
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



Re: PostgreSQL Developer Best Practices

From
Melvin Davidson
Date:
Thank you to all that contributed to this thread. I now have about a dozen guidelines
I would like to share. However, since a few individuals felt it was more of an
opportunity to prove or disprove certain theories, rather than just contribute additional
good practices, I will not be posting them directly to this thread. Rather, if anyone  is
interested in obtaining a copy, please just send me a request to my direct email and I
will gladly reply back with a copy.
   
Know that these are general guidelines. If you disagree with any of them, then you
are free to change them to suit you specific requirements. Just please do not start
another thread to debate the merits or disadvantages of them. If it's more important
to prove that you are right rather than contribute to help others, then you are doing
neither.


On Wed, Aug 26, 2015 at 3:13 PM, Gavin Flower <GavinFlower@archidevsys.co.nz> wrote:
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




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



--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.