Retrieving node by unique identifier (not Node ID)

It sounds to me like you're going to need to write a small custom module that uses the Drupal menu system. Technically, the entire path is passed to Drupal as a GET variable. (That's why yoursite.com/node/123 and yoursite.com/?q=node/123 do the same thing, assuming you have clean URLs enabled in your Drupal site settings.)

Basically, you'll want your module to watch for paths like thriller_by_michael-jackson/song/29581. (You can use % as a wildcard in place of thriller_by_michael-jackson). That path should point to a function that finds the song ID in the database and returns the appropriate node (probably via node_show). This might look something like:

<?php

function YOURMODULE_show_song($song_id) {
  $sql = 'SELECT nid FROM {content_type_song} WHERE field_song_id_value = %d LIMIT 1';
  return node_show(node_load(db_result(db_query($sql,$song_id))));
}

?>

You might want to add some error handling, etc., and you may need to make the SQL more sophisticated in case the song ID is different in different revisions of a given song node, but that's the basic idea.

I can't think of a good way to avoid going to the database for each song ID, but if you create a two-column index on field_song_id_value and nid in the content_type_song table, this shouldn't cause too many problems—especially if your server is running MEMCACHE or something like that.

nevets, what do you think?