How to generate serial STRING primary key in Database.

Question

My collegues don't like auto generated int serial number by database and want to use string primary key like :

"camera0001" "camera0002"

As camera may be deleted, I can not use "total nubmer of camera + 1" for id of a new camera.

If you were me, how will you generate this kind of key in your program?

PS : I think auto generated serail number as primary key is OK, just don't like arguing with my collegues.

Answer 1

Have a table with your serial number counters, increment it and insert your record.

OR

Set the Id to 'camera' + PAD((RIGHT(MAX(ID), 4) + 1), '0', 4)

Answer 2

Don't do it like "camera0001"! argue it out, that is a horrible design mistake.

try one of these:

• http://en.wikipedia.org/wiki/Database_normalization
• http://www.datamodel.org/NormalizationRules.html
• just google: database normalization

Each column in a database should only contain 1 piece of information. Keep the ID and the type in different columns. You can display them together if you wish, but do not store them the together! You will have to constantly split them and make simple queries difficult. The string will take a lot of space on disk and cache memory, if it is a FK it will waste space there too.

have a pure numeric auto column ID and a type column that is a foreign key to a table that contains a description, like:

Table1

YourID     int  auto id  PK
YourType   char(1)  fk

TypeTable

 YourType   char(1)  PK
 Description  varchar(100)

Table1

YourID  YourType   YourData....
1         C        xyz
2         C        abc
3         R        dffd
4         C        fg

TypeTable

YourType   Description
C          Camera
R          Radio

Answer 3

I don't agree that a sequence number is always the best key. When there is a natural primary key available, I prefer it to a sequence number. If, say, your cameras are identified by some reasonably short model name or code, like you identify your "Super Duper Professional Camera Model 3" as "SDPC3" in the catalog and all, that "SDPC3" would, in my opinion, be an excellent choice for a primary key.

But that doesn't sound like what your colleagues want to do here. They want to take a product category, "camera", that of course no one expects to be unique, and then make it unique by tacking on a sequence number. This gives you the worst of both worlds: It's hard to generate, a long string which makes it slower to process, and it's still meaningless: no one is going to remember that "camera0002904" is the 3 megapixel camera with the blue case while "camera0002905" is the 4 megapixel camera with the red case. No one is going to consistently remember that sort of thing, anyway. So you're not going to use these values as useful display values to the user.

If you are absolutely forced to do something like this, I'd say make two fields: One for the category, and one for the sequence number. If they want them concatenated together for some display, fine. Preferably make the sequence number unique across categories so it can be the primary key by itself, but if necessary you can assign sequence numbers within the category. MySQL will do this automatically; most databases will require you to write some code to do it. (Post again if you want discussion on how.) Oh, and I wouldn't have anyone type in "camera" for the category. This should be a look-up table of legal values, and then post the primary key of this look-up table into the product record. Otherwise you're going to have "camera" and "Camera" and "camrea" and dozens of other typos and variations.