Modeling and Designing Databases - Learning MySQL - page 123

artist_id

Artist

artist_name

1

Compiles

N

album_id

1

Contains

Album

album_name

track_id

N

track_name Track

1

time

WasPlayedAt

N

played Played

Figure 4-11. The ER diagram of the music database

The only strong entity in the database is Artist , which has an artist_id attribute that
uniquely identifies it. Each Album entity is uniquely identified by its album_id combined
with the artist_id of the corresponding Artist entity. A Track entity is similarly
uniquely identified by its track_id combined with the related album_id and artist_id
attributes. The Played entity is uniquely identified by a combination of its played time,
and the related track_id , album_id , and artist_id attributes.

What it doesn't do
We've kept the music database simple because adding extra features doesn't help you
learn anything new, it just makes the explanations longer. If you wanted to use the
music database in practice, then you might consider adding the following features:
• Support for compilations or various-artists albums, where each track may be by a
different artist and may then have its own associated album-like details such as a
recording date and time. Under this model, the album would be a strong entity,
with many-to-many relationships between artists and albums.
• Playlists, a user-controlled collection of tracks. For example, you might create a
playlist of your favorite tracks from an artist.
• Track ratings, to record your opinion on how good a track is.