Finalizing a Table s Design


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).

    Figure 4.1. Tables are listed in alphabetical order; you can use this to your benefit by giving related tables similar names.

  • 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.





MySQL Visual QuickStart Guide Serie  .Covers My SQL 4 and 5
MySQL, Second Edition
ISBN: 0321375734
EAN: 2147483647
Year: 2006
Pages: 162
Authors: Larry Ullman

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net