String as primary KEY?

I have this database structure

CREATE TABLE `productinfo` (
  `ProductID` int(11) NOT NULL AUTO_INCREMENT,
  `ProductName` varchar(255) NOT NULL,
  `ProductImage` varchar(255) NOT NULL,
  `CategoryID` int(11) NOT NUL开发者_Go百科L,
  `SubCategoryID` int(11) NOT NULL,
  `ProductBrief` varchar(255) NOT NULL,
  `Features` text NOT NULL,
  `Specifications` text NOT NULL,
  `Reviews` text NOT NULL,
  `Price` varchar(255) NOT NULL,
  `Status` tinyint(4) NOT NULL,
  PRIMARY KEY (`ProductID`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

I now I need to turn ProductID, CategoryID, and SubCategoryID into a string like Ps-5678 for the part number. ProductID is the primary key so how do i change the structure of the database. CategoryID, and SubCategoryID are primary keys in other tables so how do i handle this..is it as easy as turning

  `ProductID` int(11) NOT NULL AUTO_INCREMENT

into a string..and getting rid of

  PRIMARY KEY (`ProductID`)

ideas, suggestions anyone

Primary keys are for the database.

Display names are for end users.

Do not confuse one with another! Don't make a primary key out of something that has a meaning. You will regret it sooner or later.

Having a surrogate key / identity / autonumber as a primary key is a very good idea and is used widely in database design.

You can add a column or even a DERIVED COLUMN and add a unique constraint on it.

I believe it should be that easy. however you need to determine what string type you want to use http://dev.mysql.com/doc/refman/5.1/en/string-types.html

Your requirement is unclear. How do you get "PS-5678" fro 3 int columns ? There is only 2 components in your example.

Do you just need to CONVERT the 3 INTs to a single CHAR() string ?

If so, the database is fine, no need to change the table at all !?!?! The three components are already available, correctly seperated, as distinct columns. What you are looking for is merely DISPLAYING the three components as a single string.

It's not clear to me from your question just what you do to product, category, and subcategory to make your part number. I'll assume for the sake of argument that you are concatenating them together, like product 123, category 456, subcategory 789 gives part number 123-456-789 or some such.

I like to use natural identifiers as primary keys whenever practical. But "whenever practical" can be a serious constraint. If your natural identifier is derived by somehow combining three other fields, you have four choices:

1. Make the primary key be the combination of these three fields. This tends to be a pain. All your joins then must match on three fields, searches must test three fields, etc.

2. Create a new field that is the concatenation of the three fields, and use this as the priamry key. Then whenever one of the "basic" fields changes, also change this concatenated field. This is a very, very bad idea. Don't do it. It's redundant data, with all the bad things that come from redundant data.

3. Replace the three separate fields with one combined field. This is worse than #2. Now when you need the individual values, you have to take the field apart.

4. Give up and create a synthetic key, like a serial number. Use this as the primary key, and then just use the natural key for display purposes. If my natural key requires concatenating or otherwise manipulating three fields, I tend to go with this option.