Re: Primary Key of type string ?

Walt (walt_askier@xxxxxxxxxxxxxx) wrote:
: sybrandb@xxxxxxxxx wrote:
: > On Thu, 31 May 2007 13:41:01 +0200, Robert Klemme wrote:
: >
: >> And I thought the standard choice would be the key dictated by business
: >> requirements...
: >
: > Question: do you like to have composite, possible lengthy foreign keys
: > in all of your tables?
: > One usually ends up with a surrogate key.
: > And NO those shouldn't be varchar2(31)
: > (Regrettably this is a real world example in a commercial
: > application).
: >

: I think my favorite example is a table with a 6 field primary key, one
: of which is a timestamp. And yes, there are foreign keys that reference it.

: No, I didn't design it. I'm not going to embarrass the software vendor,
: but everyone here has heard of them.

On the other hand, when a query filters on parts of the primary key then
possibly only the index must be read, not the table, since the required
values are already in the index. Same is true for displaying columns that
are in a key, and at various other times as well.

e.g.

select max(date_is_in_pk),col1_of_pk,col2_of_pk
from my_table
group by col1_of_pk,col2_of_pk

The table will likely not be read at all, just the index, which is smaller
and already grouped = much less work = faster.

There are pros and cons for surrogate keys and natural keys, and also pros
and cons for being consistent in your use of one approach or the other.

$0.10
.

Relevant Pages

  • Whos the man in NY?
    ... News votes for Pedro as king of New York ... Pros: The Mets may be destined to be a .500 team this season, ... Cons: ... The Yankees are his daddy. ...
    (alt.sports.baseball.ny-mets)
  • SUMMARY: how do you manage documentation?
    ... Pros: existing docs can remain in native format, user logins, version control ... Cons: need to setup MySQL or Postgresql ... "I don't create documentation for others to use. ... therefore, doesn't consume proprietary files ...
    (SunManagers)
  • Re: Whos the man in NY?
    ... > Every era in New York sports history has The Man, ... Below, you'll find our rankings, with pros and cons ... The Yankees are his daddy. ...
    (alt.sports.baseball.ny-mets)
  • Re: NC State bouncyball coaching search
    ... coach, as they see it." ... Pros and Cons: ... recruiter with today's young athletes. ...
    (rec.sport.football.college)
  • Re: Why use a composite PK ever?
    ... Your ideas around surrogate key usage is completely wrong. ... we might actually need to use their DNA sequence. ... if you have used the primary key as the access through to the data from the ... completed set is presented to Foobar all at once, not a row at a time. ...
    (comp.databases)