Learning Mysql
The Sample Music Database
Download 4.24 Mb. View original pdf
|
- Navigate this page:
- 212 | Chapter 6: Working with Database Structures
| The Sample Music Database We’ve used the music database extensively in this and the previous chapter, so you’re already familiar with its structure. This section explains the steps we took to express our sample music database as SQL statements for loading into MySQL. It also lists the complete SQL statements used to create the structures, which you’ll find a useful reference for discussions in later chapters. Let’s begin by discussing how we structured the file that contains the SQL statements. You can download the file music.sql from the book’s website. We created the table using the monitor, and created the file from the output of one of MySQL’s commands for dumping SQL, and then edited it for readability. You’ll find more about how to dump SQL statements to a file in Chapter The music.sql file is structured as follows. Drop the database if it exists, and then create it. Use the database. Create the tables. Insert the data. This structure allows you to reload the database—using the SOURCE command discussed in Chapter at anytime without having to worry about whether the database, tables, or data exist. Loading the file just wipes the database and starts again. Of course, in a production environment, always ensure your backups are reasonably up-to-date before commencing a restore operation that involves dropping tables or deleting existing data. The first three lines of the file carryout the first two steps: DROP DATABASE IF EXISTS music; CREATE DATABASE music; USE music; 212 | Chapter 6: Working with Database Structures 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 book, 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) 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 afield 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 beset 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 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, predictable 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. Seethe "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) 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. Download 4.24 Mb. Share with your friends:
|