Eh, i'll just respond without a specific response about what the OP is looking for mod-wise.
http://www.fallout3nexus.com/
This is where you get them. Period.
There's alot of junk you have to put up with in trying to get mods to work, but there's a fair bit of documentation around. I'd say more, but its been so long since I did it I don't exactly remember how it worked. Some ArchiveInvalidation thing, the FOMM (Fallout Mod Manager), FOSE, and (perhaps) FO3 MasterUpdater. You will be spending a fair bit of time screwing with files.
As for the mods themselves... Mart's Mutant Mod (MMM) and GNR More Where That Came From are sure-fire winners. If you are looking for appearance changes, you can find millions on FO3Nexus (I use FACE and Type3), though many (most) of these are also nude mods. So it goes I guess.
There's also some pretty large comperehensive "game-changer" mods out there that add TONS of stuff, like FOOK or FO3 Wanderer's Edition (FWE), but these drastically change the game, so I wouldn't touch these until you've at least played through the original game extensively. I probably wouldn't even pick up FWE anyway, looking back now, but I don't want to go through the pain of uninstalling mods, which probably would just mean a fresh re-install in the end.
Aside from that, just look around on Fallout 3 Nexus. You will find stuff
