Should running number be separated column from primary key in database?

Question

I have questions that blogged my mind on separating member number and member id.

Suppose that we have a database with member id primary key of Guid type. Then, normally, when we view the profile (on web page), we will display a Profile/100001 instead of Profile/{Guid-id}. So, in this case, we would have another column for member number which is separated from member id.

Now, assume that we have a case where Member ID is int datatype, and the running number of Member No is incremented 1 each time start from 100001, should we separate the member ID (primary key) from member number in this case?

This question is focus on web application database design. Because I got to "know" that exposing primary key to user is not a good practice. So, does that means that we should have an internal ID(primary key) that is used for database and another column with running number that is display to user?

Answer 1

Your Primary Key is a unique key and should be sufficient to find the records that you need. However, if you need a sequential number for other identifying reasons, then YES you should make it a separate unique value.

I personally would skip the GUID key and just use the Integer, but that is a choice you will need to make.

Getting a sequential set of keys without gaps will require some code. Are you using Microsoft SQL Server or some other version. If MS SQL Server, look at:

http://msdn.microsoft.com/en-us/library/ff878058.aspx

This is a discussion of using Sequence Numbers, with some warnings about the limitations.

Answer 2

Usage of primary key for database alone is a best practice. Member number will refer only the particular table entry but primary key is used to interlink tables using joint queries by having same primary key in different tables.