I'm suprised nobody mentioned:
X-Tended Mod 0.7.5 for X3 Reunion
It added quite a lot to the game, and if it never existed XTC would never exist in the first place.

I'm rather ignorant when it comes to mods, played only a few of them and they weren't that good.

Is there some kind of a big website filled with mods for all kinds of games, or do you just fish out different mods, for different games, from different parts of the internet?

http://www.moddb.com/ and google are your friends

Oh, thanks.

I like these big databases, because I enjoy browsing trough things, for some reason.

Love the game but I didn't try the mod coz I went on to play X3TC.

I would also suggest downloading Desura from the website. It's steam-inspired client, except it's for mods and indie games - basically, you can easily browse best and most popular mods from MODDB there, and if you want to try a mod, it automatically detects installation of the base game, and then downloads / installs the mod, hassle-free. Uninstalling a mod is just as easy.