Might & Magic IX ? Requires one to learn different skills to certain levels in order to be able to learn the spells. A bit of a "jigsaw puzzle" to get the necessary combinations right, which required one to really think a character in that game through from the beginning.
Also, I really like the specialty magics of the 1st DA (and only that). I like all 3: blood magic, spirit healer, and shapehifter. Why? Because one did not have to be an elemental mage in DA:O. In the later installments that pretty much the range options you got: would you be a fire mage, a frost mage or a lightning mage? Boring.
And the simplest of them: (A)D&D. A lot of the spells will be combat-oriented in some way at least, they might, e.g., add bonuses to skills or abilities in some way (which of course can be beneficial also in outside of combat). Having returned to D&D I think perhaps that system still is what works best -both regarding how spells are learned (or created) and memorized, as well as how they function in-game (and not only in combat necessarily).