Types of data

When designing your tables, you need to choose what attributes you want the table to describe and, just as importantly, what type of data each attribute is. Because data comes in all shapes and sizes, DBMSs allow you to choose very specific storage types for each attribute - should it be a string, a number, or a boolean? More specifically, should it be a small string, a large string, a huge string, a floating-point number, or a cardinal number (only accepts positive integers)?

Most databases support the same set of data types, but they all have their own extensions here and there. This table describes the most popular column types available in MySQL:

As you can see, the top five data types are all integers - this is the recommended way of saying how large an integer you need. Note that that each integer comes in signed format (allows negative numbers) and unsigned (does not allow negative numbers, but therefore has a larger range of positive numbers)..

Floats and doubles are both floating-point numbers that handle massive numbers, with doubles holding the largest. These are specified using the size of number you display when reading from the field, and the number of decimals also. Therefore FLOAT(5,2) would output a maximum number of 99999.99.

CHAR and VARCHAR fields hold character data from one character in length to a maximum of 255 characters. A character can be any data - any letter, number, symbol, etc - and that makes CHAR and VARCHAR fields very flexible. CHARs and VARCHARs have their length specified after them inside brackets, e.g. CHAR(10) is a fixed-length, 10-character string field, whereas VARCHAR(200) is a variable-length, 10-character string field

The difference between the two is that CHAR uses a fixed-length for each field, whereas VARCHAR (VARiable CHARacter) is specified using a maximum size - if one row only uses 10 characters of its 255, it only takes up 10 bytes of space. Fixed-length records always take up their full amount of space, which has advantages and disadvantages. Fixed-length fields take up more room, but are easier to search through, whereas variable-length fields take up less room, and are slower to search through.

As most hardware today is CPU bound as opposed to hard drive bound, the chance are you will want to use CHAR fields rather than VARCHAR - you should try some small experiments out to see what your situation requires. Note that if all the fields in a table are fixed-length, the table itself is classified as fixed-length, whereas if one or more fields are variable-length, the table is classified as variable-length. This is often referred to as the row format of a table.

Once you define a field as being of type X, you are usually able to convert it to another type if so needed, and MySQL will attempt to make the conversion as painless as possible. For example, if you change from a 10-character VARCHAR field to a 20-character VARCHAR field, MySQL will keep the existing data there and just give each row more room when needed. If you try to convert a 10-character VARCHAR field to an unsigned integer, however, MySQL will simply delete the existing data, as no meaningful conversion is possible.

Just to make sure you are fully up to speed on data types, here's a short table detailing possible data types for different sorts of every day variables:

Those should make sense, with the exception perhaps of Date. Why not use the built-in DATETIME field type? If you read the chapter on working with date and time, you will know that PHP's default way to work with dates is using the number of seconds that have elapsed since January 1st 1970. More often than not it is easiest to store this Unix timestamp directly in the database, thereby keeping all your date and time in the same format across the board.

One final note before we move on: always try to choose the most precise data type. Choosing CHAR(100) rather than CHAR(255) will cut your storage requirements to about 40% of what they would otherwise have needed to be. Similarly using a BIGINT for ages is also a big waste - put some thought into the data types you choose.

Copyright ©2015 Paul Hudson. Follow me: @twostraws.