[mythtv] MythMusic database schema suggestion
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
I put together a Visio diagram (I know, I know) and exported it to a HTML
file. 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
/ / / __/ Brian Lalor
/ _ \/__ \ blalor at bravo5.org
More information about the mythtv-dev