I haven't played it in years, so I don't know which mods are still around and compatible with what.
The Unofficial Patches would be a good start.
From what I remember, the important mods were the huge overhauls, FCOM would somehow combine them all into one super-mod. I was very bandwith limited at the time, so I played with the TIE mod. It used all standard components, but removed the enemy and loot scaling. Enemies would be very dangerous from the start, and becoming actually fair once you leveled some.
Unique Landscapes had some utterly beautiful scenery. Eerie forests, scenic coastlines, stunning canyons, and actual farms.
Deadly Reflex was good for a laugh, and decapitating or cutting people in half.
Magic mods were cool for adding new spells and actually making magic useful, but they also made you insanely powerful. I would cast a speed spell on my horse, and it would run faster than the world could load on my admittedly weak machine.
Open Cities would remove the gates to cities, making the world more free flowing, but it had lots of incompatibilities with other mods.