Re: PostgreSQL Developer Best Practices

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.