Re: Number or character?

From: <pihlab_at_cbr.hhcs.gov.au>
Date: 8 Nov 93 14:59:53 +1000
Message-ID: <1993Nov8.145953.1_at_cbr.hhcs.gov.au>


In article <2bck3e$kfh_at_access.digex.net>, mstein_at_access.digex.net (Michael P. Stein) writes:
> [Question about employee table and having a STATE table with the employee
> STATE field being a foreign key, and what data type to make the foreign key]
>
> This is a classic example of too much of a good thing. Normalization
> is fine up to a point, but this is carrying it too far. There is a
> reasonable efficient natural coding: the two-character postal state code
> (NV, TX, etc.). If you really need to have 'Nevada', 'Texas', etc, then
> go ahead and have a state table keyed by this code. Trying to shrink
> this to a one-byte code is carrying things too far. Are you that hard up
> for disk space? Even a million employees would only add one meg of storage
> going with two bytes rather than one. Remember, *people* have to use
> your data; how do you expect them to know what state code '7' means?

I missed the original posting but you could implement the state table as a hardcoded VIEW which uses a large DECODE to give back the long name of the state (with the code etc).

Hmm. Would work efficiently on a small number of states but you have quite a few over there don't you. Oh well, just a thought.

>
> --
> Mike Stein The above represents the Absolute Truth.
> POB 10420 Therefore it cannot possibly be the official
> Arlington, VA 22210 position of my employer.
 

-- 
Bruce...        pihlab_at_cbr.hhcs.gov.au

"If you swallow a live frog first thing in the morning ...
 Nothing worse will happen to either of you for the rest of the day."

*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of                                      * 
*          Health, Housing, Local Government & Community Services *
* Canberra, Australia                             (W) 06-289-7056 *
*******************************************************************
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************
Received on Mon Nov 08 1993 - 05:59:53 CET

Original text of this message