artificial keys or not?

From: Hannes Dorbath <light(at)theendofthetunnel(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: artificial keys or not?
Date: 2005-04-22 09:15:27
Message-ID: 4268ba10$0$24344$8fe63b2a@news.disputo.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

we have lots of small relations that only consist of a few attributes.
like in an ecommerce app we have relations like:

payment_methods
payment_methods_lang

delivery_types
delivery_types_lang

basket_states
basket_states_lang

payment_methods, delivery_types, basket_states mostly just consist of

foo_id serial [PK]
active boolean

the *_lang relations look like

foo_id integer [PK]
language_id integer [PK]
label varchar
description text

The problem is, our queries got a bit unreadable..

SELECT
b.basket_id
FROM
baskets b
WHERE
b.basket_state_id IN (1,3,6,7);

..dosen't tell much, so we came up with an additional attribute called
handle, which is just an alternate key for foo_id.

SELECT
b.basket_id
FROM
baskets b
INNER JOIN basket_states bs USING (basket_state_id)
WHERE
bs.handle IN (
'open',
'sign_pending',
'freight_cost_calc_pending',
'expired'
);

looks more readable, but there is need for one more join as well..

..so the only question is:

drop the the serials as PKs and just use short text handles? our
database size is arround 290 mb. there shouldn't be speed issues as long
as proper indexes exit, right? some of the conditions are dynamic
though, so there can't be an proper index in any case. any other concerns?

thanks in advance

Responses

Browse pgsql-general by date

  From Date Subject
Next Message JM 2005-04-22 09:18:54 ShmemAlloc: out of memory
Previous Message Sebastian Böck 2005-04-22 09:09:48 Re: Multiple RULES on Views