This chapter is from the book
This chapter is from the book
This chapter is from the book
Final Design Steps
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 (some of which are required):
Use alphanumeric characters.
Limit yourself to less than 64 characters (this is a MySQL restriction).
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.
Field names should be descriptive.
Field names should be unique across every table, except for the 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). Others like to indicate the column type in its name. The most important consideration is that you remain consistent with your conventions.
Table 3.6 shows the final database design, which will be created in the next chapter.
Table 3.6 The final database design step incorporates certain naming conventions that I try to adhere to.
accounting |
||
|
Column Name |
Table |
Column Type |
|
invoice_id |
invoices |
SMALLINT(4) UNSIGNED NOT NULL DEFAULT 0 |
|
client_id |
invoices |
SMALLINT(3) UNSIGNED |
|
invoice_date |
invoices |
DATE NOT NULL |
|
invoice_amount |
invoices |
DECIMAL(10,2) UNSIGNED NOT NULL |
|
invoice_description |
invoices |
TINYTEXT |
|
client_id |
clients |
SMALLINT(3) UNSIGNED NOT NULL DEFAULT 0 |
|
client_name |
clients |
VARCHAR(40) NOT NULL |
|
client_street |
clients |
VARCHAR(80) |
|
client_city |
clients |
VARCHAR(30) |
|
client_state |
clients |
CHAR(2) |
|
client_zip |
clients |
MEDIUMINT(5) UNSIGNED |
|
client_phone |
clients |
VARCHAR(14) |
|
contact_name |
clients |
VARCHAR(40) |
|
contact_email |
clients |
VARCHAR(60) |
|
expense_id |
expenses |
SMALLINT(4) UNSIGNED NOT NULL DEFAULT 0 |
|
expense_category_id |
expenses |
TINYINT(3) UNSIGNED |
|
expense_amount |
Expenses |
DECIMAL(10,2) UNSIGNED NOT NULL |
|
expense_description |
expenses |
TINYTEXT |
|
expense_date |
Expenses |
DATE |
|
expense_category_id |
expense_categories |
TINYINT(3) UNSIGNED |
|
expense_category |
expense_categories |
VARCHAR(30) |
Tips
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 6, 7, and 8.