Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Design question...

RE: Design question...

From: Toepke, Kevin M <ktoepke_at_trilegiant.com>
Date: Wed, 17 Apr 2002 10:26:05 -0800
Message-ID: <F001.00446F30.20020417102605@fatcity.com>


Hmmmm....

from a pure relational standpoint (something like 4th or 5th normal form), keys shouldn't have any inherent meaning so you should go with STATE_ID as the PK.

>From a real-world perspective, I would use STATE_CD as the PK. Its not going
to change very often (how often do state abbreviations change?) unless you are going international. And, if you have the appropriate indexes on the fk columns you can eliminate a join from many queries.

>From a space perspective, there is no difference:

	SELECT VSIZE(1) FROM dual;	-- result is 2
	SELECT VSIZE(50) FROM dual;	-- result is 2
	SELECT VSIZE('AK') FROM dual;	-- result is 2
I've never seen a noticable difference in performance or FK management either.

Caver

-----Original Message-----
Sent: Wednesday, April 17, 2002 1:19 PM
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: Toepke, Kevin M
  INET: ktoepke_at_Trilegiant.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).
Received on Wed Apr 17 2002 - 13:26:05 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US