Back in the days of me trying to philosophize about storytelling separately from psychology, I thought there were four prime traits for a good magic system. I don't stand by the old philosophy any more, but I think I said enough worthwhile things that I won't scrap the article. Here are the three traits I listed that I still think are, while not true prime criteria, useful to think about:
Depth. It should give rise to complex usage and far-reaching implications through relatively simple mechanics. A parallel can be drawn here to game design.
A really good example of depth (and honestly just the best magic system I've ever seen overall) is the Mistborn books. Some people are born with Allomancy, which allows them to consume small bits of metal and then "burn" it for magical power. Each metal gives you a different effect, and they come organized in three dichotomies: internal versus external, mental versus physical, and pushing versus pulling. For example, the internal physical pushing metal, pewter, makes the Allomancer's body much stronger and more agile while burning it. The external mental pulling metal, zinc, magnifies the emotions of others, which can be used to manipulate crowds and individuals. The external physical pushing metal, steel, allows you to push metal away from you - or, if the metal object is heavier than you, pull yourself toward it. This creates so much depth of interaction when we see Allomancers carrying around sacks of coins to use as projectiles and specialized soldiers called "hazekillers" that use wooden shields and weapons and wear no metal in order to better combat Allomancers.
Allomancy has a consistent theme: metal is power. This is built on later in the books when the Atium stash is revealed to be Ruin's body. And since the powers are organized in a rigid system of dichotomies, it doesn't feel like eight separate things magic can do. It feels more like three ideas from which eight powers follow.
Transparency. The reader should be told how the magic works, so that they can appreciate how cool it is, and so that it feels fair when you use the magic to solve a problem.
A really good example of a system that fails horribly in this area is (I bet you know what I'm going to say) Harry Potter. These books are all about magic and yet we never learn how it works. There are an unknown but massive number of "spells" and whenever the plot needs something, the author makes up a spell that can do it. This stops us from predicting what's going to happen because we just don't know what's possible in this world.
Imagine what might happen next is part of the fun of fiction. If something happens that you didn't expect because you didn't decipher the clues, that's a good story. If something happens that you couldn't have expected because you didn't know it was possible, that's just lame, especially if the POV character did know it was possible.
There's also quite a few instances of magic that don't involve incantations, spells or wands, such as potion making, divination (logically impossible), portable time travel devices (logically impossible), and probably others that I don't remember because it's been so many years. These things feel completely separate from the wand, spell, and incantation-based magic that wizards use directly, like Rowling had to include every trite form of magic in one story.
We never even learn what the process of learning a spell involves, which is pretty important considering the story is about students at a magic academy. We also never learn what makes some wizards more "powerful" than others, or even what it means to be more "powerful" than another person in this magic system. How are we supposed to appreciate how cool the magic is if we don't even know how it works?
There is an exception to the rule of transparency. Final Fantasy 13 is a good example. In this game, magic exists and we don't know how it works, but it isn't used as a plot device - instead we're quickly given the impression that it's really only useful for combat (Lightning's gravCon device is a piece of technology as far as I'm concerned), and because of that combined with the combat system being an abstraction of what's actually happening in the story, we don't really need to know how it works. But if you're writing a novel and not a JRPG, odds are you intend your magic to be more than a combat asset, so you'll need to explain it.
Two-sidedness. The best magic systems, instead of giving great power at small cost or small power at small cost, give great power at great cost. A parallel can be drawn here to desiging moral dilemmas.
Power is fun, but it's more fun when it has an interesting cost, limitation, or risk associated. In one now-scrapped story I created, magic can get very powerful, but it costs wakefulness, and the cost depends only the magnitude of the effect you achieve and not on how powerful you are. So when the characters were first gaining magic and it was weak, the effect was so small they didn't even notice, but I had planned that later in the story, when they became much more powerful, fighting for just a few minutes would sap all of their energy and they would need to sleep for several hours to recover. Much more interesting than just letting them use magic endlessly for free. It also served the plot in at least one way: it stopped the heroes from being invincible later in the story.
Another example, from a story I have planned but probably won't be getting around to for a while, is built on the premise that each person lives many lives in many different worlds, and what's happening when you sleep is you're essentially signing out of one world and into another. People don't know about this because their memories are compartmentalized. Still, that's what dreams are - your memories from other worlds leaking a bit. So when you use magic, the barriers keeping your memories separate get a lot weaker, and you start to remember your other lives. This seems like a good thing... until you get so deep into it that you can no longer tell the difference between your different worlds. You remember things that didn't happen, try to apply knowledge of other worlds to your present one, and, from the outside, it just looks like you're going insane. You effectively are.
The next I want to talk about is stock powers: common or obvious abilities that are useful to think aobut as starting points.
Telekinesis. This is one of the most obvious and straightforward powers of magic: the ability to apply force to an object remotely. Although the power itself is unoriginal and uncreative, there's still a lot of cool stuff you can do with this.
An old novel I wrote when I was 19 called Pillars of Life used a simple restriction to make things a lot more interesting: you can never telekinet a living person's body, not even your own. So even though powerful mages fly, they have to do so by standing on wooden platforms with their ankles strapped in and telekineting those. It meant if they were ever caught without their platforms they might have trouble getting off the ground (although I never used that). It also meant no Force-push (you couldn't circumvent it by telekineting someone's clothes).
Another restriction Pillars of Life used was that you can't telekinet something already being telekineted by someone else, no matter how much more powerful you are. That solved the problem of why you don't just push back any projectiles a weaker mage throws at you. I also used it for a (I like to think) clever plan where the protagonist killed a much more powerful mage by collapsing the ceiling on him.
Time travel. Bug off, okay? It's logically impossible. Never use this one.
Some sort of obvious attack. This is extremely common in RPG magic systems (and RPGs are legitimate storytelling in every way), and can come in the form of fireballs, lightning bolts, or straight up "magic blast". While it's the pinnacle of uncreative, that doesn't mean it's inherently bad. Not all stories need a super creative magic system. One way I could think of to make this more interesting is to say that shooting a fireball makes the caster extremely cold. Not only does this prevent the caster from just incinerating all their enemies, it gives the power a sort of theme: not creating energy, but transferring energy. It would be interesting to build on that if there were other powers in the magic system. (Hm... I might've gotten inspiration from Mistborn for this...)
Healing. This is an incredibly useful thing to have plot-wise in a fantasy setting. Without it, injuries are rather permanent, which leaves you with two options: either the heroes miraculously never get injured (which will be unbelievable if the story contains a lot of combat), or no single hero ever has to fight more than once or twice throughout the story, which crosses off a lot of possible stories. Having magic be able to heal seems like an easy solution, but it brings with it a lot of its own issues. For one thing, if magical healing is easily accessible, it changes the world dramatically: doctors probably won't exist, nobody is sick, etc, and also, sometimes you want to give a character a lasting injury, which becomes difficult to do.
As with other unruly powers, healing can work fine if it's harder to access. Maybe you can heal injuries but it shortens the patient's lifespan by several years, or maybe you have to hurt someone else who consents in order to heal.
I guess another thing I might as well talk about while I'm here is stock costs of using magic. Generally magic needs some kind of limitation on its use to prevent it from breaking the plot, the world, or both.
The consumption of a special substance. Allomancy is an example of this. It didn't work out very well: it ended up mostly equivalent to unlimited magic since the substances weren't hard to come by except for atium. Sure enough, it caused the problem of magicless characters being irrelevant to such an extent that the author decided to turn the most important magicless character into a Mistborn.
If I were to do this in a story, I'd give the heroes a practical limit on the amount they could get so it would actually matter.
Pain. This is kind of generic but I've never seen it done. A downside is that it's kind of "opaque" in that the audience can't easily imagine it; describing pain well is hard.
Memory loss. Here's a more serious cost. Everyone will think twice before using magic now. I'm surprised that I also can't remember seeing this used anywhere.
Here's another question: how do you get magic? If it has a cost like consumption of a rare substance, it might be okay to say everyone has it innately, but many stories want to limit magic to certain people.
It's genetic. Some people are born with it and others aren't. A problem with this is because magicless characters usually end up getting dwarfed by the magic-wielding hero and villain. Even Mistborn does this; almost all of the heroes are rare Allomancers, and it's the three with the most powers who drive the plot. (Two of them are POV, but I think that's because the magic system makes it inevitable that they'll dominate the story, and not vice versa.) Again, this led to the need to turn the most important magicless character into a Mistborn because a magicless character just couldn't stay relevant.
One way to avoid this problem would be to have being magical not be a strictly good thing. Obviously, if being magical just gives you the option to use magic, then it will always be a strict positive (outside of rare "kill yourself to avoid capture and torture" type of situations). But if there are some consequences to just being magical even without using your magic, that can get really interesting, as well as give non-magical characters a way to still be useful.
Study. This is fine in theory, but the problem is that you have to answer what "studying" to become a mage involves. Harry Potter, again, is the perfect example of failure in this area: a book about students at a magic academy that doesn't even try to answer this question. I've never seen this one done well, and never tried it myself either. I'd like to at some point but I just don't have any ideas.
Endowment from supernatural beings. This is a lot like the "it's genetic" answer but without the problem, since you don't have to cheat to give it to people. It also gives the opportunity to explore what you have to do to get one of the gods to make you magical. Do characters have to worry about losing favor? Is it possible to trick them into giving you power you can use against them?
An experience. Actually, Mistborn uses this one too: even if you're born an Allomancer, you have to "snap" - which involves a near-death experience - to unlock your powers.
This seems like the most useful and versatile of the solutions here. It lets you limit magic, not just to a random subset of people like the genetic answer does or to people with specific ancestry, but to people with a certain common thread. For example, maybe the only people who can use magic are those who have killed someone. Lends itself easily to evil being more powerful than good. There's also the juicy opportunity to explore someone who wasn't evil, but killed an innocent person to get magic so they could save more innocents explore the agonizing process of picking someone out! Do you kill the kind grandparent who's too old to be much more than a burden, or the young adult whose business is doing a lot to help people, but whose motivations are mostly selfish? A good person would likely face huge amounts of self-doubt and retrospective guilt over such a decision. and is now outside the restrictive storytelling limitations of white-and-black morality. In fact there would probably be a lot of such people in such a world.
That's just one example of what you can do with this. It's a broad solution with a huge design space. Not only that, but this option allows you to give magic to whoever you want throughout the story without needing to send them to magic school for years or come up with an explanation of how you can go from being a muggle to being a wizard.