Hi, and welcome to SH3 and the SUBSIM community here.
Yes, there are lots of mods, and different people prefer different things. WAC, RUB, NYGM, GWX, plus a countless set of minor tweaks and fixes. It sort of depends what sort of thing you'd like, really. I do a lot of multiplayer games, and our group uses GWX plus some multiplayer tweaks. However, I know that others have their own favourites.
What I'd suggest is to ask. If you'd like to find a mod to give you X, or improve Y, then ask for it, stating what sort of thing you're looking for. I'm sure then that someone will pipe up and make some useful suggestions.
For patches, I'd suggest for the UK, you visit here...
http://www.silent-hunteriii.com/uk/updates.php
If you've bought the UK DVD, you'll need the EMEA-DVD patch 1.4. You don't need to patch 1.1, 1.2, 1.3 first, you can go stright from the DVD-installed version to patch 1.4 directly.
I hope that helps... once again: WELCOME!!!