Choosing Other Column Properties

Besides deciding what data types and sizes you should use for your columns, you should consider a handful of other properties.

First, every type can also be set as NOT NULL. The NULL value, in databases and programming, is equivalent to saying that the field has no value. Ideally, every field of every record in a database should have a value, but that is rarely the case in reality. To force a field to have a value, you add the NOT NULL description to its column type. For example, a required dollar amount can be described as

 cost DECIMAL(5,2) NOT NULL 

When creating a table, you can also specify a default value for any type. In cases where a majority of the records will have the same value for a column, presetting a default will save you from having to specify a value when inserting new rows (unless that row's value for that column is different from the norm).

 gender ENUM('M', 'F') default 'F' 

If no value is specified for a column when adding a record, the default will be used if set or an error will occur if the column is defined as NOT NULL.

The number types can be marked as UNSIGNED, which limits the stored data to positive numbers and zero. This also effectively doubles the range of positive numbers that can be stored (because no negative numbers will be kept). You can also flag the number types as ZEROFILL, which means that any extra room will be padded with zeros (ZEROFILLs are also automatically UNSIGNED).

Finally, when designing a database, you'll need to consider creating indexes, adding keys, and using the AUTO_INCREMENT property. Chapter 5 discusses these concepts in greater detail, but in the meantime, check out the sidebar "Indexes, Keys, and AUTO_INCREMENT" to learn how they affect the users table.

To finish defining your columns:

1.

Identify your primary key.

The primary key is quixotically both arbitrary and critically important. Almost always a number value, the primary key is a unique way to refer to a particular record. For example, your phone number has no inherent value but is uniquely a way to reach you.

In the users table, the user_id will be the primary key: an arbitrary number used to refer to a row of data.

2.

Identify which columns cannot have a NULL value.

In this example, every field is required (cannot be NULL). If you stored peoples' addresses, by contrast, you might have address_line1 and address_line2, with the latter one being optional.

3.

Make any numeric type UNSIGNED if it won't ever store negative numbers.

The user_id, which will be a number, is UNSIGNED so that it's always positive.

4.

Establish the default value for any column.

None of the columns here logically implies a default value.

5.

Confirm the final column definitions (Table 4.6).

Table 4.6. The final description of the users table. Also remember that the user_id will be marked as an auto-incremented primary key.

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

Before creating your tables, you should revisit the type and range of data you'll store to make sure that your database effectively accounts for everything.

Indexes, Keys, and AUTO_INCREMENT

Two concepts closely related to database design are indexes and keys. An index in a database is a way of requesting that the database keep an eye on the values of a specific column or combination of columns (loosely stated). The end result of this is improved performance when retrieving records but slightly hindered performance when inserting or updating them.

A key in a database table is integral to the normalization process used for designing more complicated databases. There are two types of keys: primary and foreign. Each table should have one primary key, and as you'll discover in the next chapter, the primary key in a table is often linked as a foreign key in another.

A table's primary key is an artificial way to refer to a record and should abide by three rules:

1. It must always have a value.

2. That value must never change.

3. That value must be unique for each record in the table.

In the users table, the user_id will be designated as a PRIMARY KEY, which is both a description of the column and an indication to MySQL to index it. Since the user_id is a number (which primary keys almost always will be), I'll also add the AUTO_INCREMENT description to the column, which tells MySQL to use the next-highest number as the user_id value for each added record. You'll see what this means in practice when you begin inserting records.