Re: Is the use of VARCHAR(256) as Primary Keys preferred in Oracle?

This has been my experience exactly. Before you know it, to do a simple join you are required to have numerous comparisons in a where clause. Bulky and inefficient.

Systems evolve over time, and the use of natural keys although "easier" to understand, are a liability for future enhancement/modification.

I understand the concern about having multiple indexes. However, although this is a very informative thread, I still haven't seen anything to change my mind and I prefer surrogate key structures.

Jeff

"Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> wrote in message news:co8itu89q9o13i7gne3dodrkao8hl2cu6j_at_4ax.com...
> On Mon, 18 Nov 2002 07:35:59 -0800, Jim <no-spam_at_no-spam.org> wrote:
>
> >I confess I don't understand the preference for the use of a surrogate
> >key instead of a natural key except in special cases.
> >
> >I can see that a surrogate, "artificial" key would have some value IF
> >all "natural" columns in a table must be updatable, IF no 'natural'
> >column, or reasonable combination of 'natural' columns, would be
> >unique, and IF all updating of the table would be perfomed by
> >application programs able to deal with the essentially meaningless
> >surrogate key.
> >
> >On the other hand, use of a 'natural' key composed of column(s)
> >understood and meaningful to the user would, it seems to me, yield a
> >more straightforward, easier for the user to understand design.
> >
> >Am I missing something (probably)?
> >
> >
>
>
> The meaning of such a natural key tends to evolve over time,
> especially when users didn't specify their requirements correctly
> during the initial design phase. Quite often you end up with adding
> extra (quite often meaningless ordinal ) columns to those key, and
> inevitably all those columns would have to be replicated to any other
> tables referring to them.
> This results in designs with primary keys composed of up to 4 or 5
> columns, do you still think that is 'meaningful'? It definitely
> hampers performance, so one should better use surrogate keys from the
> start.
>
> Regards
>
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Tue Nov 19 2002 - 07:54:30 CST