Are you wondering whether to buy this over Silent Hunter 4 or 5?
Although SH5 has had some substantial attention from the modding community, it still has a way to go, so I would hold off on that one.
SH4 features US Navy submarine warfare in the Pacific theatre. If that tickles your fancy it is equally as good as SH3.
SH3 features German U-boats in the Atlantic theatre in WWII. It has adjustable settings for difficulty, which the game deems "realism". These features include whether the game will just automatically lock on to a target and automatically set your torpedoes upon them, or whether you would like to do the calculations yourself. Let me tell you there is a significantly rewarding feeling when doing the calculations yourself, waiting for what seems like an eternity for the impact, not knowing whether the torpedo will hit... then you see it happen. You almost can't believe it. Such a great feeling.
This game isn't about instant gratification. There is a lot of planning involved and you will have to figure out how to best intersect with your targets' headings. It is really rewarding though, and I can't say that about a lot of games.
As far as mods, I suggest going with Grey Wolves and then heading over to subsim.com to check out what is available.