It kind of depends on the goal. If one is just building a fantasy world, I can see the justification for having lots of different "kinds" of magic, different schools for wizardry and sorcery and witchcraft, for necromancy and elfin magic and psionics, for miracle and druidry and shamanism, and however many other nuances and variations you could come up with. But sometimes that gets overdone, especially in RPGs.
In games, I'd much rather just have one thing that gets called "magic," and all the better if the rules are the same for arcane, divine, and psionic powers -- the fewer distinctions between them, the better.
In my own campaigns, for example, there is no separate thing called "psionics," because all ability to use magic is rooted in the caster's innate psychic potential. The words of the spell, the material components, and all the arcane trappings are mere channels or foci for the caster's ability to psychically affect the physical world.
The difference between arcane and divine magic is all in the caster's approach, not in the source of the power. (And all casters need spell-books; the gods are too distant and possibly-non-existent to grant miracles to their followers.) Arcanists treat magic as an art, a natural talent to be cultivated by individually practicing mages. Divine casters, on the other hand, see it more as a science, and they approach magic with more structure and rigor. So arcane casters in my settings come off feeling more like psychics and spirit mediums, while divine casters are portrayed as hermeticists, theurgists, and occult scientists. Mages are a cross between Dr Faust and Mme Blavatsky; while clerics are more like a cross between Dr Van Helsing and Aleister Crowley.
As to where the magic comes from, arcanists contend that the source of magic is the spirit world. Furthermore, the spells written into a spell book are actually formulae for drawing spirits out of this other realm and binding them within the mind of the caster. That's why mages have to memorize spells -- the spells themselves are actually spirits, trapped within the caster's memory until the mage releases them, and the spell's effect happens as a by-product of the spirit escaping back to its own plane!
Divine casters, meanwhile, are more likely to believe that magic is a gift from on high, and the actual power of magic flows from an impersonal energy source, the Luminiferous Aether, which the caster draws from the Aethereal Plane and stores within his soul until the energy can be released to cast the spell. Of course, magic being mysterious and all, there is no hard and fast means of proving the arcane theory, the divine theory, both, or neither to be correct.
The only kind of power that doesn't fit into the scheme is ch'i, the subtle and quasi-magical force wielded by very high-level monks. A ch'i-kung master is able to perform some astounding supernatural feats that certainly seem like magic or psychic power, except that they aren't. They're just an extension of martial arts training, and learning to mentally manipulate the flow of one's own life force (and, to a lesser extent, the Ch'i Pool that flows through all living beings). It's quasi-psychic, but not otherworldly enough to really count as magic, and there's definitely no spell-casting involved. Just simple Jedi mind-tricks.
With regard to the frequency of magic... the rarer the better. Magic should be just prevalent enough that everybody has heard of it, believes in it, and takes its reality for granted -- but not so much that everybody has
seen magic performed. Likewise, magic items should be rare enough that they're considered, well, rarities, but not so obscure that your average commoner would doubt their power or their existence as a matter of course.
And the level of magic... maybe I'm just biased towards low-level games and Middle-Earth type settings, but I like magic to be subtle. It's just a preference, but IME once the players have their hands on earth-shattering magic, the game is far less of a challenge. So spells above 3rd level are
very difficult to acquire. 3rd has some great stuff -- fireballs, lighting bolts, haste -- but 4th level and up, what with polymorphs and dimension doors kicking in at that level, that's where it really starts to get over the top. Hence the beauty of playing
E6. It really keeps the magic manageable and suitably balanced.
