To completely disable a notification tone on your iPhone on a contact by contact basis do the following:
1. Tap Address Book
2. Open a contact
3. Tap Edit
4. Tap the ‘text tone’ field
5. Scroll to the bottom and tap ‘None’
6. Tap Save & Done.
This applies to iOS version 4.2 and newer on iPhone.