As we all know (/snark), Gandalf knew only three spells: speak with moths, continual light, and force charm (or was that Obi Wan?). The rest of his magic required his epic-level staff (just ask Wormtongue). So why do some wizards need 10-pound spellbooks? Isn't three spells, plus a staff, enough magic?
This is such a great question.
IMHO.... "As many as the plot needs".
I have run almost every system ever made. And the 100% consistent thing I notice about any game with magic/psionics/mystic etc =
The players use what the challenges call for
No more
No less
From Mage to D&D to Conan to Aberrant and so on and on... If all the players do is talk, socialize, persuade, and intrigue = not a single "fireball" was ever cast or even desired by any player. The rules could have never had a "fireball" spell, and the players would never have noticed.
Players will use a tool if its useful, and if they only have one tool, then they will use it whenever they can force it to apply. So when creating spells for your game, first think about
"Ok, but what is this 'caster' most often doing?" and then follow it up with
"Yeah, but do I want a spell to help them do that?"
With this game we hacked =
Demon the Fallen (free link) , there are 'spells' and 'powers'. And instead of writing down details. We made keywords and let the dice determine how much they could accomplish.
This solved the problem of "how many spells" , buttttt it put a LOT of improv creative narrative on the player every time they used a power... so...