Natural Key vs. Autonumber in practice

From: Chris (chris_dubuc_at_hotmail.com)
Date: 05/27/04 

Date: 27 May 2004 11:20:51 -0700

All,

I am writing this post in hope that it sparks more debate about this
topic, hopefully with the responders using my real-world example to
demonstrate their points! ;-)

I have spent many hours reading the passionate debate this forum
regarding the use of artifical/surrogate keys. This is because I am
currently in the design phase of a large database project, and I am
trying to decide whether all my tables should use an Autonumber/ID (or
equivalent) primary key field or not. After reading hundreds of posts
I am still unsure of the best route to take for my application. I have
designed a number of databases in the past, but I have no formal
training in relational database theory; I am, however, always
concerned with doing things the 'right' way from the start.

Here is the simplified scenario in brief:

Geologists collect rock samples in the field, and need to record
approx. 100 observations for each sample. The majority of these
observations will be chosen from lookup tables (think ComboBoxes in
the application). Also, each lookup item (and category) may have many
names/descriptions (different languages, field codes, etc.).

Question: According to proper relational database theory and/or
practicality, which underlying schema (or variation of) should I use
to record these values in the database?

The basic data model entities as I see it are this:

Lookup Item (1 to unlimited of these per Category, usually 5 to 10
per)
Lookup Category (100 of these, really just Lookup Item's themselves,
no?)
Lookup Description (1 to unlimited per Lookup Item, usually 3 to 5)
Sample (each sample has 100 columns, one for each
observation/category)

According to what I've learned by copying other databases, normalizing
as much as possible and using AutoNumber/ID columns, I have come up
with the following schema (w/example records):

(note: I am considering 'Lookup Category' entities as really just
'Lookup Items' themselves)

Schema 1)

                (auto)
LookupItem: LookupItemID CategoryID
                ------------------------
                     1 0 <-this is a Category
                     2 1 <-these belong to the
Category above
                     3 1 <-
                     4 0
                     5 4
                     6 4
                     (etc...)

                        (auto)
LookupDescriptionType: DescriptionTypeID Description
                        ------------------------------
                              1 English
                              2 English Abbr.
                              3 Spanish
                              4 Spanish Abbr.
                              (etc...)

LookupDescription: LookupItemID DescriptionTypeID Description
                       
--------------------------------------------------
                              1 1 Sample Types
                              1 2 ST
                              1 3 El Sample Typo
                              2 1 Rock
                              2 2 R
                              2 3 Roca
                              3 1 Soil
                              3 2 S
                              4 1 Sample Colors
                              5 1 Red
                              5 3 Rojo
                              6 1 Green
                              (etc...)

Sample: SampleID SampleType SampleColor etc...
         -----------------------------------------
            1 2 5

Schema 2)

After reading the posts on Natural Keys, I am thinking the schema
should maybe look like this (w/appropriate unique indexes):

LookupItem: LookupItem Category (natural key being both columns)
              ----------------------
              Sample Types Category
              Rock Sample Types
              Soil Sample Types
              Stream Sample Types
              Sample Color Category
              Red Sample Color
              Green Sample Color
              (etc...)

AlternateDescriptions: LookupItem Category DescriptionType
Description
                        
----------------------------------------------------
                         Sample Types Category English Abbr.
ST
                         Rock Sample Types English Abbr.
R
                         Rock Sample Types Spanish
Roca
                         (etc...)

Sample: SampleID SampleType SampleColor etc...
         ------------------------------------
              1 Rock Red

Is this last schema correct according to relational database theory
(using natural keys)? If so, aren't I duplicating a lot of information
and violating xNF rules? Also, what would be the natural key for the
Sample table if not a derived (i.e. autonumbered) SampleID?

Or am I way off, and there is some other 'best' implementation?

Thanks for any input/debate, and for reading this post all the way to
this point!

Chris DuBuc
President
Buke, Inc.

Relevant Pages

  • Database Lookup Functoid Not Returning any values.
    ... I've been unable to get the Database lookup functoid to return any values. ... I'm attempting to use a value from my Source Schema to do a lookup in a SQL ... Value Extractor Functoid and assig it to a field on my Destination Schema. ...
    (microsoft.public.biztalk.general)
  • transform db lookup table to xml schema
    ... I'm creating a xml schema for my existing database. ... for any change in the lookup values, I'd have to change the schema, ...
    (comp.lang.java.programmer)
  • Re: meeting workspace, DEFAULT Required Column...
    ... The only way to change it is to change the schema used to create it. ... > the default LOOKUP or something?!?! ... Prev by Date: ...
    (microsoft.public.sharepoint.portalserver)
Loading