Choosing the columns

Before creating a table, we should plan the information we want to store. This is usually done during database design. In our case, a simple analysis leads us to the following book-related data we want to keep:

For now, it is not important to have the complete list of columns for our book table. We will modify it by prototyping the structure now and refining it later. At the end of the chapter, we will add a second table, author, containing information about each author.

Creating a table

We have chosen our table name and we know the number of columns. We enter this information in the Create table dialog and click on Go to start creating the table. At this point, it does not matter if the number of columns is exactly known, as a subsequent panel will permit us to add columns while creating the table.

We then see a panel specifying column information. As we asked for four columns, we get four input rows. Each row refers to information specific to one column. The following screenshot represents the left side of this panel:

And the next one represents the right side:

The MySQL documentation explains valid characters for the table and column names (if we search for "Legal names"). This may vary depending on the MySQL version. Usually, any character that is allowed in a file name (except the dot and the slash) is acceptable in a table name, and the length of the name must not exceed 64 characters. The 64-character limit exists for column names as well, but we can use any character.

We enter our column names under the Column column. Each column has a type, and the most commonly used types are located at the beginning of the drop-down list.

The VARCHAR (variable character) type is widely used when the column content is alphanumeric, because the contents will occupy only the space needed for it. This type requires a maximum length, which we specify. If we forget to do so, a small pop-up message reminds us later when we save. For the page count and the author identification, we have chosen INT type (integer), as depicted in the following screenshot:

There are other attributes for columns, but we will leave them empty in this example. You might notice the Add 1 column(s) dialog at the bottom of the screen. We can use it to add some columns to this table-creation panel by entering the appropriate value and hitting Go. The number of input rows would change according to the new number of columns, leaving intact the information already entered about the first four columns. Before saving the page, let us define some keys.

Choosing keys

A table should normally have a primary key (a column with unique content that represents each row). Having a primary key is recommended for row identification, better performance, and possible cross-table relations. A good value here is the ISBN; so, in the Index dialog we select PRIMARY for the isbn column. Other possibilities for index type include INDEX, UNIQUE, and FULLTEXT (more on this in Chapter 5).

To improve the speed of the queries that we will make by author_id, we should add an index on this column. The right part of our screen now looks as shown in the following screenshot:

At this point, we could pick a different Storage Engine from the corresponding drop-down menu. However, for the time being, we will just accept the default storage engine.

Now we are ready to create the table by clicking on Save. If all goes well, the next screen confirms that the table has been created; we are now in the Structure page of the current database.

Of the various links shown for the book table, some are not active, because it would not make sense to browse or search a table if there are no rows in it.