Creating our first table

Now that we have a new database, it's time to create a table in it. The example table we will create is named book.

Choosing the fields

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:

  • International Standard Book Number (ISBN)
  • Title
  • Number of pages
  • Author identification

For now, it's not important to have the complete list of fields (or columns) for our book table. We will modify it by prototyping the application 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 fields. We enter this information in the Create new table dialog and click on Go to start creating the table. At this point it does not matter if the number of fields is exactly known, as a subsequent panel will allow the addition of fields when creating the table:

Creating a table

We then see a panel specifying field information. As we asked for 4 fields, we get four rows. Each row refers to information specific to one field. The following image represents the left side for this panel:

Creating a table

And the next one represents the right side:

Creating a table

Had we asked to create a table with less than four fields, the display would be oriented vertically—we'll see an example of that at the end of the chapter.

The MySQL documentation explains the valid characters for the table and field names (if we search for Legal names). This may vary depending on the MySQL version. Usually, any character that is allowed in a filename (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 field names as well, but we can use any character.

We enter our field names in the Field column. Each field 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 field 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 the page. For the page count and the author identification, we have chosen INT type (integer), as depicted in the following screenshot:

Creating a table

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

Choosing keys

A table should normally have a primary key (a field 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 field. Other possibilities for index type include INDEX, UNIQUE, and FULLTEXT (more on this in Chapter 5,Changing Data and Structure).

Note

Index management (also referred to as Key management) can be done at initial table creation, or later in the Structure subpage of Table view.

To improve the speed of the queries that we will make by author ID, we should add an index on this field. The rightmost part of our screen now looks like this:

Choosing keys

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 subpage of the Table view:

Choosing keys

Of the various tabs leading to other subpages, some are not active, because it would not make sense to browse or search a table if there are no rows in it. However, it would be acceptable to export a table, as we can export a table's structure even if it contains no data.