Naming Tables and Columns

Now let's look at some of the database design issues to considerwhen creating tables in MySQL.

The names you give your tables and their columns are importantchoose them carefully. First and foremost, the names you use should be clear identifiers. Table name should indicate what data is being stored, and column names should identify what their values represent.

For example, the products table does exactly what you would expect: It contains details of the products in the database. The purposes of columns named weight and price in this table are obvious as well.

You can adopt many different naming conventions. No one convention in particular is correct, and the one you choose to use will be a matter of preference, but you should try to retain a consistent namingscheme throughout your database.

In the sample database, table names are always plural (customers, products, and so on), but you might prefer to use singular nouns (such as customer and product) as the table names.

For both table names and column names, the sample database uses an underscore character to separate words, as in the order_lines table or the product_code column. Another popular convention is to capitalize the first letter of each word, as in ProductCode or sometimes productCode.

Case Sensitivity

Table names in MySQL are usually case sensitive, so you must always use the same capitalization when referencing tables. On Windows systems and other platforms where the directory names on the file system are not case sensitive, table names are not case sensitive.

Column names are not case sensitive on any platform, but you should still try to keep a consistent capitalization to maintain readable code.

Sometimes you will come across systems in which the table identifiers contain a prefix or suffix to indicate that they are, in fact, a table, such as tblProducts or products_tbl. Other types of database objects might have their own naming scheme, such as an idx prefix or suffix for indexes. Columnnames might even contain a reference to the table name, such as prodWeight. These conventions sometimes aid the readability of an SQL statement by ensuring that the purpose of an identifier is absolutely clear.

Database and table identifier names can contain any character except ., \, and /. Because MySQL writes tables to disk using the names you provide, characters that can have special meanings in a filename are disallowed. A column name may use any character.

An identifier must be quoted if it contains a symbol character other than an underscore or is a reserved word. For a list of reserved words, refer to Appendix F, "MySQL Reserved Words," which can be found on the book's website at quote an identifier by using the backtick (`) character.

Forexample, the following CREATE TABLE statement is perfectly validin practice, however, the poor choice of identifier names would render it unusable:

 CREATE TABLE `table` (   `a number` INT,   `price$`   DECIMAL(6,2),   `em@il`    TEXT,   `varchar`  VARCHAR(1),   `()`       INT ); 

Sams Teach Yourself MySQL in 10 Minutes
Sams Teach Yourself MySQL in 10 Minutes
ISBN: 0672328631
EAN: 2147483647
Year: 2006
Pages: 165
Authors: Chris Newman

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: