Re: Primary Key of type string ?

From: Ed Prochak <edprochak_at_gmail.com>
Date: 31 May 2007 12:14:59 -0700
Message-ID: <1180638899.127798.274180@w5g2000hsg.googlegroups.com>

I don't often disagree with you Sy, but I must here.

On May 31, 11:48 am, sybra..._at_hccnet.nl wrote:
> On Thu, 31 May 2007 13:41:01 +0200, Robert Klemme
>
> <shortcut..._at_googlemail.com> wrote:
> >And I thought the standard choice would be the key dictated by business
> >requirements...
>
> Question: do you like to have composite, possible lengthy foreign keys
> in all of your tables?

If that matches the data I am modelling, then YES. The first guideline IMHO is the phrase:
the key, the whole key and nothing but the key, so help me Codd. (not my invention, but the quote is very suscinct)

> One usually ends up with a surrogate key.

There is a balance. It comes down to what level of normalization makes sense. Software Engineering is about the tradeoffs between theory and reality, speed performance and space performance, maintainability and optimization. While a lot of systems end up using surrogate keys in a lot of places, I don't agree that that is always the best choice. It certainly should NOT be the first choice for PK.

> And NO those shouldn't be varchar2(31)

Agreed. A surrogate is best numeric. (If you must make something up for the key, then pick something efficient.)

> (Regrettably this is a real world example in a commercial
> application).

Unfortunately a contractor here managed to slip a few tables like that into my system (lack of certain development controls here). I rant a little every time I forget and run an ad hoc query like:

    SELECT * FROM xtable WHERE xkey >200000;

which ORACLE easily handles the type conversion silently and returns rows like:

XKEY ...
---- ...
9    ...

(sanafrazan razafrazen!!)

Have a good day.

   Ed Prochak Received on Thu May 31 2007 - 14:14:59 CDT