Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id 5A5921969A5C
	for <pgsql-sql@postgresql.org>; Sat, 14 Jan 2012 04:21:25 -0400 (AST)
Received: from mbx.knossos.net.nz ([202.160.48.10])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <GavinFlower@archidevsys.co.nz>) id 1RlyrZ-00062S-J1
	for pgsql-sql@postgresql.org; Sat, 14 Jan 2012 08:21:25 +0000
Received: from [10.1.1.3] (60-234-150-59.bitstream.orcon.net.nz
	[60.234.150.59]) (authenticated bits=0)
	by mbx.knossos.net.nz (8.14.4/8.14.4) with ESMTP id q0E8KlUq030818
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT);
	Sat, 14 Jan 2012 21:20:58 +1300
Message-ID: <4F113ADF.8070102@archidevsys.co.nz>
Date: Sat, 14 Jan 2012 21:20:47 +1300
From: Gavin Flower <GavinFlower@archidevsys.co.nz>
Organization: ArchiDevSys
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:9.0) Gecko/20111222 Thunderbird/9.0
MIME-Version: 1.0
To: David Johnston <polobo@yahoo.com>
CC: "'Carlos Mennens'" <carlos.mennens@gmail.com>,
	"'PostgreSQL (SQL)'" <pgsql-sql@postgresql.org>
Subject: Re: Unable To Modify Table
References: 
 <CAAQLLO70j_j-nz7sZmXbcKT7QRmuf6oQRsCT_N5=+8UC7NFp1A@mail.gmail.com>
	<016a01ccd14b$1f40baf0$5dc230d0$@yahoo.com>
In-Reply-To: <016a01ccd14b$1f40baf0$5dc230d0$@yahoo.com>
Content-Type: multipart/alternative;
	boundary="------------000107000600020800090905"
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201201/55
X-Sequence-Number: 36226

This is a multi-part message in MIME format.
--------------000107000600020800090905
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 13/01/12 05:56, David Johnston wrote:
> [...]
> Contrary to my earlier advice assigning a sequential ID (thus using a
> numeric TYPE) is one of the exceptions where you can use a number even
> though you cannot meaningfully perform arithmetic on the values.  The reason
> you would use a numeric value instead of a character is that the value
> itself is arbitrary and the space required to store a number is less than
> the space required to store a string of the same length.
>
> There are many points-of-view regarding whether to use "serial" PRIMARY KEYs
> but regardless of whether you add one or not you should try and define a
> UNIQUE constraint on the table by using meaningful values.  However, for
> things like Orders this is generally not possible and so you would want to
> generate a sequential identifier for every record.
>
> David J.
>
>

Hmm...

In any database I design, I deliberately keep primary keys quite 
separate from any user visible values. In order to minimise changes to 
the database resulting from business format changes, such as redoing the 
format of customer numbers for marketing purposes.

Also, in a chain of parent child tables, the child only needs to know 
how to get its parent, it does not need to know its grandparents! One 
insurance package I worked on, had the primary key of a child table a 
concatenation of its parent's primary key with a unique field. So some 
child tables had multiple character field as their primary keys, 
potentially have keys of some 45 or more characters!

I normally use integers for the primary key type. This makes keeping 
track of records in a program much easier.

However, I do not usually expose these keys to users, and it would be 
rare (if ever) to have them as fields in search boxes.


Cheers,
Gavin


--------------000107000600020800090905
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    On 13/01/12 05:56, David Johnston wrote:
    <blockquote cite="mid:016a01ccd14b$1f40baf0$5dc230d0$@yahoo.com"
      type="cite">
      <pre wrap="">[...]
Contrary to my earlier advice assigning a sequential ID (thus using a
numeric TYPE) is one of the exceptions where you can use a number even
though you cannot meaningfully perform arithmetic on the values.  The reason
you would use a numeric value instead of a character is that the value
itself is arbitrary and the space required to store a number is less than
the space required to store a string of the same length.

There are many points-of-view regarding whether to use "serial" PRIMARY KEYs
but regardless of whether you add one or not you should try and define a
UNIQUE constraint on the table by using meaningful values.  However, for
things like Orders this is generally not possible and so you would want to
generate a sequential identifier for every record.

David J.


</pre>
    </blockquote>
    <meta http-equiv="CONTENT-TYPE" content="text/html;
      charset=ISO-8859-1">
    <p class="western" style="margin-bottom: 0cm">Hmm...<br>
      <br>
      In any
      database I design, I deliberately keep primary keys quite separate
      from any user visible values. In order to minimise changes to the
      database resulting from business format changes, such as redoing
      the
      format of customer numbers for marketing purposes. <br>
      <br>
      Also, in a
      chain of parent child tables, the child only needs to know how to
      get
      its parent, it does not need to know its grandparents! One
      insurance
      package I worked on, had the primary key of a child table a
      concatenation of its parent's primary key with a unique field. So
      some child tables had multiple character field as their primary
      keys,
      potentially have keys of some 45 or more characters!</p>
    <p class="western" style="margin-bottom: 0cm">I normally use
      integers
      for the primary key type. This makes keeping track of records in a
      program much easier.</p>
    <p class="western" style="margin-bottom: 0cm">However, I do not
      usually expose these keys to users, and it would be rare (if ever)
      to
      have them as fields in search boxes.<br>
      <br>
      <br>
      Cheers,<br>
      Gavin</p>
    <title></title>
    <meta name="GENERATOR" content="LibreOffice 3.4 (Unix)">
    <style type="text/css">
	<!--
		@page { margin: 2cm }
		P { margin-bottom: 0.21cm }
	-->
	</style>
  </body>
</html>

--------------000107000600020800090905--