Our songs table includes a genre column. Suppose you wanted to add a description and a history for each genre. You could go ahead and add two new columns to the songs table, but this would mean duplicating the same description and history for each genre across every record that contains that genre.
select count(*) from songs where genre="Rock"
The “rock” genre alone has 2,289 songs in it. That’s a lot of work. In general, you don’t want to have to duplicate more than one column across all of your records. When the only genre column was the genre itself, separating that into a different table wouldn’t have saved much. But now that genre is also going to be tied to a description and a history, it will be a lot less work and a lot more reliable to separate the genres into their own table.
What we really need to do is create a new table, much like we did above. Since we’ll be creating it from almost scratch this time, we won’t have the same problems we had above.
The first thing to do is make a new table with all the genres in it. We know how to make a table from data in an existing table. First, we find the select statement we’re going to need.
select distinct genre from songs
That shows us the 29 genres we’re going to have in our new genre table.
create table genres select distinct genre from songs
That creates a very simple one-column table with our genre names in it. The next step is to add the rest of the columns we need: an auto increment ID column, and the two text columns for description and history, respectively.
You already know how to create the ID column. But our current text column type, the variable length character column, is really designed for small pieces of text. If your text is going to contain more than 255 characters, varchar will not suffice. For longer text, we have the text column type. Text columns can contain up to 65,535 characters. (If that isn’t enough for you, there is also mediumtext which can contain up to 16,777,215 characters!)
Make the description and history columns be text types.
Now, go ahead and set up some content for the description and history of a few of the genres. For example:
update genres set description="Rock and roll is a form of popular music, usually featuring vocals (often with vocal harmony), electric guitars and a strong back beat; other instruments, such as the saxophone, are common in some styles. Rock's social impact on the world is unparalleled by any other kind of music. It has been credited with ending wars and spreading peace and tolerance, as well as corrupting the innocent and spreading moral rot."
where genre="Rock"
update genres set history="Rock and roll emerged as a defined musical style in America in the 1950s, though elements of rock and roll can be heard in rhythm and blues records as far back as the 1920s. Early rock and roll combined elements of blues, boogie woogie, jazz and rhythm and blues, and is also influenced by traditional Appalachian folk music, gospel and country and western. Going back even further, rock and roll can trace a foundational lineage to the old Five Points district of mid-19th century New York City, the scene of the first fusion of heavily rhythmic African shuffles and sand dances with melody driven European genres, particularly the Irish jig.\nRocking was a term first used by gospel singers in the American South to mean something akin to spiritual rapture. By the 1940s, however, the term was used as a double entendre, ostensibly referring to dancing, but with the hidden subtextual meaning of sex; an example of this is Roy Brown's \"Good Rocking Tonight\". This type of song was usually relegated to \"race music\" (the music industry code name for rhythm and blues) outlets and was rarely heard by mainstream white audiences. In 1951, Cleveland, Ohio disc jockey Alan Freed would begin playing this type of music for his white audience, and it is Freed who is credited with coining the phrase \"rock and roll\" to describe the rollicking R&B music that he brought to the airwaves."
where genre="Rock"
I pulled this information off of Wikipedia (http://en.wikipedia.org/). You may find it easier to go there and copy some of this text out. Remember to watch out for quotes! You cannot surround your text with the same kind of quotes that the text contains unless you backquote the quotes in the text, as I’ve done with the history of rock above.
The final step is to replace the genres in the songs table with the genre ID we’ve just created. First, create an unsigned integer column in songs called “genreID”.
Since we’ll be searching on this genreID, you’ll want to create a standard index for the column as well.
Now, let’s set the genre ID to the correct ID from the genres table. First, we test the where we’re going to use.
select song, artist, songs.genre, genres.genre, genres.id
from songs join genres on songs.genre=genres.genre
If that shows you what you’d expect to see, backup your data (always backup your data before making a potentially data-threatening change such as this!) and convert that select into an update:
update songs join genres on songs.genre=genres.genre set genreID=genres.id
This may take a while! It has 7,000 records to update.
You should now be able to get genre information for any songs whose genre you’ve entered information for. You’ll only have to enter that information once for all songs of that genre; and if you alter the name of the genre that alteration will follow through as long as the genre ID stays the same.
select song, artist, description
from songs left join genres on songs.genreID=genres.id
where song like "%chain%" order by artist
This will show all songs whose name contains “chain”, along with the artist and the description of the genre.
Once you’ve verified that this works, you’ll want to remove the extraneous genre column in the songs table. In Sequel Pro you can just choose the column in the structure tab and delete it. In the mysql command line utility use:
alter table songs drop column genre;
You no longer need that column.
