The solution that I'm using is
Photo Contacts Pro 5 which in it's latest incarnation includes contact specific sms tones. It's the best $25 I've ever spent on my Q.
I know have the ESPN sound when I recieve an NFL txt, the ITV news 'bongs' ring out when I get a news alert and seperate alerts when I get texts from friends and family. It's great.
