I can't believe I missed this thread! This in only my area of expertise!!
I've covered a lot of ground here over the years. You just can't go wrong with an AWE64 as an "average joe" card. Fewest "issues" you'll ever have. It's clean, runs EVERYTHING, and never a hitch. Sounds decent too.
Now if you want specialty.....
MOD-music creation; the Gravis Ultrasound series (especially the PnP w/ 8MB!) is king.
MIDI creation; Yamaha DB50XG or Roland SCDB10/15 daughterboards on a SB16/32, or something else with a good internal synth is a good, cheap starting place.
Clean recording; Turtle Beach had a few- Multisound started it all. Whew... that card was introduced over $800US!

Soundfonts; AWE32 + 8MB in 4MB 30-pin sticks, or best, AWE64 plus a memory module. (I don't know why creative did such a thing.... ugh.)
That's about it for ISA.
