Обсуждение: Postgres, DB design, and object IDs (of any ilk)

Just looking for postgres "best practices" input from the veterans:

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;

2. I use OIDs and link back to the customer's row with the row's OID;

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.

I know serial IDs are preferred to OIDs so let's forget #2.

With #1, where we have a three or more level identifying hierarchy I end
up repeating two or more primary keys in building up the primary key of
the lower levels. Not the end of the world, but my last DB was built in
an OODB so I got uses to having object identity and am tempted to use
serial_ids for same under postgres.

Bad idea?

On Thu, May 21, 2009 at 12:35 PM, Kenneth Tilton <kentilton@gmail.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

I wouldn't trust OIDs, because they are 32bits for once.
Secondly, Watch for index size. That's the main reason why (big)int as
a key reference is a win over other types - at least in my general
practice.

And third advice, try different approaches, and queries - to figureout
what would suit the solution. Anyone who says, that this is always
win, and something else is not - is a lier.

Merlin Moncure wrote:
> On Thu, May 21, 2009 at 12:35 PM, Kenneth Tilton <kentilton@gmail.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.

Ah, thx for the label, I have been able to google up some pros and cons.


Thx again,

kt

kentilton@gmail.com (Kenneth Tilton) writes:
> Just looking for postgres "best practices" input from the veterans:

OIDs are decidedly a bad idea; the difference between "natural" IDs
and "surrogate" IDs is a general database issue that is fairly well
documented in the literature and is not notably a PostgreSQL-specific
issue.

There are competing doctrines, basically between the respective
beliefs:

 a) Some believe that there should always be a natural primary key,
    and that it is wrong to attempt to use surrogates

 b) Others contend that even when users claim to provide natural
    primary keys that they are actually lying when they suggest
    certainty about this

Major bashing can take place back and forth.
--
output = ("cbbrowne" "@" "acm.org")
http://cbbrowne.com/info/wp.html
Rules  of  the Evil  Overlord  #100.  "Finally,  to keep  my  subjects
permanently locked in  a mindless trance, I will  provide each of them
with free unlimited Internet access. <http://www.eviloverlord.com/>