Firstly, for inspiration, I suggest you check out -
http://www.insimenator.org/showthread.php?t=24809- for really amazing houses by Muse, Dee, Dinki, Platinum and heaps others. Now that's real talent.
And then I think you're talking about custum content, it's stuff that modders and creators here make for us (because Maxis crap gets boring after a while). For a good list of CC check out -
http://forums.sims2community.com/forumdisplay.php?f=58 .
Often, our own stuff that we download has a certain "flaire" Maxis items do not posses, so that's what creates the "amazing houses that we can download".