RE: Primary Keys

From: David Barbour <dbarbour_at_connectsouth.com>
Date: Thu, 01 Feb 2001 14:35:13 -0800
Message-ID: <F001.002A8385.20010201141233@fatcity.com>

Brian,

This may be rather simplistic, but why can't you just use the bank routing number, account number, check number as the primary key? Routing numbers are unique to each bank. Account numbers are unique to each bank/customer. Check numbers are unique to each customer. Occasionally, an unscannable check will be entered manually as perhaps #9999, which may end up as a duplicate, but you could append a timestamp or something to take care of these situations. Now 99% of your queries go through on the first pass, with a second select based on user input being required if and only if duplicate check numbers are returned.

David A. Barbour
Oracle DBA - ConnectSouth
512-681-9438
dbarbour_at_connectsouth.com

-----Original Message-----
Sent: Thursday, February 01, 2001 2:48 PM To: Multiple recipients of list ORACLE-L

The will be querying off of customer id, check number, etc but they cannot guarantee the combination of customer and check or various other columns will be a unique row so the need for a sequence. The columns that would normally make up the PK are ALMOST always unique but once in a while ...

This data is coming from banks - how accurate can it be? :)

Once they receive the rows back that match the query they will then do a second select using the rowid returned from the first select to pull back the specific images.

Brian
- "Gogala, Mladen" <MGogala_at_oxhp.com> wrote:
  > Well, how are you going to locate the image you need? If none of the
  > supported data is needed to describe or locate the image, why have
  > the
  > data at all? I'm not quite sure how to answer your question.
  >
  >
  > -----Original Message-----
  > Sent: Thursday, February 01, 2001 2:08 PM
  > To: Multiple recipients of list ORACLE-L
  >
  >
  > I know this violates the most basic data modeling techniques but tell
  > me what you think.
  >
  > I'm working on creating a new database which will be fairly small in
  > number of tables ~8 but large in storage size ~4 terabytes with
  > millions of records eventually.
  >
  > The 3 large tables will hold small images along with supporting data
  > and the only reason I can see to have a primary key is to have a
  > primary key. There is not a natural key for the tables so it would
  > be
  > a sequence which would never be selected against. Given the number of
  > records all I can see it doing is taking up space, increase the time
  > of
  > the imports, generate more redo logs, etc and I can't see the
  > benefits.
  >
  > There won't be any tables hanging off of these, I won't be using
  > snapshots, replication or anything else I can think of that would
  > require a PK but the voice 'YOU MUST HAVE A PRIMARY KEY' is
  > resounding
  > loudly in my head so I must ask why?
  >
  > Tell me what you think.
  >
  > Thanks
  >
  > - Brian
  >
  > __________________________________________________
  > Get personalized email addresses from Yahoo! Mail - only $35
  > a year! http://personal.mail.yahoo.com/
  > --
  > Please see the official ORACLE-L FAQ: http://www.orafaq.com
  > --
  > Author: Brian Wisniewski
  > INET: brian_wisniewski_at_yahoo.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: Gogala, Mladen
  > INET: MGogala_at_oxhp.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).

Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Brian Wisniewski
  INET: brian_wisniewski_at_yahoo.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: David Barbour
  INET: dbarbour_at_connectsouth.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 Thu Feb 01 2001 - 16:35:13 CST