My old website had a list of “recommended mods”, but the list was frequently outdated with broken links and I decided not to continue it formally for this incarnation of the site.
If you are looking for a good list of mods as a “where to begin” place for Morrowind, this page has a good selection:
Really, the only “don’t play without it” mods are Better Bodies and any face/texture/misc item replacer, but I think all of the mods at http://mwmythicmods.com/ could be described as “good” (often “excellent” or better!), though you’ll of course have to make choices between one and another, based on your own preferences.
As with my first link, there is almost never one definitive “best” mod, so you really do have to just look through the list and see what you like the look of. Sure, I’d agree with Visual Pack as an excellent texture replacer – but I normally install Visual Pack XT by Qarl over the top of that, and then various texture packs for each location on top of all that (I prefer TextureFreak’s Balmora mod, for example). Some people prefer Darker Morrowind; others like Skydye’s or Connary’s textures.
I spend a lot less time with mods in Oblivion, and find the whole process of installing and using them a lot more complicated so I keep it simple. I use Qarl’s Texture Pack 3 (and again you have a choice to use the ‘Reduced’ or ‘Redimized’ versions) and Exnem’s Eye Candy body mod (note: there are plenty of body mods available – just pick the one you like best!).
After that, I just use some houses from Oblivion’s Real Estate, and one or two others like Belda Elysium, then pick a few small tweaks from Modular Oblivion Enhanced and I’m good to go. I’m personally not a fan of big overhauls like Oscuro’s Oblivion Overhaul, though it comes highly recommended if it matches your playing style. I do play with Ruined Tail, which is great so far but I haven’t completed, but other than that it’s 99% house mods and DLCs for me.
I haven’t really explored Fallout 3’s mods yet, though there are some pretty awesome things coming out already. Tarrant’s work with companions, for example, is ground-breaking, and the various rideable vehicle and animal-mount mods are very exciting. There are various texture packs available, but since they’re beyond the capability of my hardware at the moment, I can’t use or recommend any. My Fallout 3 modlist is mostly Yevic’s house mods and my own, for now, though I do look forward to trying out many more mods in future.