OK, you have now shown me a little more about the database structure which I wasnt aware of but it doesnt mean all is lost. It does mean a bit more work on the existing system but the benefits could solve many problems. Taking your example, lets remove the genre_track and contributor_track tables and create the remaining tables as:
In the database this will be represented as: tracks ====== Row 1: Id=1, Title="Track 1", genre=10,artist=100,composed=200,performed=100 Row 1: Id=1, Title="Track 1", genre=20,artist=100,composed=200,performed=100 Row 2: Id=2, Title="Track 2",genre=10,,composed=100(default),performed=100 genres ====== Row 1: Id=10, Name="Pop" Row 2: Id=20, Name="Easy Listening" contributors ============ Row 1: Id=100, Name="John Doe" Row 2: Id=200, Name="Mozart" The indexes would be on: tracks ====== Id Title Genre Artist Composed Performed genres ====== Id Name contributors ============ Id Name The only piece of extra coding I can see from this example is that, if either the composer of performer are not supplied, default them both to be the same as whichever IS supplied. If not, have a default of (perhaps) 0. In answer to your questions: How would it in this case be possible to find the composer for Track 1 ? Select contributors.name track.title from contributors, track where track.composed = contributors.id How would it in this case be possible to find all the genres for Track 1 ? Select genres.name track.title from genres, track where track.genre = genre.id In each case, the columns are indexed and, therefore, access is direct. Arguably, you have cut down on the overall size of the database while making it much faster. The only significan code changes to programs is in the scan where you build the database and the SQL changes for retrieval are pretty simple. -- larrettp ------------------------------------------------------------------------ larrettp's Profile: http://forums.slimdevices.com/member.php?userid=10191 View this thread: http://forums.slimdevices.com/showthread.php?t=45261
_______________________________________________ discuss mailing list [email protected] http://lists.slimdevices.com/lists/listinfo/discuss
