Change Data and Table Structure Simultaneously

Our new compositions table works better now that we’ve added some indexes, but we discover another problem as we start to add data. Our current structure only allows us to associate one composer with each record, and some songs have more than one composer. As we think about it further, we realize that many songs have separate composers and lyricists, and we might want to capture that info as well.

This sounds like a job for a many-to-many relationship between compositions and artists:. A composition can have many artists, and an artist can contribute to many songs. As we discussed here, we’ll need a new join table between compositions and artists. In addition to the foreign keys for these two tables, we’ll also include a role ...