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
|
|
1.
|
Determine the whole database's name. This should be easy to remember and descriptive. The database name must also be unique, so no other database on the same MySQL server can have the same name.
For the example I've been using in this and the
preceding
chapter, the actual database name will be
accounting
. You could instead use
Accounting
, but I prefer to always use all-lowercase names.
|
|
|
|
|
2.
|
Identify each table's name.
Again, these should be easy to remember and descriptive. Furthermore, no two tables in the same database can have the same name (two tables in two different databases can have the same name, though). I'm going with
clients
,
invoices
,
expenses
, and
expense_categories
.
|
|
3.
|
Label each column within each table.
You'll see a lot of variations here, with everyone having their own style. As I mentioned, I'll add
_id
to any primary or foreign key column. If there is a date field, I tend to put the word
date
in its name, but for no required reason.
|
|
4.
|
Order the columns within each table.
The results of this step are more for your own organization than anything. The order of the columns will have
absolutely
no impact on the functionality of the table or database. I prefer to put the primary key column first, followed by the foreign keys.
|
Tips
-
If you give
related
tables names that begin similarly, they'll appear together when you list all tables in a database. For example,
expenses
and
expense_categories
are together in
Figure 4.1)
.
-
Database and table names are case-sensitive on Unix systems but insensitive under Windows. Column names are always case-insensitive.
-
By
strictly
adhering to any set of database design principles, you minimize errors that could occur when programming a database interface, as you will in Chapters 79.
-
You can technically use an existing keyword for a table or column name. But to then refer to that item, you would need to always quote its name using backticks:
SELECT * FROM `table`
Even so, I think it's best not to use existing keywords.
|
