Mysql – Model to handle music release artwork

database-designMySQL

I’m designing a FAN web page for one artist, more specifically, the discography section. The discography includes this entire artist CD’s, Vinyls, 8-tracks, cassette, etc. So far, I’ve build something pretty good, but I got stuck when it came time to handle all of the artwork of a release, not just the front cover. Let me explain:

The model I had was handling only the front cover. The logic was as following:

One release contains 1 front cover, and 1 front cover could be in many releases (this happens a lot for editions of a release where the front cover of an edition is pretty much the same as the primary release.)

For that, I’ve build 2 tables: release and cover

release (release_id, title, cover_id,...)
cover (cover_id, filename, width, height,...)

The model worked perfectly until I’ve decided to add more artwork for releases. Since then, I’m stuck and I can't get my mind to think clearly.

Here’s what I’m trying to do:

I’m trying to add more artwork to a release, like the back cover, inlay, side a, side b, booklet pages, etc. The artwork needs to be in a specific order (front cover first, then back cover, etc).

I’m struggling on how to achieve that. Should I keep the one-to-many model and add one more table for the rest of the artwork? Should I mix all of the images in one single table (let’s call it Artwork)? If yes, how do I handle front covers so there’s only 1 front cover for each release. Should the front cover remain a 1:m relation since the rest of the artwork would probably end up being a 1:1 relation? If all of the images should go in one table, then how to position images and how to make the front cover unique and the primary cover of the release? So many questions that led me to build so many models, so many that at this point I’m totally lost!

Here’s the basics :

  • One artist discography (cd, cassettes, 8-track, singles,vinyl,etc…)
  • Some releases have up to 12 editions, and a lot of times the front cover of the main release is the same as the front cover of the edition.
  • I need to make one artwork the main artwork of the release (most of the time that would end up being the front cover)
  • I need to display all of the artwork in a specific order… (front covers are always the first displayed)

How would you approach this?

Best Answer

This is how I'd model it:

release (release_id, title, ...)

artwork (artwork_id, filename, width, height,...)

release_artwork(release_id, artwork_id, display_order)

All releases belong in the releases table, all artwork in the artwork table.

The release_artwork table joins the two, with the display_order column enforcing the display order. Insert the covers with display_order=1, then the rest (back cover, inlay etc) with 2, 3, 4... etc as needed - A simple ORDER BY display_order ASC will then deal with the ordering for you. If you want to doubly ensure the cover is first, you could always add a is_cover column to release_artwork and set it to 1 for covers.

Related Question