basic primary key question

[salmanjavaheri]

Hi, should a primary key always be a number?
if so, how can one enter data in a user friendly way? for example, in
a form if i want to select a customer name from a drop down list, but
this record has a primary key that is a number, how does the primary
key get stored, without it actually displaying on the form? It would
look better if the name was on the form, not some number. if that
makes any sense. I know how to 'display' non primary key information
in a drop down box and have it select the primary key instead, but
even then the primary key is still shown once you make your selection.

TIA

 

[Andy Hull]

Hi

Ignoring whether a primary key should be a number for the time being...

With a drop down box you control whether a column is displayed or not by
setting the "Column Widths" on the Format tab of the controls property sheet.

Eg. If the drop down box has a row source of...

select PersonID, ForeName, LastName from tblPeople

and you only wanted to display the ForeName and LastName then set the
"Column Widths" to

0;2;2

So the first column (PersonID) has a width of 0 and therefore isn't displayed.

Note that the "Bound Column" setting would normally be 1 (meaning the first
column) so when you use the control's value, perhaps to filter your records,
then it is equal to PersonID and not what is displayed. This is normally
necessary to uniquely identify the record chosen.

Hope this helps.

As regards primary keys having to be numbers - no, they don't have to be.
There are many opposing views about primary keys and whether they should be
meaningless to users or not. generally, I prefer meaningless PK's because
even the best thought out meaningful PK's often have their format / structure
change sometime in the future causing lots of database headaches such as
changing data types or updating keys throughout a whole database.

Regards

Andy Hull

 

[salmanjavaheri]

oh yes, that is perfect cheers. your reason for using a meaningless
primary key has convinced me, does having a number speed things up at
all as well?

 

[AaronKempff]

should a primary key always be a number?
if so, how can one enter data in a user friendly way?

No, identifiers are text, precisely for those reasons People need to
write them down! ID is short for IDIOT.

"What car are you driving these days?"
"A number 5"
"What.. ?"
"Well, Its the fifth one I've had"