All I can offer is my personal opinion, obviously, so I won’t add “for my taste” to every sentence, but it’s implied. So I think for cool magic system you should think about:
1. a) - the place and relation of magic in the grand scheme of your universe
b) - the relation between magic and a person practicing it.
I think in the best examples those points are connected. Like in “Mage the Ascension”, where magic basically represent different views and ways of exploration of the world AND the never-ending personal growth that lets you to affect the world with your beliefs more the stronger you get. Or the upcoming “Pillars of Eternity”, where magic and all “special” effects like paladin’s aura and etc, are actually come from the same source within each person - their soul. But those powers act and manifest differently because of the different talents, beliefs and training of each individual. (btw I think it’s freaking awesome metaphysical idea, everyone go preorder this game).
So, I think the greatest cases is when magic is a bridge between a person and the world, allowing mage to express their individuality and simultaneously learn of the hidden mechanics of the universe.
2. How magic manifests in your universe.
I mean, fireballs are boring, right? They are ok, but you’d want to have something unique for your world, because otherwise what’s the point of world-building? Magic effects can vary depending on:
types: necromancy, summoning, illusion, that kind of thing, but depending on your unique world. Even if there are isn’t such drastically different schools of magic, what are the types of specialist mages in your world? What combination of skills and talents they need for some specific purpose (like, idk, mages that can control weather and calm storms will be found on the ships and will probably know a lot about sailing and related stuff) Even from a practical point of view, if not a metaphysical.
locations: local specifics are fun! Take Planescape: Torment. Sentient magical rats, smoking a Death Cloud through a pipe, thinking with portals, “I’ve just returned from Hell and I’m going to send you there with my 9 level spell!” So much more fun than just fireballs, right?
mage: but if it fits the meta? As if, changing the effects/spells depending on the traits and personality of a caster.
3. Social reaction to magic.
Because if there are people who can throw a fireball at you in any moment, you’ve gotta have some opinion about it.
Is magic kept in secret from the outside world? How?
Harry Potter, though it doesn’t put any effort in actual hiding, and World of Darkness, where being outed is an actual risk and there are organizations who hunt mages.
If people are aware of existence of the magic, how do they react? Well, it depends on several factors, and I’m going to show it in compassion of DnD (free mages, it’s kinda a respectable occupation)vs Dragon Age (mages are kept under surveillance by a special order and feared) Why people aren’t mad at mages in DnD?
Well, dnd is balanced game. Sometimes it’s a shit balance, but still. Low level mages in DnD can be killed by a critical success of a burp and all they can do is throw sparks at you or make you feel clumsy for a minute. Not very scary, right? It takes a long time for mages to become really dangerous and not many make it to high levels.
In Dragon Age mage is maybe slightly more killable than a fighter, but generally on the same level. And fireball is a first level spell. You can set a crowd on fire on the first level. And one possessed untrained KID is capable of killing off the entire castle and a village. ONE KID. So yeah, kinda alarming, yeah?
Also, in DnD you can learn magic. You can study and learn magic, and you can have powers of a different kinds - divine spells, nature spells, hellish spells, whatever. Magic is not exclusive.
In Dragon Age you have to be randomly born with it and there is nothing you can equal to it’s power. It’s very dangerous and it’s not accessible, that’s why other people are so scared of mages and Tevinter empire conquered half of the world.
So here are the criterias: is magic a “gift” or it can be learned? Is there is something of an equal power to balance it out? If it can’t be learned, how often mages are born? How powerful magic actually is and on what it depends? How hard and long is the training? How long does it take to cast a spell and does it require the preparation and ingredients?