From: Gavin Flower <GavinFlower@archidevsys.co.nz>
To: David Johnston <polobo@yahoo.com>
Cc: 'Carlos Mennens' <carlos.mennens@gmail.com>
Cc: 'PostgreSQL (SQL)' <pgsql-sql@postgresql.org>
Subject: Re: Unable To Modify Table
Date: Sat, 14 Jan 2012 21:20:47 +1300
Message-ID: <4F113ADF.8070102@archidevsys.co.nz> (raw)
In-Reply-To: <016a01ccd14b$1f40baf0$5dc230d0$@yahoo.com>
References: <CAAQLLO70j_j-nz7sZmXbcKT7QRmuf6oQRsCT_N5=+8UC7NFp1A@mail.gmail.com>
<016a01ccd14b$1f40baf0$5dc230d0$@yahoo.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: GavinFlower@archidevsys.co.nz, polobo@yahoo.com, carlos.mennens@gmail.com
Subject: Re: Unable To Modify Table
In-Reply-To: <4F113ADF.8070102@archidevsys.co.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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