Where modern databases start to get both more confusing and more useful is when you start making multiple tables and link them together. For example, we now have a list of all of our albums, why not a list of all of the songs as well?
- Import songs
- Create a new table called “songs”. This one will have a field for song, length, artist, album, year, rating, track, and genre. Set up the ID as an auto increment field as we did for albums.
- Grouping songs
- Now that we have songs, we can look up, for example, how many songs we have in each genre.
- Joining tables
- So we have a song table with song information, and an album table with album information. Suppose we want to combine the two on our display? We need to have a field that joins the two tables. In this case, the item that is the same is likely to be the album name.
- Indexing columns
- If you have large databases, or if you have complex select statements, searches can be slow. This is because MySQL has to re-index the columns every time you execute the statement. If you are willing to slow down adding data, however, you can pre-index columns. Generally, you don’t want to pre-index any column that is not searched on, related to, or sorted by.
- Conflicting column names
- Let’s take another look at that query.
- Missing records
- We’re asking MySQL for all songs whose album name matches an album name in the albums table. What if a song doesn’t have an album name?
- Quotes in statements
- One of the “bad” albums is Aerosmith’s “Rocks”. The title of the album in albums has quotes in it, whereas the title of the album in songs does not. Let’s assume that the title with quotes is the correct one. We need to update the songs table and give it the new title.
- Multiple tables: Using IDs
- We’ve set up each table with a unique ID. We should be using those rather than album names to relate our tables together. Otherwise, when we change an album name in one table, we also need to change it in the other. By adding a reference to the unique ID, which never changes and never has to change, we only have to update the album name in one place.
- 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.
