Re: Design question...

One thing to consider is country codes. I have seen many applications where two character country codes have been crammed into the STATE_CDS table. Usually the two character state abbreviation is the primary key, but that means that some smaller countries end up with "abbreviations" that have no relationship to the county's name. If you're absolutely certain that your application will be limited to the US, then using hte abbreviations makes a certain sense. If there's the possibility that the application will use data from outside the US then it's probably best to use some sequence generated primary key.

Just my $.02 (US)

                                                                                           
                    Bjørn Engsig                                                           
                    <bjorn               To:     Multiple recipients of list ORACLE-L      
                    @miracleas.dk        <ORACLE-L_at_fatcity.com>                            
                    >                    cc:                                               
                    Sent by: root        Subject:     Re: Design question...               
                                                                                           
                                                                                           
                    04/17/2002                                                             
                    02:21 PM                                                               
                    Please                                                                 
                    respond to                                                             
                    ORACLE-L                                                               
                                                                                           
                                                                                           

There is hardly any difference, except the waste of space for the state_id. But purists will tell you, that you should not put business information into
primary key columns, and therefore state_code should not be a primary key. And if a state is gets a new code, it is much easier to change your data if state_code is not PK/FK....

Rgds, Bjørn.

On Wednesday 17 April 2002 19:18, Grabowy, Chris wrote:
> To simplify my question, if I am creating a STATE table to hold all the
> states of the US, should I create it like this...
>
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> STATE_CODE NOT NULL CHAR(2) <-- PK
>
> STATE_DESC NOT NULL VARCHAR2(50)
>
> or like this...
>
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> STATE_ID NOT NULL NUMBER <-- PK
> STATE_CODE NOT NULL CHAR(2)
> STATE_DESC NOT NULL VARCHAR2(50)
>
> I'm trying to figure out which is more efficient, STATE_CODE or STATE_ID,
> when doing a PK lookup, dealing with FKs, etc.
>
> Many TIA!!!
>
> Chris

--
Bjørn Engsig, Miracle A/S
http://MiracleAS.dk
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Bj=F8rn=20Engsig?=
  INET: bjorn_at_MiracleAS.dk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: tday6_at_csc.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).