Finalizing a Table's Design

The final step in designing your database is to adhere to certain naming conventions. While MySQL is very flexible on how you name your databases, tables, and columns, here are some good rules to go by (required rules are in bold):

• Use alphanumeric characters.

• Do not use spaces.

• Limit yourself to less than 64 characters.

• Field names should be descriptive.

• Field names should be unique across every table, except for the keys.

• Do not use existing MySQL keywords.

• Use the underscore (_) to separate words.

• Use entirely lowercase words (this is definitely a personal preference rather than a rule).

• Use plural table names (to indicate multiple values stored) and singular column names.

• End primary and foreign key columns with id (or ID).

• List the primary key first in a table, followed by foreign keys.

These are largely my recommendations and are therefore not absolute, except for limiting yourself to alphanumeric names without spaces. Some developers prefer to use capital letters to break up words (instead of underscores). Some developers like to indicate the column's type in its name. The most important rule is that you remain consistent with the conventions you abide by.

Table 4.7 shows the final database design, after following these next steps.

To finalize your database design:

Table 4.7. The final database design step incorporates certain naming conventions and orders the columns within each table.

Accounting Database, Finalized
Column Name	Table	Column Type
invoice_id	invoices	SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT
client_id	invoices	SMALLINT(3) UNSIGNED NOT NULL
invoice_date	invoices	TIMESTAMP NOT NULL
invoice_amount	invoices	DECIMAL(10,2) UNSIGNED NOT NULL
invoice_description	invoices	TINYTEXT NOT NULL
date_invoice_paid	invoices	DATE
client_id	clients	SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT
client_name	clients	VARCHAR(40) NOT NULL
client_street	clients	VARCHAR(80) NOT NULL
client_city	clients	VARCHAR(30) NOT NULL
client_state	clients	CHAR(2) NOT NULL
client_zip	clients	MEDIUMINT(5) UNSIGNED ZEROFILL NOT NULL
client_phone	clients	VARCHAR(14)
contact_name	clients	VARCHAR(40)
contact_email	clients	VARCHAR(60)
expense_id	expenses	SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT
expense_category_id	expenses	TINYINT(3) UNSIGNED NOT NULL
expense_amount	expenses	DECIMAL(10,2) UNSIGNED NOT NULL
expense_description	expenses	TINYTEXT NOT NULL
expense_date	expenses	TIMESTAMP NOT NULL
expense_category_id	expense_categories	TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT
expense_category	expense_categories	VARCHAR(30) NOT NULL