- Naming Database Elements
- Choosing Your Column Types
- Choosing Other Column Properties
- Accessing MySQL
- Review and Pursue
This chapter is from the book
This chapter is from the book
This chapter is from the book
Choosing Other Column Properties
Besides deciding what data types and sizes you should use for your columns, consider a handful of other properties.
First, every column, regardless of type, can be defined as NOT NULL. The NULL value, in databases and programming, is equivalent to saying that the field has no known value. Ideally, in a properly designed database, every column of every row in every table should have a value, but that isn’t always the case. To force a field to have a value, 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 column, regardless of 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).
subscribe ENUM('Yes', 'No') default 'No'
With the subscribe column, if no value is specified when adding a record, the default will be used.
If a column cannot be NULL and does not have a default value, and no value is specified for a new record, that field will be given a default value based on its type. For numeric types, the default value is 0. For most date and time types, the type’s version of “zero” will be the default (e.g., 0000-00-00). The first TIMESTAMP column in a table will have a default value of the current date and time. String types use an empty string (‘’) as the default value, except for ENUM, whose default value—again, if not otherwise specified—is the first possible enumerated value (Yes in the previous example).
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 (see Table 4.2). 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 6 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:
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 unique to you (your home or mobile phone).
In the users table, user_id will be the primary key: an arbitrary number used to refer to a row of data. Again, Chapter 6 will go into the concept of primary keys in more detail.
Identify which columns cannot have a NULL value.
In this example, every field is required (cannot be NULL). As an example of a column that could have NULL values, if you stored people’s addresses, you might have address_line1 and address_line2, with the latter one being optional. In general, tables that have a lot of NULL values suggest a poor design (more on this in…you guessed it…Chapter 6).
Make any numeric type UNSIGNED if it won’t ever store negative numbers.
user_id, which will be a number, should be UNSIGNED so that it’s always positive. As a rule, primary keys should always be unsigned. Other examples of UNSIGNED numbers would be the price of items in an e-commerce example, a telephone extension for a business, or a zip code.
Establish the default value for any column.
None of the columns here logically implies a default value.
Confirm the final column definitions (Table 4.6).
Before creating the tables, you should revisit the type and range of data you’ll store to make sure that your database effectively accounts for everything.
Table 4.6 users Table
Column Name
Type
user_id
MEDIUMINT UNSIGNED NOT NULL
first_name
VARCHAR(20) NOT NULL
last_name
VARCHAR(40) NOT NULL
email
VARCHAR(60) NOT NULL
pass
CHAR(128) NOT NULL
registration_date
DATETIME NOT NULL