the table field design in mysql database?

Question

i made a research to a cms's database:

uid(Unique user ID): it set to int(11)

name(nique user name it set to varchar(60)

created(tiemstamp for when user was created) it set to int(11)

title the title of article it set to varchar(255)

1, when i should use Int or char or varchar or other types

2, how to set the number in the parenthesis。eg: varchar(60) thank you!

Answer 1

You should use INT when you want to use primary key (an INT is faster to look for than a string) or calculate data. For example, you want to calculates the number of items someone purchased when ordering some products online. Each line will have the article description, price, quantity ordered (and more - just giving an example here). In this case, by should an INT for quantity it will be easy to retrieve the number of product (total number of products for the invoice) ordered.

Strings with the type "char" and "varchar" are a little different. The type "char" is defined as follow:

A fixed-length string that is always right-padded with spaces to the specified length when stored.

In order words, if you define a first name with char(200), you will use 200 bytes. The varchar type is defined as follow:

A variable-length string. Note: Trailing spaces are removed when the value is stored (this differs from the ANSI SQL specification)

In order words, if you define a first name with varchar(255) and the name is "Tim", you will use 4 bytes. 3 for the name +1 byte for the length of the field.

Typically, you use char(6) for HTML colors (FFFFFF) and usually you use varchar for names, addresses etc...

Regarding your second question as of setup the number in the parentheses. This number will set the maximum length of a string. If you set varchar(60) to a field and when you insert or update the record and the length of the string is greater than 60 character then MySQL will truncate the string to use the first 60 characters.

You can check all the fields types here: http://help.scibit.com/mascon/masconMySQL_Field_Types.html

You can also read about database normalization: http://en.wikipedia.org/wiki/Database_normalization

Answer 2

In general type depends on what actually you need to store. However, speed and size of PK index is also very important so this is also considered then designing database tables.

Number in parenthesis indicate type size, which have different meaning depending on a type (varchar, for example, indicate maximum number of chars which can be stored). You should look to manual of db engine for this.