We've always been able to assign different ringtones to the people on our iPhone address book. With the introduction of iOS 4.2, we can now add unique tones to SMS (text) messages from our contacts, too. Here's how.
Go to the contact you want to change the SMS (text message) tone for. You can do this through the Contacts section of the Phone app or the standalone Contacts address book app.
Once you're in your contact list, find the contact you want and tap it.
Tap the "Edit" button in the top right corner of the contact.
Once the Contact is in edit mode, scroll down to the section reading "Text tone." Tap it to change the SMS tone for that contact.
Select from list of SMS tones. This list includes both the original set of iPhone ringtones and new ones added with this OS. Tap the tone you want and you'll hear it played. Once you've found the SMS tone you want, tap the "Save" button in top right corner.
Unfortunately, you can't currently assign custom or homemade ringtones.
After changing the SMS tone, you'll be taken back to the contact. Tap the "Done" button in top right corner to save the change.