Re: PostgreSQL Developer Best Practices

Adrian,

Stop being so technical. When we/I speak of natural keys, we are talking
about the column
that would NATURALly lend itself as the primary key.
No one ever said a number is not natural. just that there is no need to
duplicate uniqueness
with a separate number.

IOW: If we have an account table, then the account_id or account_no
would be the primary key. There is no need to have a separate serial
id as the primary key.

Likewise, if we have a car table, then registration (or vehicle_id) is
preferred.

EG: Good
CREATE TABLE car
(
registration_no varchar(30) not null,
car_make varchar(25) not null,
model varchar(15) not null;
build_year date not null;
owner varchar(50),
CONSTRAINT car_pk PRIMARY KEY (registration_no)
);

bad
CREATE TABLE car
(
id serial not null,
registration_no varchar(30) not null,
car_make varchar(25) not null,
model varchar(15) not null;
build_year date not null;
owner varchar(50),
CONSTRAINT car_pk PRIMARY KEY (id)
);

The benefit in avoiding arbitrary and simple values for the key is that it
makes
the database design much more logical.

Consider:
SELECT c.registration_no,
c.car_make,
p.part_no
FROM car c
JOIN parts p ON ( p.registration_no = c.registration_no)
WHERE registration_no = <some_var>;

versus:
SELECT c.registration_no,
c.car_make,
p.part_no
FROM car c
JOIN parts p ON ( p.id = c.id)
WHERE registration_no = <some_var>;

Why join on id when registration_no is better?

On Tue, Aug 25, 2015 at 10:17 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 08/24/2015 08:44 PM, Rob Sargent wrote:
>
>>
>> On Aug 24, 2015, at 6:53 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
>>> wrote:
>>>
>>> You are right, he was probably talking about FK's. I was just so
>>> frustrated about people insisting that using "ID" as the primary key in
>>> every table is a "good" idea,
>>> I didn't bother to reply previously. I stand firm on my belief that the
>>> primary key should be something meaningful and NOT "id" just for the sake
>>> of having a unique numeric key.
>>>
>>> What, pray tell, is the unique natural key of person in any meaningfully
>> large domain such as state? Certainly not name + birthdate. Current
>> address isn’t guaranteed. Social isn’t reliable and actually not truly
>> unique.
>>
>
> To add:
>
> 1) Who determined that a number is not natural?
>
> 2) One of the older unique natural keys (genus, species) is not so unique.
> I am a fisheries biologist by training and in my time the 'unique'
> identifier for various fishes has changed. Now that ichthyologists have
> discovered DNA testing, it can be expected there will be even more changes.
> This is even more apparent when you go back in in history. As an example:
>
> https://en.wikipedia.org/wiki/Rainbow_trout
>
> Rainbow trout
>
> Current
>
> Oncorhynchus mykiss
>
> Past
>
> Salmo mykiss Walbaum, 1792
> Parasalmo mykiss (Walbaum, 1792)
> Salmo purpuratus Pallas, 1814
> Salmo penshinensis Pallas, 1814
> Parasalmo penshinensis (Pallas, 1814)
> Salmo gairdnerii Richardson, 1836 <--The one I learned.
> Fario gairdneri (Richardson, 1836)
> Oncorhynchus gairdnerii (Richardson, 1836)
> Salmo gairdnerii gairdnerii Richardson, 1836
> Salmo rivularis Ayres, 1855
> Salmo iridea Gibbons, 1855
> Salmo gairdnerii irideus Gibbons, 1855
> Salmo irideus Gibbons, 1855
> Trutta iridea (Gibbons, 1855)
> Salmo truncatus Suckley, 1859
> Salmo masoni Suckley, 1860
> Oncorhynchus kamloops Jordan, 1892
> Salmo kamloops (Jordan, 1892)
> Salmo rivularis kamloops (Jordan, 1892)
> Salmo gairdneri shasta Jordan, 1894
> Salmo gilberti Jordan, 1894
> Salmo nelsoni Evermann, 1908
>
>
> All the above point to the same fish and have appeared and appear in
> articles and reports about said fish. Lets not even get into the common
> name situation:).
>
>
>> Even given that there are models which are made of entities with
>> legitimate attributes which per force define a unique instance, I see no
>> benefit in avoiding the convenience of an arbitrary and simple value for
>> the key. Is it the overhead of generating and storing one more value per
>> tuple that you can’t abide?
>>
>>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>

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