Having explored the basics of database design it’s time to move on to creating tables and, from there, looking at how we get the data out again using SQL queries. There are plenty of ways to go about this, including accessing the database via a command line prompt and a dedicated GUI – either last month’s Control Center, or a standalone tool like phpMyAdmin.

Much as programming begins with Hello World, it’s traditional to start a look at databases with a simple collection – books, CDs, MP3 files: you’re dealing with the same basic database, just slightly different field names. We kick off with three key tables: books, authors and publishers. As discussed in our look at the three normal forms, we always want to separate our data out as much as possible, hooking it in where necessary. Our most important relationship for this is n:m – better known as ‘many to many’. An author may have written multiple books, just as one book might have multiple authors. They may not have, of course, but that doesn’t matter.

Our main table, books, is the longest of the set. Every individual book in our archive is exactly that – even if we have multiple copies, they’re not the same physical object, and so require an additional entry. Books can contain as many fields as you like but, most commonly, you’ll be looking at title, edition, publisher and author IDs (more on this later), year and ISBN number.

We can simplify the creation of this table by making use of our field options. By definition, years and ISBN numbers are numerical values, and while the millennium bug may be semi-fresh in our minds, it’s probably safe to assume you won’t still be adding books on 31 December, 9999AD. In addition, you can set most of these settings to allow NULL values – literally nothing imported, not simply the value 0. This means you don’t have to fill in an ISBN number for each and every entry if, for instance, you want to include friends’ books or other material that hasn’t been assigned one.

Doing it with text
You’ll almost certainly be tying MySQL into applications, so it’s important that you can create your tables and fields without the aid of a fancy GUI. Begin by connecting to the database using the ‘mysql’ application, creating and accessing a new database, ‘mymusic’:

CREATE DATABASE mymusic; USE mymusic;

To create the table, we enter its name using the line:

CREATE TABLE artists (

Hit [Return] and the text prompt will be slightly indented. Enter the following lines to create an auto-incrementing ID number to keep track of which author we’re looking at (which we subsequently use as our primary key) followed by a more general VARCHAR field to hold the name of the singer or group.

artistID INT NOT NULL AUTO_INCREMENT, artist VARCHAR(100) NOT NULL, PRIMARY KEY (artistID));

The semi-colon marks the line as finished, dropping us back into standard editing mode. You should receive a message saying 'Query OK, no rows affected'. We now repeat this for each of our other tables, for instance:

CREATE TABLE music (MusicID INT NOT NULL AUTO_INCREMENT, track VARCHAR(100) NOT NULL, genre VARCHAR(25), PRIMARY KEY (titleID))

It’s possible to alter your tables later, but ideally you’ll have your database fully planned out in advance. In particular, you’ll soon find that even the subtlest differences can have a huge impact on how your database turns out. Take the issue of names. When working with individual artists, it may seem obvious to have an ‘Artist’ name like we have here, but how do you plan to do it? A particular style, such as ‘Cobbett, Richard’? If you aren’t consistent, your database won’t reach its full potential.

For instance, giving a separate field for each name will allow you much more flexibility, including more advanced searching possibilities and mail merging abilities. But be careful; give separate fields for everything from first name to shoe size – just in case they’re ever needed – and your database will suffer from bloat. In this case, most music is credited to a group rather than individual singers, so we’ve been able to stick with just the one field.

Inserting the data
Open up the MySQL Control Center we used last month. If all went well, you’ll be able to connect to a new ‘mymusic’ database, and both browse and add data from this convenient GUI. This is by far the easiest way, but obviously not particularly useful if you have more than a handful of discs. To get the best out of MySQL, you have to work programmatically. Luckily, inserting a new piece of data is easy. Just follow the fields we already created, entering:

INSERT INTO music VALUES (‘1’, ‘Singing Something Simple’, ‘Jazz’);

This should be self-explanatory – creating a new entry with a musicID of 1, a title of ‘Singing Something Simple’ and the genre ‘Jazz’. We can speed up the process by adding multiple records simultaneously:

INSERT INTO music VALUES (‘2’, ‘Cheese Samba’ ‘Rock’) (‘54’‘Sleepytown Love’ ‘Blues’);

A quick introduction to SQL
Each table contains part of the information we need. To get this backout, we have to run queries within SQL. Here’s the simplest-possible example:

SELECT * FROM music;

The asterisk, much like a DOS wildcard, tells MySQL to produce all of the information contained within the music table – our three sample pieces of music and anything else you may have added. We can cut this down by using the command

SELECT * FROM music LIMIT 1;

or, alternatively, focus on one individual column:

SELECT track FROM music;

Let’s try something more directly useful. Add a few extra entries – a decent spread, it doesn’t matter what they are – and list them all using a wildcard selector. You’ll see they appear in rather a higgledy-piggledy mess, in the order they were added. To put them in alphabetical order, try this line:

SELECT * FROM music ORDER BY track;

More practically, we can find specific entries, provided we have at least one piece of the field to hand. In this example, the database will produce any songs by the name of ‘Generic Track’.

SELECT track FROM music WHEREtrack=’Generic Track’;

By creating these queries, it’s easy to find information in a particular table. The catch comes if we need to run searches across our entire database. We look at this in a bit more detail in the boxout below.

SELECT track, artist FROM music, artists WHERE music.artistID = artists.artistID

Song 1 - First Singer 
Song 2 - Second Singer

Song 1 - First Singer
 Song 1 - Second Singer
 Song 2 - First Singer 
Song 2 - Second Singer

SELECT track, artist FROM music INNER JOIN artists ON music.artistID = artists.artistID;