Re: Postgres, DB design, and object IDs (of any ilk)

On Thu, May 21, 2009 at 12:35 PM, Kenneth Tilton <kentilton(at)gmail(dot)com> wrote:
> If the primary key of the customer table is cust_short_name and my DB
> reflects also customer departments, I can link a customer to its departments
> one of three ways:
>
> 1. The department table has a cust_short_name column and makes that the
> first segment of its primary_key;
>
> 3. I give the customer a cust_serial_id column and make it SERIAL and give
> the dept table a column called cust_serial_id.

This is the very well tread 'natural vs. surrogate key' debate.
There's tons of threads about this online...including the archives
here. It's a very complicated issue with lots of facets (performance,
logic, elegance of design) with no clear right answer so it largely
boils down to personal choice.

I would venture to guess that a large majority of database developers
use incrementing serial keys. That said, I personally was in that
camp until I was tasked with converting a large erp system written in
cobol/isam (where natural keys are used for technical reasons) into
sql. Following that experience, I have decided that a hybrid approach
is best for me.

I would strongly advise learning how to map out your data either way
and choose the approach that best meets your design criteria. I'm
especially skeptical of database development standards that _always_
use a serial primary key and _always_ use it for relating data.

merlin