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.
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 www.samspublishing.com.You 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 );