Publishers of technology books, eBooks, and videos for creative people

Home > Articles > Web Design & Development

  • Print
  • + Share This
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.

  • + Share This
  • 🔖 Save To Your Account