[mythtv] MythMusic database schema suggestion

Brian Lalor blalor at bravo5.org
Tue Jun 24 10:39:39 EDT 2003


thor wrote:

>>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
> 
> 
> 	There is currently a clean mapping between a "track" (file on disk), and 
> metadata information. Any attempt to squeak efficiencies out of this is 
> probably doomed to failure. A new file means what? Deleting a file means 
> what? Re-tagging a file with another application (while myth is *not* 
> running) means what?

I don't think I understand.  Information about the *artist* should not be 
stored on a per-track basis.  Adding and deleting tracks would be no more 
difficult (simpler, in fact, given the current comma-separated list for 
playlists) with the more relational schema I proposed.  Re-tagging a *file* is 
kind of a moot point unless MythMusic is reading the ID3/OGG tags frequently 
rather than using what's in the database.  If you want to change the data 
about a track/artist/disc, then it'll be up to you (you being the user) to 
kick or restart MythMusic or the backend.

> 	playlists are clearly sub-optimal. Someone should be able to improve on 
> comma-delimited strings. I await a precise description of how to do this 
> better. This would be very welcome, as I am far from a DB/SQL expert ;-)

I'd recommend looking at my schema or DDJ's.  They're both easily manipulated 
via SQL.

>>I'm much stronger at SQL than I am at C++; 
> 
> 	This is, unfortunately, often the difference between theory and practice.

Not really.  I'm quite adept at writing data-driven applications in Python, 
PHP and even Java.  Just not C++/QT.

 > If the target application (i.e. MythMusic/C++) needs a read for every track,
> then extra tables for artists and albums actually increase the startup 
> burden. 

You don't need to perform additonal reads with my schema.  The queries that 
are being done now can still be performed with a single query that joins the 
data from the multiple tables.  No additional startup burden, just easier and 
cleaner maintenance of the database.

What's being stored in memory?  If I've got 10,000 songs (which I probably do 
and my collection isn't that big in relative terms), does that mean you're 
reading *10,000* records from the database into memory at startup time?  I see 
that reaching practical limits of memory pretty quickly...

Allow me to look at it from the point of view of the current (0.9.1) "select 
music" screen.  Startup of the application should be near-instantaneous.  Just 
draw the screen.  Then we go to the "select music" screen.  No data's been 
loaded from the database because we don't know what the user wants to do yet, 
and for the sake of argument no playlist data is yet loaded.  The select music 
screen is configured to show a tree view with a structure like this:
	.
	+ All My Music
	| + Artist1
	| | + Album1
	| | + Album2
	| | + ...
	| | \ AlbumX
	| + ...
	| \ ArtistX
	+ All My Playlists
	  + Playlist1
	  + ...
	  \ PlaylistX


... but when we enter the select music screen, we're presented with a fairly 
collapsed view, thusly:
	
	.
	+ All My Music
	| + Artist1
	| + ...
	| \ ArtistX
	+ All My Playlists
	  + Playlist1
	  + ...
	  \ PlaylistX

That's two very simple queries:
	SELECT ID, Name FROM artists ORDER BY Name
and
	SELECT ID, Title FROM playlists ORDER BY Title

Those are probably the fastest queries you could hope for from any database.

Now, if the user cursors down to Artist1 and expands the entry, we just grab 
the discs for that artist:
	SELECT albums.ID, albums.Title FROM albums, artist_album_link
	 WHERE artist_album_link.ArtistID = <artist id>
	   AND albums.ID = artist_album_link.AlbumID


I could go on, but I won't. :-)  I guess I just don't see where this layout 
adds any complexity.  I think the normalized data makes for easier 
manipulation outside of MythMusic and that makes it worthwhile, especially 
given the number of rows.

Thanks,
B

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


More information about the mythtv-dev mailing list