Now that you understand the different types of data that fields can take, it is time to introduce tables. As defined in the Schemas and tables section, a table is a collection of fields that defines a type of information. You could compare it with OOP and think of tables as classes, fields being their properties. Each instance of the class would be a row on the table.

When defining a table, you have to declare the list of fields that the table contains. For each field, you need to specify its name, its type, and some extra information depending on the type of the field. The most common are:

Table definitions also need a name, as with schemas, and some optional attributes. You can define the charset of the table or its engine. Engines can be a quite large topic to cover, but for the scope of this chapter, let's just note that we should use the InnoDB engine if we need strong relationships between tables. For more advanced readers, you can read more about MySQL engines at https://dev.mysql.com/doc/refman/5.0/en/storage-engines.html.

Knowing this, let's try to create a table that will keep our books. The name of the table should be book, as each row will define a book. The fields could have the same properties the Book class has. Let's take a look at how the query to construct the table would look:

mysql> CREATE TABLE book(
    -> isbn CHAR(13) NOT NULL,
    -> title VARCHAR(255) NOT NULL,
    -> author VARCHAR(255) NOT NULL,
    -> stock SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    -> price FLOAT UNSIGNED
    -> ) ENGINE=InnoDb;
Query OK, 0 rows affected (0.01 sec)

As you can note, we can add more new lines until we end the query with a semicolon. With this, we can format the query in a way that looks more readable. MySQL will let us know that we are still writing the same query showing the -> prompt. As this table contains five fields, it is very likely that we will need to refresh our minds from time to time as we will forget them. In order to display the structure of the table, you could use the DESC command, as follows:

mysql> DESC book;
+--------+----------------------+------+-----+---------+-------+
| Field  | Type                 | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+-------+
| isbn   | char(13)             | NO   |     | NULL    |       |
| title  | varchar(255)         | NO   |     | NULL    |       |
| author | varchar(255)         | NO   |     | NULL    |       |
| stock  | smallint(5) unsigned | NO   |     | 0       |       |
| price  | float unsigned       | YES  |     | NULL    |       |
+--------+----------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

We used SMALLINT for stock as it is very unlikely that we will have more than thousands of copies of the same book. As we know that ISBN is 13 characters long, we enforced this when defining the field. Finally, both stock and price are unsigned as negative values do not make sense. Let's now create our customer table via the following script:

mysql> CREATE TABLE customer(
    -> id INT UNSIGNED NOT NULL,
    -> firstname VARCHAR(255) NOT NULL,
    -> surname VARCHAR(255) NOT NULL,
    -> email VARCHAR(255) NOT NULL,
    -> type ENUM('basic', 'premium')
    -> ) ENGINE=InnoDb;
Query OK, 0 rows affected (0.00 sec)

We already anticipated the use of enum for the field type as when designing classes, we could draw a diagram identifying the content of our database. On this, we could show the tables and their fields. Let's take a look at how the diagram of tables would look so far:

Note that even if we create tables similar to our classes, we will not create a table for Person. The reason is that databases store data, and there isn't any data that we could store for this class as the customer table already contains everything we need. Also, sometimes, we may create tables that do not exist as classes on our code, so the class-table relationship is a very flexible one.