Oracle-L: RE: Design question...

From: Pardee, Roy E <roy.e.pardee_at_lmco.com>
Date: Wed, 17 Apr 2002 10:21:00 -0800
Message-ID: <F001.00446E60.20020417102100@fatcity.com>

If you go with the first option, you will likely be able to get out of joining your STATE table to the referencing tables in a bunch of cases (since the 2-letter abbreviation is interpretable on its own). But if you'll wind up having to do the join anyway (e.g., to display the STATE_DESC) then those joins will likely be faster on a numeric...

HTH,
-Roy

Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487

-----Original Message-----

Sent: Wednesday, April 17, 2002 10:19 AM To: Multiple recipients of list ORACLE-L

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
--

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).
--

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

Author: Pardee, Roy E
INET: roy.e.pardee_at_lmco.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