Search Postgresql Archives

Re: PostgreSQL Developer Best Practices

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.g.johnston@xxxxxxxxx> wrote:
On Tue, Aug 25, 2015 at 11:40 AM, Melvin Davidson <melvin6925@xxxxxxxxx> 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.



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux