/ database

MySQL: Columns Design Decisions

MySQL Primitive Types

The three primitive data types in MySQL are text, number, and date/time.

Text

Within the text data type, the subtypes are the following:

CHAR(LENGTH) - Fixed-length field from 0 to 255 characters long.
VARCHAR(LENGTH) - Variable-length field from 0 to 65,535 characters long.
TINYTEXT - A string with maximum length of 255 characters.
TEXT - A string with maximum length of 65,535 characters long.
MEDIUMTEXT - A string with maximum length of 16,777,215 characters long.
LONGTEXT - A string with maximum length of 4,294,967,295 characters long.

Number

Within the number data type, the subtypes are the following:

  • TINYINT(LENGTH) - A number value that goes from -128 to 127 or, if unsigned, 0 to 255.
  • SMALLINT(LENGTH) - A number value that goes from -32,768 to 32,767 or, if unsigned, 0 to 65,535.
  • MEDIUMINT(LENGTH) - A number value that goes from -8,388,608 to 8,388,607 or, if unsigned, 0 to 16,777,215.
  • INT(LENGTH) - A number value that goes from -2,147,483,648 to 2,147,483,647 or, if unsigned, 0 to 4,294,967,295.
  • BIGINT(LENGTH) - A number value that goes from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,808 or, if unsigned, 0 to 18,446,744,073,709,551,615.
  • FLOAT(LENGTH, DECIMALS) - A relatively small floating decimal point number.
  • DOUBLE(LENGTH, DECIMALS) - A large floating decimal point number.
  • DECIMAL(LENGTH, DECIMALS) - A large floating decimal point number stored as a string.

Date/Time

Within the date/time data type, the subtypes are the following:

  • DATE - Date in format YYYY-MM-DD.
  • DATETIME - Date and time in format YYYY-MM-DD HH:MM:SS.
  • TIMESTAMP - Date and time in format YYYYMMDDHHMMSS.
  • TIME - Time in format HH:MM:SS.

Additional Considerations

In addition to data types, there are other things to consider.

Length of Column

The length parameter is optional and specifies the maximum length the column can hold. It is best practice to specify the column length for all applicable data types. Obviously, the length should be the longest possible entry for that column. Too small and you end up truncating your data. But too big, the database will take up too much memory and potentially have performance issues. It is an important consideration if the database will have tables with millions of rows or dealing with data types that might store large values (eg TEXT, MEDIUMTEXT, LONGTEXT, or an unmentioned data type: BLOBS).

For example, if the column is to store last names, a length of 50 should suffice. This should suffice for most users in United States.

NOT NULL

Every column can be defined as NOT NULL as well. This means the column must have a value for every entry. For example, the first line of an address should be set to NOT NULL while the second line of an address for apartment or suite number should be left optional. A sample code would look like this:

CREATE TABLE addressbook (
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    address_one VARCHAR(60) NOT NULL,
    address_two VARCHAR(60)
);

In the example above, the first_name and last_name columns are variable-length strings with maximum length of 40 characters and are both required. The address_one column corresponds to the first line of the address is is required while the address_two column corresponds to the optional second line of the address.