Choosing Your Column Types

Before you start working with SQL and MySQL, you have to identify your application's needs. This then dictates the database design. For the examples in this chapter, I'll create a database (generically called sitename) that stores some user registration information (which will then be incorporated into Chapter 7). The database will consist of a single table, users, that contains columns to store user ID, first name, last name, email address, password, and registration date. Table 4.1 shows the current layout, using MySQL's naming rules for column titles (alphanumeric names, plus the underscore, with no spaces).

Table 4.1. The users table will have these six columns, to store records like the sample data here.

users Table
COLUMN NAME	EXAMPLE
user_id	834
first_name	Hannah
last_name	Mauck
email	[e-mail]
password	bethany
registration_date	2004-12-15 17:00:00

Once you have identified all of the tables and columns that the database will need, you should determine each field's MySQL data type. When creating the database, MySQL requires that you define what sort of information each field will contain. There are three primary categories, which is true for almost every database application:

• Text

• Numbers

• Dates and times

Within each of these, there are a number of variantssome of which are MySQL-specificyou can use. Choosing your column types correctly not only dictates what information can be stored and how but also affects the database's overall performance. Table 4.2 lists most of the available types for MySQL, how much space they take up, and brief descriptions of each type.

Table 4.2. The common MySQL data types you can use for defining columns.

MySQL Data Types
TYPE	SIZE	DESCRIPTION
CHAR[Length]	Length bytes	A fixed-length field from 0 to 255 characters long
VARCHAR[Length]	String length + 1 bytes	A variable-length field from 0 to 255 characters long
TINYTEXT	String length + 1 bytes	A string with a maximum length of 255 characters
TEXT	String length + 2 bytes	A string with a maximum length of 65,535 characters
MEDIUMTEXT	String length + 3 bytes	A string with a maximum length of 16,777,215 characters
LONGTEXT	String length + 4 bytes	A string with a maximum length of 4,294,967,295 characters
TINYINT[Length]	1 byte	Range of 128 to 127 or 0 to 255 unsigned
SMALLINT[Length]	2 bytes	Range of 32,768 to 32,767 or 0 to 65,535 unsigned
MEDIUMINT[Length]	3 bytes	Range of 8,388,608 to 8,388,607 or 0 to 16,777,215 unsigned
INT[Length]	4 bytes	Range of 2,147,483,648 to 2,147,483,647 or 0 to 4,294,967,295 unsigned
BIGINT[Length]	8 bytes	Range of 9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 or 0 to 18,446,744,073,709,551,615 unsigned
FLOAT	4 bytes	A small number with a floating decimal point
DOUBLE[Length, Decimals]	8 bytes	A large number with a floating decimal point
DECIMAL[Length, Decimals]	Length + 1 or Length + 2 bytes	A DOUBLE stored as a string, allowing for a fixed decimal point
DATE	3 bytes	In the format of YYYY-MM-DD
DATETIME	8 bytes	In the format of YYYY-MM-DD HH:MM:SS
TIMESTAMP	4 bytes	In the format of YYYYMMDDHHMMSS; acceptable range ends in the year 2037
TIME	3 bytes	In the format of HH:MM:SS
ENUM	1 or 2 bytes	Short for enumeration, which means that each column can have one of several possible values
SET	1, 2, 3, 4, or 8 bytes	Like ENUM except that each column can have more than one of several possible values

Many of the types can take an optional Length attribute, limiting their size. (The square brackets, [], indicate an optional parameter to be put in parentheses.) You should keep in mind that if you insert a string five characters long into a CHAR(2) field, the final three characters will be truncated. This is true for any field in which the length is set (CHAR, VARCHAR, INT, etc.). So your length should always correspond to the maximum possible value (as a number) or longest possible string (as text) that might be stored.

The various date types have all sorts of unique behaviors, which are documented in the MySQL manual. You'll use the DATE and TIME fields primarily without modification, so you need not worry too much about their intricacies.

There are also two special typesENUM and SETthat allow you to define a series of acceptable values for that field. An ENUM column can have only one value of a possible several thousand values, while SET allows for several of up to 64 possible values. These are available in MySQL but aren't present in every database application.

To select the column types

1.

Identify whether a column should be a text, number, or date type (Table 4.3).

Table 4.3. The users table with generic data types.

users Table
COLUMN NAME	TYPE
user_id	number
first_name	text
last_name	text
email	text
password	text
registration_date	date/time

This is normally an easy and obvious step. You will find that numbers such as ZIP codes and dollar amounts will be text fields if you include their corresponding punctuation (dollar signs, commas, and hyphens), but you'll get better results if you store them as numbers and address the formatting elsewhere.

2.

Choose the most appropriate subtype for each column (Table 4.4).

Table 4.4. The users table with more specific data types.

users Table
COLUMN NAME	TYPE
user_id	MEDIUMINT
first_name	VARCHAR
last_name	VARCHAR
email	VARCHAR
password	CHAR
registration_date	DATETIME

In my example, I'll set the user_id as a MEDIUMINT, allowing for up to nearly 17 million values (as an unsigned, or non-negative, number). The registration_date will be a DATETIME, storing both the day and the specific moment a user registered. When deciding among the date types, consider whether or not you'll want to access just the date, the time, or possibly both.

The other fields will be mostly VARCHAR, since their lengths will differ from record to record. The only exception is the password, which will be a fixed-length CHAR (you'll see why when inserting records later in this chapter). See the sidebar "CHAR vs. VARCHAR" for more information on these two types.

3.

Set the maximum lengths for text and number columns (Table 4.5).

Table 4.5. The users table with set length attributes.

users Table
COLUMN NAME	TYPE
user_id	MEDIUMINT
first_name	VARCHAR(15)
last_name	VARCHAR(30)
email	VARCHAR(40)
password	CHAR(40)
registration_date	DATETIME

The size of any field should be restricted to the smallest possible value, based upon the largest possible input. For example, if the largest a number such as user_id can be is in the hundreds, set the column as a three-digit SMALLINT (allowing for up to 999 values).

Tips

• In Chapter 5, "Advanced SQL and MySQL," I'll develop a more complex database and discuss database design in more detail.

• Many of the data types have synonymous names: INT and INTEGER, DEC and DECIMAL, etc.

• The TIMESTAMP field type is automatically set as the current date and time when an INSERT or UPDATE occurs, even if no value is specified for that particular field. If a table has multiple TIMESTAMP columns, only the first one will be updated when an INSERT or UPDATE is performed.

• There is also a BLOB type, which is a variant on TEXT that allows for storing binary files (like images) in a table. This type is also used for some encrypted data.

CHAR vs. VARCHAR

Both of these types store strings and can be set with a fixed maximum length. One primary difference between the two is that anything stored as a CHAR will always be stored as a string the length of the column (using spaces to pad it). Conversely, VARCHAR strings will be only as long as the stored string itself.

The implications of this are

• VARCHAR columns tend to take up less disk space.

• Unless you are using the InnoDB table type, CHAR columns are faster to access than VARCHAR.

Granted, the speed and disk space differences between the two types may be imperceptible in most cases, and as the InnoDB table type becomes the norm, any speed difference will disappear.

There is also a third, minor difference between these two: MySQL trims off extra spaces from CHAR columns when data is retrieved and from VARCHAR when it's inserted.

If a string field will always be of a set length (e.g., a state abbreviation), use CHAR; otherwise, use VARCHAR. You may notice, though, that in some cases MySQL defines a column as the one type (like CHAR) even though you created it as the other (VARCHAR). This is perfectly normal and is MySQL's way of improving performance.