26 Working with multiple

It  turns  out  that  different  artists  occasionally  release  albums  of  the  same  name.  Tracy

Chapman released an album called  Crossroads ; and there was a movie called  Crossroads .

Our select statement couldn't tell the difference.

One solution would be to check the artist as well as the album when doing the transfer. But

while it would solve this problem, it will exacerbate the problem of different spellings: instead

of only different spellings for album mattering, different spellings for either album or artist

will matter.

These are the sorts of problems you run into when you try to reconcile data from multiple

sources. Sometimes you can fix the problem at the source; other times, you'll need to do the

work to reconcile them in MySQL. If it is important data, however, it is almost always worth

the time now to fix the data, rather than spending more time later after problems have had a

chance to build up.

Separating data

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.