Hi,

In our design, lookup tables (Customers, Products etc..) are using
natural key as PK. We use VARCHAR2(30) as PK column.

Is it too long ? How long is too long ?

Thank you,
xtanto

krislioe@gmail.com wrote:
> Hi,
>
> In our design, lookup tables (Customers, Products etc..) are using
> natural key as PK. We use VARCHAR2(30) as PK column.
>
> Is it too long ? How long is too long ?
>
> Thank you,
> xtanto

Length is irrelevant.
How many bytes is the data required to uniquely define a record?

The only thing that matters are:

First Normal Form:
Make a separate table for each set of related attributes and uniquely
identify each record with a primary key.

Second Normal Form
tables do not have composite primary keys

Third Normal Form
all non-key fields are dependent on the primary key

Everything else is window dressing related to performance. And you
don't mention anything performance related in your inquiry.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Hi,

> Length is irrelevant.
I thougt that the long primary key is bad for performance, isn't it ?

And the long PK is the reason for using surrogate key, isn't it.

Thank you,
xtanto




DA Morgan wrote:
> krislioe@gmail.com wrote:
> > Hi,
> >
> > In our design, lookup tables (Customers, Products etc..) are using
> > natural key as PK. We use VARCHAR2(30) as PK column.
> >
> > Is it too long ? How long is too long ?
> >
> > Thank you,
> > xtanto
>
> Length is irrelevant.
> How many bytes is the data required to uniquely define a record?
>
> The only thing that matters are:
>
> First Normal Form:
> Make a separate table for each set of related attributes and uniquely
> identify each record with a primary key.
>
> Second Normal Form
> tables do not have composite primary keys
>
> Third Normal Form
> all non-key fields are dependent on the primary key
>
> Everything else is window dressing related to performance. And you
> don't mention anything performance related in your inquiry.
> --
> Daniel A. Morgan
> University of Washington
> damorgan@x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

DA Morgan wrote:
>
> Second Normal Form
> tables do not have composite primary keys
>

I realize that your definitions are only informal but this one is a
total mistake. 2NF does not imply the absence of composite keys.

--
David Portas

DA Morgan wrote:
> Second Normal Form
> tables do not have composite primary keys

huh?

--

Niall Litchfield
Oracle DBA
http://www.orawin.info/services/

David Portas wrote:
> DA Morgan wrote:
>> Second Normal Form
>> tables do not have composite primary keys
>>
>
> I realize that your definitions are only informal but this one is a
> total mistake. 2NF does not imply the absence of composite keys.

Not informal at all. Perhaps this will clarify it.

"Second normal form (2NF) requires that data stored in a table with a
composite primary key must not be dependent on only part of the table's
primary key"

http://en.wikipedia.org/wiki/Databas...nd_normal_form
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Niall Litchfield wrote:
> DA Morgan wrote:
>> Second Normal Form
>> tables do not have composite primary keys
>
> huh?

As in my response to Dave:

"Second normal form (2NF) requires that data stored in a table with a
composite primary key must not be dependent on only part of the table's
primary key"

http://en.wikipedia.org/wiki/Databas...nd_normal_form
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

krislioe@gmail.com wrote:
> Hi,
>
>> Length is irrelevant.
> I thougt that the long primary key is bad for performance, isn't it ?
>
> And the long PK is the reason for using surrogate key, isn't it.
>
> Thank you,
> xtanto

While it is true that a numeric comparison will probably be faster than
a string comparison there are many other considerations.

First ... you have no control over the length of a natural key. It is
what it is.

Second ... if you create a surrogate key you add overhead with the
creation and use of the surrogate and you create overhead with the then
required maintenance of a unique constraint and index on the natural
key too.

I wouldn't lose one minutes sleep over the length of a primary key
unless I had a specific performance issue that, after optimizing
everything else, was related to that specific string comparison.

I've seen a lot of slow systems that required tuning. Not once have I
seen the root cause be the use of a natural key.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

DA Morgan wrote:
> Niall Litchfield wrote:
> > DA Morgan wrote:
> >> Second Normal Form
> >> tables do not have composite primary keys
> >
> > huh?
>
> As in my response to Dave:
>
> "Second normal form (2NF) requires that data stored in a table with a
> composite primary key must not be dependent on only part of the table's
> primary key"
>
> http://en.wikipedia.org/wiki/Databas...nd_normal_form
> --
> Daniel A. Morgan
> University of Washington
> damorgan@x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

LOL. Do you normally trust what you read in Wikipedia? Maybe it is on
the reading list at U of W? That definition is completely bogus and
worthless. Anyway it is not the same as what you originally said.

2NF has nothing to do with whether keys are composite or not. I
recommend you study some more reliable sources to clear up your own
misconceptions. There is no value in attempting to understand a potted
summary otherwise.

--
David Portas

David Portas wrote:
> DA Morgan wrote:
>> Niall Litchfield wrote:
>>> DA Morgan wrote:
>>>> Second Normal Form
>>>> tables do not have composite primary keys
>>> huh?
>> As in my response to Dave:
>>
>> "Second normal form (2NF) requires that data stored in a table with a
>> composite primary key must not be dependent on only part of the table's
>> primary key"
>>
>> http://en.wikipedia.org/wiki/Databas...nd_normal_form
>> --
>> Daniel A. Morgan
>> University of Washington
>> damorgan@x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Group
>> www.psoug.org
>
> LOL. Do you normally trust what you read in Wikipedia? Maybe it is on
> the reading list at U of W? That definition is completely bogus and
> worthless. Anyway it is not the same as what you originally said.
>
> 2NF has nothing to do with whether keys are composite or not. I
> recommend you study some more reliable sources to clear up your own
> misconceptions. There is no value in attempting to understand a potted
> summary otherwise.

No. But it is rather hard to photocopy Codd's books and post them here.

Perhaps you'd prefer one of these instead:
http://www.cdf.toronto.edu/~boconnel...malization.txt
http://www.devx.com/ibm/Article/20859
http://www.cs.jcu.edu.au/Subjects/cp...ation/2nf.html
http://www.utexas.edu/its/windows/da...ng/rm/rm7.html

I've got a couple of hundred more if these similarly produce fits of
laughter.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org