Sacred Haven of Debauchery

Mods come in different forms. Sometimes, they are so good that the developers could learn a thing or two. The only downside is that they rarely offer truly new and interesting content; it's either vanilla content, or the same meshes used in hundreds of other mods, or just retextures. But even with vanilla content, you can still create something great.
In general, you should look at the screenshots, description, and rating to minimize the chance of stumbling upon something bad.