You can set custom text tones, mail alerts, and sounds for when you tweet! Only thing i have noticed is you cant have a tone 30 seconds or more, it has to be 29 seconds or less!
I have one contact set up that way, but it's intentional. It's for the automated pager system at work - I have intentionally chosen a long, loud obnoxious tone to be sure and wake me (and grudgingly) my wife when I get paged at night.