RE: Design question...

We have a country_code table to represent countries, but the first version of the app is supposed to be the U.S. only version. Obviously, any flexibility that we can design in right now can only help in the next phase.

It's the client's left hand that keeps saying this will only be used in the US, but the right hand keeps saying that a future version of the app will be international...but were not allowed to ask about international requirements or specs. Sigh.

-----Original Message-----
Sent: Wednesday, April 17, 2002 3:04 PM
To: Multiple recipients of list ORACLE-L

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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Grabowy, Chris
  INET: cgrabowy_at_fcg.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).