Here is the URL: http://www.youtube.com/watch?v=KcsYWqhxV2M
Basically this tutorial shows you how to create great looking textures using in conjunction Photoshop and Z-Brush. I decided to have ago at this technique and see what would happen. Below is how it was built up:
Here is the diffuse that this tutorial helped me generate when it exports it out into Photoshop:
If we do a quick comparison between the old texture and the new texture you can see a massive improvement.