Working with Database Structures - Learning MySQL - page 214

Again, we've added NOT NULL for the primary key, added DEFAULT clauses to make the
behavior predictable, and gone with only fixed-length types to improve performance.
The track table is created as follows:
CREATE TABLE track (
track_id SMALLINT(3) NOT NULL DEFAULT 0,
track_name CHAR(128) DEFAULT NULL,
artist_id SMALLINT(5) NOT NULL DEFAULT 0,
album_id SMALLINT(4) NOT NULL DEFAULT 0,
time TIME DEFAULT NULL,
PRIMARY KEY (artist_id,album_id,track_id)
);
The reasoning behind the choices for the first four columns is the same as for the other
tables. The time column stores the duration of each track, and we've chosen to use the
TIME type to store this. Using the TIME type—in preference to a numeric type such as
DECIMAL —makes it easy to do math such as summing values to find the running time
for an album. It also gives you flexibility in formats for the time data, as discussed
previously. Despite this, you'll see that in music.sql we use the format HH:MM:SS because
we prefer to keep SQL queries readable and unambiguous.
The final table is played :
CREATE TABLE played (
artist_id SMALLINT(5) NOT NULL DEFAULT 0,
album_id SMALLINT(4) NOT NULL DEFAULT 0,
track_id SMALLINT(3) NOT NULL DEFAULT 0,
played TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (artist_id,album_id,track_id,played)
);
The choices for the first three columns are again as previously described. The played
column makes use of the TIMESTAMP type and its automatic-update feature: we want the
value to be set to the current date and time whenever a row is inserted (and, for good
measure, whenever it's updated, which we don't plan to do). To use the feature, when-
ever we play a track, we create a new row with the artist_id , album_id , and track_id ,
and set the played column to NULL . Since all columns form the primary key, it's accept-
able to have more than one entry for a specific combination of artist, album, and track,
as long as the timestamps aren't the same. We can reasonably assume that two tracks
won't be played at the same time in a single-user application, and can also add instruc-
tions to enforce this in any application that uses this database.
Altering Structures
We've shown you all the basics you need for creating databases, tables, indexes, and
columns. In this section, you'll learn how to add, remove, and change columns, data-
bases, tables, and indexes in structures that already exist.