Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Is the use of VARCHAR(256) as Primary Keys preferred in Oracle?

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

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 30 Nov 2002 22:06:21 -0600
Message-ID: <u65uegqwk.fsf@hotpop.com>


On Sun, 01 Dec 2002, pablo_at_dev.null wrote:
> Galen Boyer <galenboyer_at_hotpop.com> wrote in
> news:uptsmgsup.fsf_at_hotpop.com:
>

>> You again dogged my answer.  I said redesign of the application
>> would have to happen.  What wouldn't have to happen, with artificial
>> keys, is redesign and rewrite of the PKs.  The choice of keys isn't
>> going to preclude a well-designed app, but choosing artificial,
>> dummy keys makes it so one doesn't have to redefine and rewrite
>> cascading keys.

>
> Re-repeating myself, the idea behind using an artificial primary key
> is that one builds a level of abstraction between the physical layer
> and the logical layer.

I think of it more as the database designer not trusting any outside source for his primary keys. Let attributes be able to be maintained, but never, ever touch my PK's. With the use of natural keys, then the database designer is "trusting" that the natural key is the correct one.

> I'm not advocating that the business keys get chucked though, they get
> used as alternate keys. This way we end up with the best-of both
> words at the cost of adding an additional index.

A "natural" key will invariably define a unique index on a table and will be the column that users type into text boxes for translation to database queries. These "natural" keys are the crux of defining an application's behaviour.

I just don't believe they make alot of sense in defining the relationships between my tables. I really don't know if email address or ssn is the correct key. I do know that we will have to make every accomodation in our application for interacting with the email addresses and the ssn of the customer, but I don't want to trust the relationships within my tables to a client's correctly entering these values.

[...]

> Furthermore, it becomes _very_ easy to auto-generate code for the
> above. I know because we have an code generator that generates
> insert/update/delete PL/SQL for any schema by reading the meta data
> from the schema itself. All written in Perl/OraPerl.

This is only a nice side effect of the design. Ease of development probably shouldn't be the consideration, although I succombed to that one in an earlier design as well.

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.
Received on Sat Nov 30 2002 - 22:06:21 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US