The Sample Music Database | 213

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) DEFAULT NULL,
artist_id SMALLINT(5) NOT NULL DEFAULT 0,
album_id SMALLINT(4) NOT NULL DEFAULT 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 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 beset 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, whenever we play a track, we create anew 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 acceptable to have more than one entry fora 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 instructions to enforce this in any application that uses this database.

Download 4.24 Mb.

Share with your friends: