RE: Design question...

Well Chris, as you are probably aware, there is a theoretical train of thought that says that every table should have a primary key that is an arbitrary ID, not related to the data in any way shape or form. The benefit is that data that is SUPPOSSED to be unique (like ssn's) can break and your application can most likely still run. I have taken the road of ID as primary key, unique key on what would be a natural primary key in my designs.

However I doubt we'll have two MN's, two FL's, two AR's in the near future.

Efficiency? I would think the number would be more efficient but I have no concrete evidence to back this up.

Two cents from an extremely hormonal furnace...

Lisa Koivu
Oracle Database Tank
Fairfield Resorts, Inc.
954-935-4117

> -----Original Message-----
> From: Grabowy, Chris [SMTP:cgrabowy_at_fcg.com]
> Sent: Wednesday, April 17, 2002 1:19 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Design question...
>
> 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: Koivu, Lisa
  INET: lisa.koivu_at_efairfield.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).