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
PK username (jdoe)
In this case it might be a good idea to have
username (Candidate Key)
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.
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.
If it's not practically useful, then it's practically useless.
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.