Creating Tables

Now that you have a database, you can start to design tables that both model the library data and act as containers for it.

First, set the database within which you will create the tables using the USE command.

USE library; 

Designing Tables

Designing tables is a difficult skill to teach. Although there are principles to guide the design of tables, the applicability of the principles can be limited by performance and storage requirements. This appendix cannot teach you everything, but it at least points you in the right direction for creating your own tables.

Jumping Right In

One of the easiest ways to learn about table design is to follow along with someone designing a set of tables. This appendix walks you through the steps of designing your table.

The first thing to do is think about what information you need to store. In the case of this little library, you need to store information about

• The books that you have (book title, author, and condition)

• The people who borrow books (name and email address)

• The book loans that you have made (which book was loaned to which person at what time)

You could store much more information in your tables, but this will do for illustrative purposes.

A naïve table design for this data is a table that contains a column for each piece of information: book title, book author, book condition, borrower name, borrower email, and so forth.

One major problem with this design is that there is a large amount of duplicated data. Every time you lend out In the Night Kitchen, for example, you would have to record all of the data on the book in the table. This ends up being tremendously inefficient, both in terms of storage and processing effort. In some cases, duplication is unavoidable (or even desirable), but in most cases the duplication is clearly a waste.

To address this redundancy, you will place the most commonly duplicated sets of data (books and borrowers) in their own tables. You will also put loans into their own table that includes columns that reference entries in the book and person tables.

Designing Your First Table

The first table is going to contain information about the title, author, and condition of each book. Each of these pieces of data will be stored in its own column, while each row will contain a full set of information about a book.

Choosing a Primary Key

Your first choice is to determine if you want to use one of your candidate keys for your primary key or if you want to create a synthetic primary key. A candidate key is a column or set of columns that can uniquely identify a row within a table. A primary key is a candidate key that has been chosen (or created) as the candidate key that best identifies a row.

You have four possible candidate keys:

• The book title

• The book title combined with the author's name

• The book title combined with the book's condition

• The book title combined with both the author's name and the book's condition

The last candidate key is the most accurate, but it still does not allow you to safely have multiple copies of a book, and it isn't even remotely usable, given its length.

If you use a synthetic value as a primary key, you can simply assign a number to each row. In the case of the book table, this requires much less storage space than using the candidate key. In addition, by using a synthetic primary key, you can modify all the data values in the row without worrying about other tables that might be using the primary key as a foreign key.

Defining Columns

Each column in the table has a name and is defined according to the type of data it contains and the maximum length (and, in some cases, the format) of the data it can store. You're going to use a synthetic value as the primary key in your book table, so this column for the primary key will be one of the integer data types.

Before you choose the exact column type, you need to know the rough range of numbers you want to store in the column. In the case of your library, you can arbitrarily decide that you won't have more than 50,000 books. This allows you to use an UNSIGNED SMALLINT column (which can store integers between 0 and 65,535) for storing your primary key.

The columns that store the book title and book author data will contain character data rather than numeric data. MySQL has three column data types for character data: CHAR and VARCHAR for storing strings between 0 and 255 characters, and various sizes of TEXT columns for storing what are usually larger amounts of data.

For both book title and book author, use a VARCHAR column and set the column width to 255 characters. That should give you enough space for almost any author or book name.

The drawback of this approach is that indexes for the VARCHAR column must be larger to accommodate the maximum possible width for a column. You can eliminate this penalty by forcing your indexes to only use part of the column. This is explained in the "Exploring Table Creation" section later in this chapter.

Finally, for the condition column, you need to decide how you want to denote the condition of a book. You could use an integer flag, where 0 stands for perfect condition, 1 stands for normal, and so on. You could go a step further and put the condition data into a separate table. Although this would reduce the amount of duplication, it seems excessive.

For cases like this, MySQL has a column type called ENUM. An ENUM-type column can contain any single value from a list of predefined values. The values stored in an ENUM column look like strings but are actually stored as numeric data. This strategy allows ENUMs to be very storage efficient. You will define an ENUM column to hold your condition data that can hold values of mint, fine, good, or poor.

Now that you've determined the data types required by each of your columns, you'll use SQL commands to create them.

Naming Columns

When naming columns, keep the following guidelines in mind:

• Always use lower case in identifiers.

• Name the table's primary key table_id and use the same name for foreign keys in other tables that reference this table's primary key. This allows you to easily search your SQL queries for all the references to a given table's primary key.

• Name the columns in a semantic manner. Give them titles that refer to their contents. Do not use SQL keywords such as date or index as titles.

• Use underscores to separate words in column names.

• Choose a convention for singular or plural names and then stick to it.

• Avoid using reserved words (such as condition) for column names.

So, with that information in mind, you'll create a table called book with the following columns:

• book_id

• title

• author

• cond

Creating the book Table

To create the book table, run the following command:

CREATE TABLE book (   book_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,   title   VARCHAR(255),   author  VARCHAR(255),   cond    ENUM('poor','good','fine','mint'),           PRIMARY KEY (book_id) ); 

Because this is a relatively complex query, it is discussed in detail in the "Exploring Table Creation" section later in this chapter. This gives you the chance to move on to more commonly used tasks more quickly. Note that the exact amount of whitespace (tabs, new lines, or spaces) within most of a query is not semantically importantthat is, it doesn't mean anything to the MySQL server. To the server, all that matters is that at least one character of whitespace is placed between separate words. Because of MySQL's flexibility in this regard, it is easy to construct SQL queries that are easy for both humans and MySQL to read. The exception to this behavior is within string literals. 'lib ary', for instance, is quite different from 'library'.

Creating the person Table

In addition to your table for storing book information, you also need tables to store information on people who are borrowing books, as well as the book loans that are made.

For the person table, you need columns to store the person's name and email address. Once again, you need a primary key so you can refer to a given row in the table from another table. Again, use a synthetic primary key, as the available candidate key (a combination of the person's name and email address) is too long to be practical.

When choosing the field type for your primary key, you need to estimate the number of people who are likely to borrow books from your library. Again, you can arbitrarily define your values, saying that, at most, a few hundred people might borrow books. You can easily store this range within an UNSIGNED TINYINT column (which can store integers between 0 and 255).

The name and email columns are similar to the title and author columns in the book table. You can set them to VARCHAR(255).

The query for creating the table could look like this:

CREATE TABLE person (   person_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,   name      VARCHAR(255)             COMMENT "The person's name",   email     VARCHAR(255)             COMMENT "The person's email address",             PRIMARY KEY (person_id) ) COMMENT "Basic information about book borrowers"; 

Although this table is similar to the previous table, note that COMMENT keywords are used here to add explanatory information about the table and individual columns. Even though the comments don't add much value here, comments can provide valuable information to help people understand what a given table or column is for and why a table has been designed in a certain way.

Creating the loan Table

For the loan table you need to correlate a date to an entry in the book table and an entry in the person table. You will need columns for storing

• A primary key for the table (that, once again, will be synthetic)

• A primary key from the book table

• A primary key from the person table

• The date when you lent a book to someone

The resulting table creation statement could look something like this:

CREATE TABLE loan (   loan_id   INT UNSIGNED NOT NULL AUTO_INCREMENT,   person_id TINYINT UNSIGNED NOT NULL,   book_id   SMALLINT UNSIGNED NOT NULL,   date_lent DATE NOT NULL,             PRIMARY KEY (loan_id) ) COMMENT "Store info on book loans"; 

You should be familiar with each of the column types used here, with the exception of DATE. DATE columns, as implied by the name, can store dates between '1000-01-01' to '9999-12-31' (in the Gregorian calendar). When inserting a date into a DATE column, you can use a YYYY-MM-DD or YYYYMMDD notationfor example, July 25, 2005 could be written as '2005-07-25' or 20050725.