Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Design question...
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 2I'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
![]() |
![]() |