Some SMS application allows you to set custom notification sounds for each of your contact. I'll provide 2 APPS that do just that.
Let's start with mySMS. Within your list of text messages simply press and hold a conversation in the list, it will then by highlighted in blue. Notice the icon set at the top? If you click on the speaker icon, you will then be able to select what notification sound will be generated when that contact sends you a message.
Done easy as PIE! Ummm Pie!
Next up for grab is Textra! Yeah your read that correctly. Textra also allows you to setup a custom notification sound for your contacts. Much in the same manner as mySMS you can hold the conversation until you see the 3 DOT menu at the top right, then simply click the notification option and go change the sounds, the vibration etc.
Wasn't that easy? Take a look at a couple of screenshots that I have.