You can run multiple mods as long as they don't modify a same file. Such an occurrence will render one of the mods useless, or buggy at best.
Celestial's 1.051 mod probably contains all the worthwhile additions that the game should have had from the start- e.g. party members changing appearance, the mysterious streanger person improved, some of the unavailable game endings now possible. I recommend you stick to this patch. Or you may try the alternative Seraph's Patch.
Now, you may also want another kind of experience, for example, you may want to play a natural born leader and assemble a private army with the Cult of Personality mod (one allowing a person with the said perk to recruit all the NPC's at the same time). This mod will not work with the B-Team mod ( a constituent of the 1.051 patch) because they both work on the NPC's scripts. If you're a Miria fan, find the Miria mod - changes Miria into an actually useful NPC, - albeit not with 1.051 mod, for the same reasons.
You may want to drop the way of the lone Chosen One and have your faithful servant Klint (the Temple guard) tag along with the Friendly Klint mod. This one however is quite buggy, I found that my save games got corrupted a lot for no reason.
You may also try some other fun mods: Allowing you to play as the Pip-Boy (sorry, Vault Boy) or walk around in Frank Horrigan's armour. But these are more of a curiosity than a true experience.
Also, there are some mods that strip Fallout of all it's falloutishness, like the Second Apocalypse or Living Dead, which will appeal solely to primitive hack'n'slash games fans.
One thing you could really check out is Human Shield's total setting improvement mod, which changes the game a lot to be more in line with Fallout 1 and thus, darker.
At any rate, poke around the files section and I'm sure you'll find something of interest.
Also, keep an eye out for new mods, I've heard some quite large ones are in final development