Planning for Your Tables

Creating a table is easy—it's the planning that takes some brainpower. To create a simple table, you only need to give it a name. But that would make for a boring table, because it wouldn't contain any columns (fields) and couldn't hold any data. So besides the name, you should know the number of fields and the types of fields you want to have in your table.

Basic MySQL Data Types

All fields in a table are given a particular data type definition. The data type defines the type of data that's allowed in the field. With some data type definitions, you must also define the maximum length you want to allow in the field, but others are assumed to have one specific length for its particular type.

It's very important to define fields appropriately. For example, if you have a field to hold the name of a recording, and it's a 50-character varchar field, yet you try to stuff a 100-character string into the field, your string will truncate at 50 characters.

Not only is it important to define the fields correctly so that the data fits inside the fields, but if you define a field with an incorrect SQL syntax, the table won't be created, period. For example, if you want to use the text data type for a field, you cannot specify a length: it's automatically assumed to have a particular length.

Table 12.1 shows some of the more common types you will use. For a complete list, please read the MySQL manual.

Table 12.1: Some MySQL Data Types
Data Type	Definition
TINYINT	A very small integer that can be signed or unsigned. If signed, the allowable range is from 128 to 127. If unsigned, the allowable range is from 0 to 255.
SMALLINT	A small integer that can be signed or unsigned. If signed, the allowable range is from 32768 to 32767. If unsigned, the allowable range is from 0 to 65535.
MEDIUMINT	A medium-sized integer that can be signed or unsigned. If signed, the allowable range is from 8388608 to 8388607. If unsigned, the allowable range is from 0 to 16777215.
INT	A normal-sized integer that can be signed or unsigned. If signed, the allowable range is from 2147483648 to 2147483647. If unsigned, the allowable range is from 0 to 4294967295.
BIGINT	A large integer that can be signed or unsigned. If signed, the allowable range is from 9223372036854775808 to 9223372036854775808. If unsigned, the allowable range is from 0 to 18446744073709551615.
FLOAT	A floating point number that cannot be unsigned. You can define the display length (M) and the number of decimals (D). This is not required and will default to 10,2, where 2 is the number of decimals. Decimal precision can go to 24 places for a FLOAT.
DATE	A date in YYYY-MM-DD format, between 1000-01-01 and 9999-12-31. For example, December 30th, 1973 would be stored as 1973-12-30.
DATETIME	A date in YYYY-MM-DD format, between 1000-01-01 and 9999-12-31, plus hour and minute information in HH:MM:SS format. For example, 12:01 AM on December 30th, 1973 would be stored as 1973-12-30 00:01:00.
TIMESTAMP	A timestamp between midnight, January 1, 1970 and sometime in 2037. You can define multiple lengths to the TIMESTAMP field, which directly correlate to what is stored in it. The default length for TIMESTAMP is 14, which stores YYYYMMDDHHMMSS. This looks like the DATETIME format, only without the hyphens between numbers; 3:30 in the afternoon on December 30th, 1973 would be stored as 19731230153000. Other definitions of TIMESTAMP are 12 (YYMMDDHHMMSS), 8 (YYYYMMDD), and 6 (YYMMDD).
CHAR	A fixed-length string between 1 and 255 characters in length, right-padded with spaces to the specified length when stored. Defining a length is not required, but the default is 1.
VARCHAR	A variable-length string between 1 and 255 characters in length. You must define a length when creating a VARCHAR field.
BLOB or TEXT	A field with a maximum length of 65535 characters. BLOBs are "Binary Large Objects" and are used to store large amounts of binary data, such as images or other types of files. Fields defined as TEXT also hold large amounts of data; the difference between the two is that sorts and comparisons on stored data are case sensitive on BLOBs and case insensitive in TEXT fields. You do not specify a length with BLOB or TEXT.
ENUM	An enumeration (list). When defining an ENUM, you are creating a list of items from which the value must be selected (or it can be NULL). For example, if you wanted your field to contain either "A" or "B" or "C", you would define your ENUM as ENUM ('A', 'B', 'C') and only those values (or NULL) could ever populate that field. ENUMs can have 65535 different values.

Defining Your Fields

The overall goal of this chapter is to create a table to hold data from your own personal music collection. Take a moment to think about the kinds of things you'd want to know: the title and artist, obviously, and maybe the record label, the date it was acquired, and your own personal notes regarding the recording. I thought about what I wanted for my own table, which I've decided to call my_music, as shown in Table 12.2.

Table 12.2: Fields for my_music
Field Name	Description
id	Creates a unique ID number for the entry
format	Is it a CD, cassette, or even an LP?
title	The title of the recording
artist_fn	The artist's first name
artist_ln	The artist's last name or the name of the group
rec_label	The record label
my_notes	My own thoughts about the recording
date_acq	Date acquired

In the next section, you create a sequence of forms that will take your table information and send it to your MySQL database. In the first step, you submit the name of the table and the number of fields you want to include. The second step will display additional form fields so that you can define the properties of your table columns. A third step will send the request to MySQL, verify that the table was created, and display a "Success!" message.

The Importance of Unique Fields

Using unique ID numbers not only helps you keep track of your data, but also down the road, helps you attempt to establish relationships between multiple tables. In the my_music table, there will be an ID field. Using this field as the unique field, instead of the title field, will allow you to have two recordings in your table that have the same name. For example, if you own the album Strange Fire by the Indigo Girls, you could have two entries in your table: one for the version released in 1987, and one for the version re-released in 1989 (just trust me on that one).

Without using a unique identifier, you would have to pick only one version to put in your table, and your table wouldn't be very accurate. I hope this simple example conveys the importance of having a unique identifier in each record in your table. The usage of the unique identifier will become more apparent throughout the remainder of this book, as you create more database-driven elements.