This question came up a while back, and I posted that it couldn't be done...well, I was wrong!
Mort over at xda-developers created a great little program that allows you to set a different ringer / sound notification for each contact or group in your contacts list!
The main thread is right
here.
The latest file is right
here.
And finally, a simple description and basic instructions are
here.
Enjoy~!