Personally I found the POOP collection to be pretty good for starters. Fixes most of the stuff that is shit and has a few minor content mods, too.
Might be that it's very hard to track down nowadays, though. The site seems to be down but some google searching might make you find it. It's a great modpack, with gameplay and graphics overhauls and a very good step-by-step installation manual.
And installing all the mods in that pack is almost more fun than playing the actual game.
EDIT:
Found the site.
http://www.popehat.com/2008/06/09/build ... #more-1188
Try to download it from there, it's a torrent, so I hope it still has seeders.
This is a super necro, but I wanted to know if any of you guys can find a working torrent. I've searched around and found a bunch of dead ones. It's a shame.