In SQL how do I increment id like this: client1, client2, client3

I'd like to hear some advice on designing the tables in this database...

Let's say I have 3 tables: Shirts, Pants and Shoes

They all have the same fields: Name, Description, Manufacture, Material and ID I can't simply put the IDs as 1, 2, 3, 4 for each of them so I've decided to a notation: shirt001, shirt002, pants001, pants002, pants003 and so forth. I know th开发者_如何学Pythonis isn't the correct way to design it so should I just combine it into one big table and have the fields as Name, Description, Manufacture, Material, ID with an extra 'Type' field?

Yes, you should make one database table that defines all of the different products:

Table:   Product 
Columns: ID (pk, identity),
         ProductTypeID (fk to ProductType table),
         Name,
         Description, 
         Manufacture,
         Material

Table:   ProductType
Columns: ID (pk, identity),
         Name,
         Description

Yes, put it into a single table, with a foreignkey TypeID that links to a table ItemTypes.

EDIT: MySQL added to bottom of answer

The database design you are quoting doesn't adhere to first normal form (1NF).

It'd be more beneficial to you if you split your database into multiple tables, with a Type table. This would produce a relational database that doesn't have duplication, and the IDs wouldn't clash if there was a user error.

Table: Item
       Id          (int, auto-increment) [PrimaryKey]
       FkTypeId    (int) [ForeignKey]
       Name        (string)
       Description (string)
       Material    (string)
       Manufacture (string)

Table: Type
       Id          (int, auto-incrememnt) [PrimaryKey]
       Name        (string)

You can now insert your types into the Type table (Shirt, Pants, Shoe, etc) and associate each item with their relavent Type.

Here is a wikipedia page on first normal form, something you should try to adhere to in any database design: http://en.wikipedia.org/wiki/First_normal_form

Here is MySQL version of my suggestion:

NOTE THIS WORKS IN MyISAM

CREATE TABLE ProductType (
  Id INTEGER UNSIGNED  NOT NULL   AUTO_INCREMENT,
  Name VARCHAR  NOT NULL    ,
PRIMARY KEY(Id));


CREATE TABLE Product (
  Id INTEGER UNSIGNED  NOT NULL   AUTO_INCREMENT,
  ProductType_Id INTEGER UNSIGNED  NOT NULL  ,
  FkTypeId INTEGER UNSIGNED  NOT NULL  ,
  Name VARCHAR  NOT NULL  ,
  Description VARCHAR  NOT NULL  ,
  Material VARCHAR  NULL  ,
  Manufacturer VARCHAR  NULL    ,
PRIMARY KEY(Id, ProductType_Id)  ,
INDEX Product_FKIndex1(ProductType_Id),
  FOREIGN KEY(ProductType_Id)
    REFERENCES ProductType(Id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION);

Hope this helps.