I've played extensively with only the XFO mod, and there are many settings. I can't recall all that I had checked.
My recommendation is to play through the vanilla version first and note the things you don't like. Maybe stick to the quest so there is more new stuff for a second play through. Then head to the Fallout 3 Nexus and look for mods for the things you would like changed.
For me, the best way to make it more challenging is to simply not use VATS. No mods needed for that. VATS is a good idea and truly does remind one of the old games (me anyway) but it really becomes a crutch and is vastly (and illogically) overpowering.
It's still fairly easy to get by without VATS in vanilla, but with a few tweaks in XFO it is VERY difficult to survive and I will say probably impossible without the use of VATS. Especially if you increase enemy speed.
Some simple modifications (you can do through XFO) that radically affect the game include changing med and ammo rarity. Making meds more rare will really kick you in the ass. Yes, stimpacks will cost over 1000 caps. The wasteland is really nasty when you can't use a stimpack for every scratch. All your money goes to the doc, to boot.
Removing leveling for weapon damage makes a huge difference. Low level raiders can rip you a new one.
Those are a couple things to try, but again I'd probably just play it through without changing anything and avoid using VATS if you want a little more of a challenge.
As for the atmosphere, I personally found it more than satisfactory. For me at least, it is Fallout in 3D. (And I am an enormous fan of the series).
I wouldn't worry too much about enjoying the game. There really is a lot to like, despite the endless complaining in this forum. Remember, most folks come here for the single purpose of whining about it.
It pleased this fan anyway.
