[popeye1974]

Hi ... I had a basic concepts question ...

I have seen some tables which have a numeric ID as the Primary Key.
This a running number with no "intelligence" ...

I am trying to see when to use this ID as the PK vs actual data fields to form the PK.

I could think of the following ... I am looking forward to hearing from the experts.

Use Numeric ID when
a) number of records in a table are really large - increase performace.
for e.g. Invoices or Receipts table in an ERP app.
b) when your base identifier could change
For e.g.
PK username (jdoe)
firstname (Jane)
lastname (Doe)

In this case it might be a good idea to have
PK user_id
username (Candidate Key)
first name
last name

In this case if Jane's last name changes, then it does not affect the PK and if I have audits, then I am fine ...

Another example could be Item.

Rest of the time use actual fields to form the PK.

for e.g.
PK COUNTRY_CODE
PK STATE_CODE
STATE_DESC ...

Any thoughts?

[r937]

you have a very good understanding of the pros and cons

[georgev]

increased performance?

[popeye1974]

Hi ... Thanks a lot for your replies ...

George ... by performance I meant ...
I could have the following struc

Table = Project Instance
PK Project Code
PK Year
PK Quarter
PK Month
...

OR
Table = Project Instance
PK Project Instance Number (Running Number)
Project Code
Year
Quarter
Month
....

In this case I'll a Candidate Key consisting of (Project Code + Year + Quarter + Month) ...

If Project Instance is a table which is being used as a FK in a lot of other tables, then I am better of using just one field as the PK.

Is this a correct assumption?

Also is it true that is faster to search numbers vs strings?

[blindman]

Rudy favors natural keys.
I, and the other right-thinking star-bellied sneeches prefer surrogate keys.
I would encourage you to be consistent either way, and not mix methodologies within a database. Unless you like aggravating your developers.

[r937]

Originally Posted by popeye1974

If Project Instance is a table which is being used as a FK in a lot of other tables, then I am better of using just one field as the PK.

Is this a correct assumption?

yes, that is correct, that's a very good example of when a surrogate key would be advisable





blindman, what's wrong with "mixing methodologies within a database"

surely you wouldn't assign a surrogate key to state codes just because there are other tables in the same database with surrogate keys?

come on, man -- the slavish adherence to silly rules is worse practice than using a natural key when it's appropriate

[Pat Phelan]

I'm with Blindman both on my preference for PK values that the user neither sees nor understands, and on the point of avoiding mixing methodologies too.

My experience has always been that if the user can see it, they will eventually want to change it. Many of the products that I work with are hybrid technologies, some parts implemented using one tool, some parts using a different tool. In these environments, propagating changes to primary/foreign keys are ugly at their very best, and those changes are rarely examples of the products behaving at their best!

With that being said, I also firmly believe that alternate keys should be declared (using UNIQUE constraints) so that the natural keys are also represented and preserved. While practical experience has made me very gun shy about using natural keys as primary keys, that means that I use a surrogate in place of (to represent) a natural key and it emphatically does NOT mean that I disregard the natural key...

My reason for avoiding mixes of natural/foreign keys is more selfish... I tend to develop libraries of code instead of re-inventing the wheel every time I need a ride. My libraries tend to "think" in terms of widgets that have widgetID columns. This means that it is a LOT less work for me to use a schema that is consistent than a schema that isn't consistent.

-PatP

[r937]

Originally Posted by Pat Phelan

My libraries tend to "think" in terms of widgets that have widgetID columns. This means that it is a LOT less work for me to use a schema that is consistent than a schema that isn't consistent.

consistency, let me show u it

create table addresses
( ...
, stateID char(2)
);
create table states
( stateID char(2) not null primary key
, statename varchar(37) not null unique
);

vwalah, 100% consistent with "widgets that have widgetID columns"

[blindman]

Originally Posted by r937

blindman, what's wrong with "mixing methodologies within a database"

surely you wouldn't assign a surrogate key to state codes just because there are other tables in the same database with surrogate keys?

Yes, I would. And we already had this discussion. You remember my new mantra, don't you? "There's no such thing as a lookup table."