Working with Database Structures - Learning MySQL - page 213

The next section of the file creates the tables (the third step), and that's the focus of
this section; we don't list the insert statements in this topic, but they're easily viewed
in music.sql . Let's start by looking at how we created the artist table:
CREATE TABLE artist (
artist_id SMALLINT(5) NOT NULL DEFAULT 0,
artist_name CHAR(128) DEFAULT NULL,
PRIMARY KEY (artist_id)
);
The table has a structure that's derived from the design in Chapter 4. The artist_id is
the primary key; because of this, and as required by MySQL, we've added a NOT NULL
clause. The DEFAULT clause inserts a default value for the column if we don't provide
one. If a field doesn't have a default value, MySQL reports an error if a value isn't
provided for it during an insert operation. In the artist table, the artist_id will be set
to 0 if we don't provide an artist_id ourselves. MySQL will complain the second time
we try to do this, since artist_id is the primary key of the artist table, and we can't
have two rows with the same primary key.
We've used the SMALLINT type for the artist_id because it's a numeric identifier, and
a SMALLINT allows us to have around 65,000 artists; we've limited its display width to
5 characters.
We've decided that 128 characters is more than we'd need for any likely artist_name .
We use the CHAR type instead of the VARCHAR type so that each row has an fixed, pre-
dictable size; this allows MySQL to better optimize the retrieval of rows from its files,
typically making the application faster despite the files being typically larger than if
VARCHAR was used. We haven't added a NOT NULL clause to the artist_name , and have
instead assumed that whatever application we build will do the checking for us. In
general, the fewer the constraints and conditions that are built into the database, the
faster it is for MySQL to work with. However, MySQL now optimizes for NOT NULL
columns, so it is better to declare NOT NULL where the data will never be NULL. See
the "Data Size" section of the MySQL manual for details.
The album table follows a similar rationale:
CREATE TABLE album (
artist_id SMALLINT(5) NOT NULL DEFAULT 0,
album_id SMALLINT(4) NOT NULL DEFAULT 0,
album_name CHAR(128) DEFAULT NULL,
PRIMARY KEY (artist_id,album_id)
);
We've declared the artist_id to be the same type as in artist . This is important as
otherwise MySQL couldn't use indexes to join tables together to resolve queries (which
is a very common cause of odd results in EXPLAIN output). We've used SMALLINT for the
album_id , since we don't expect more than 65,000 albums per artist! We define
album_name as a CHAR(128) because 128 characters seems long enough for album titles.