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 <[EMAIL PROTECTED]>
> 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
