[mythtv] MythMusic database schema suggestion

Brian Lalor blalor at bravo5.org
Mon Jun 23 15:56:03 EDT 2003


Hey all.  I've been using Myth for a couple of weeks now and I'm really 
impressed with it.  I've been too busy to play with the CVS code, but from 
what I hear, I'm really excited about the next release.

In looking at the structure of the MythMusic database schema, I decided a lot 
of that data could be minimized and normalized to make for more efficient 
queries and to allow the database to do more of the work for searching for 
result sets while at the same time not having to work quite as hard.  I took a 
couple of minutes and whacked out what I think is a reasonable facsimile of a 
better database layout as it pertains to MythMusic.  The things that bother me 
about the current structure (as of 0.9.1) is the repetition of data for artist 
and album information for each track and the way that playlists are stored. 
By using a more relational schema, much of that information can be normalized 
(less maintenance, smaller data footprint) and the information can be queried 
more efficiently.

I put together a Visio diagram[1] (I know, I know) and exported it to a HTML 
file[2].  This shows a high-level un-detailed picture of how I think the data 
structures could be better laid out.  I think it is fairly self-explanatory, 
but I'd be happy to better describe it if it isn't.  One variation I'd 
considered is to have a separate album_track_link table since, in theory, the 
same track can be on several different albums. I don't know if it is necessary 
to persue that or not.

If you think in terms of the playlist edit window, a query that would return a 
list of artists would look like:
	SELECT Name FROM artists ORDER BY Name

A listing of albums by Soul Coughing would be queried thusly:
	SELECT albums.Title, albums.Year
	  FROM artists, albums, artist_album_link
	 WHERE artists.Name = 'Soul Coughing'
	   AND artists.ID = artist_album_link.ArtistID
	   AND albums.Title = artist_album_link.AlbumID

To get the tracks in a playlist in the order they were arranged by the user 
and stored in the "HereWeGoRockin'!" list you'd do:
	SELECT tracks.Filename FROM tracks, playlists, playlist_song_list
	 WHERE tracks.ID = playlist_song_list.TrackID
	   AND playlists.ID = playlist_song_list.PlaylistID
	   AND playlists.Title = "HereWeGoRockin'!"
	ORDER BY playlist_song_link.Order

This structure also supports multiple artists performing on a single album. 
You could even extend it to support multiple artists for a given *track* if 
you were so inclined.

I'm much stronger at SQL than I am at C++; I'd be happy to help any way I can 
with this.

Thanks,
B

[1] http://bravo5.org/MythMusicDBSchema/MythMusicMetadata.vsd
[2] http://bravo5.org/MythMusicDBSchema/

-- 
    __   ____
   / /  / __/ Brian Lalor
  / _ \/__ \  blalor at bravo5.org
/_.__/____/  http://bravo5.org/


More information about the mythtv-dev mailing list