Number or char for primary key column

问题:

I'm building a database of a very important state institution. So far, for Primary Key columns I've sometimes used numbers, particularly int32 or int64, and sometimes I've used char but still I stored only numeric characters. Since this a very crucial task I have to make it both performance and comfort-wise efficient. Now I want to know,if it makes any difference to use number or char for a PK column considering the values in the char column will still be numeric characters.

P.S.You might wonder what the point is in using char if I anyway store numeric characters. The reason is becuase the PK column is composed of many parts, like 3 characters for country, 3 characters for province,3 characters for city and 6 characters for a person. And besides that I can make string operations on those columns without worrying about explicit conversion.(I know there's implicit conversion in Oracle but it's discouraged to rely on it.)

回答1:

Neither as such. Your primary key should be a surrogate. Int or big int, Guid if scaling is going to be an issue. Then you should have country, province, city and person as a unique compound key. The 'intelligent' number thingy is rarely a good idea.

回答2:

integer data type commonly used for primary key because it give better performance for data indexing than varchar. If you need another unique key like combination of characters, you can add it beside primary key marked as unique.

Another important reason, for me, to use generic data for primary key like integer or GUID is for flexibility. For example, you can use email address as primary key of user table. but few days later, when you need to change your application rule that one user can have more than one email address. you will get it more difficult to change you data structure.