Database Tips

[Jim]

Shamelessly stolen from here.

After creating a MySQL database, you need to create a table. There are
many data types and choosing the wrong one can create huge problems
later on. This is a simple list of the different data types and what
they are generally used for.

TINYINT: A very small integer (-128 to 127)

SMALLINT: A small integer (-32768 to 32767)

MEDIUMINT: A medium-sized integer (-8388608 to 8388607)

INT: A normal-sized integer (-2147483648 to 2147483647)

BIGINT: A very large integer

FLOAT: A floating-point number

DOUBLE: A double-precision floating-point number

DECIMAL: A packed exact fixed-point number

VARCHAR: Varying Characters (up to 255 characters, no line break)

TEXT: A column (up to 65,535 characters, with line break)

BLOB: A case-sensitive column (up to 65,535 characters, with line break)

DATE: A date

TIME: A time

DATETIME: Date and Time combination

TIMESTAMP: useful for recording the date and time of an INSERT or UPDATE
operation


Auto Increment: Auto-Increment fields are useful for assigning unique
identification numbers for users, products, and customers, etc. By
default, fields are incremented using number characters (like "1", "2").

Primary Key: The primary key is a data column that uniquely identifies a
specific instance of that data. At least one of your fields must be a
Primary Key. Username is an example of a good primary key. You do not
want to have more than one individual having the same username.

Index Key: Allows you to speed up searches by designating a field as a
preferred data source, especially when combining data from multiple
tables.