Re: PostgreSQL Developer Best Practices

Personally I always set the natural key with a not null and unique
constraint, but create an artificial key for it as well. As an example, if
we had a product table, the product_sku is defined as not null with a
unique constraint on it, while product_id is the primary key which all
other tables reference as a foreign key.

In the case of a many to many situation, I prefer to use a two column
composite key. In the case of a many to many, i've never run into a case
where I needed to reference a single row in that table without knowing
about both sides of that relation.

Just my $0.02
-Adam

On Tue, Aug 25, 2015 at 12:15 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Tue, Aug 25, 2015 at 11:40 AM, Melvin Davidson <melvin6925(at)gmail(dot)com>
> wrote:
>
>> 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?
>>
>>
> ​I believe you are mistaken if you think there are absolute rules you can
> cling to here. But even then I would lean toward calling primary keys an
> internal implementation detail that should be under the full control of the
> database in which they are directly used. Artifical "natural" keys I would
> lean toward turning into, possibly unique, attributes. Inherent "natural"​
>
> ​keys get some consideration for using directly.
>
> The issue arise more, say, in a many-to-many situation. Do you define the
> PK of the linking table as a two-column composite key or do you introduce
> ​a third, serial, field to stand in for the pair?
>
> David J.
>
>