Lets talk about magic, especially in computer games.
The core of this essay is that magic is only interesting and useful if the effects are interesting and useful. You can come up with all sorts of zany spells, but if they don't change the game world in an interesting way, the player isn't going to care.
An obvious example of this are JRPGs such as the Final Fantasy series. In these games, the player is normally only able to play adaptively in battle - the exploration phase is typically just a stock map that doesn't change to suit the player's play style. Because of this, the spells are limited to those which are useful in battle or in prep for battle.
Moreover, there's almost always a "best" approach because of the way the battles work. Use a specific element against an enemy weak to that specific element. Use a specific spell when you take a specific amount of damage. Players can express themselves by playing differently, but this usually means "playing suboptimally" and therefore is not a very good way to let players express themselves.
Western RPGs are often more open-world, and therefore the magic often allows you to affect the world in general. The most noticeable times this happened were in the earlier Elder Scrolls games. In the more recent ones, the noncombat spells have been steadily degrading, but you still typically get lock-opening, invisibility, jumping, cause rage, and a number of other spells that have no real in-combat use but are fantastic at all the things outside of combat.
I started thinking about an RPG where you are a mage and there is no combat. Your magic is entirely limited to things which are useful outside of combat. I thought about how you might be able to do that.
In the end, I came up with a number of things you can set up in your game world such that they can be interestingly affected by magic.
1) Adding physics to combat. The difference between ice and fire is not that some enemies are weak to ice and some are weak to fire. The difference is that one encases you in a physical material while the other one causes the status effect "AGH I'M ON FIRE". Using physics in combat normally necessitates real-time combat in some way. A simple and good example of physics in combat is the Valkyrie Profile series, in which (depending on the game) juggling, repeatedly downing, and limb-striking enemies all have very different results.
2) Open world exploration. Do not cement paths, but instead allow the players to roam anywhere they can get the physics to work out. They can get on top of houses, sure - if they can figure out how. That might be a jump spell, or it might be summoning a crate to stand on. However, in these cases your spells that modify player movement should be high-level and not available right away.
3) Destructable/constructable environments. It can make it very hard to enforce a plot or reliably ambush the player, but allowing them to destroy or construct walls, roofs, floors, furniture, etc can really lure players into investing a lot of time in creating delicately polished variations on spells which do just that. So what if there's no balance?
4) Information environments. Embedding information/sensory details into the world can really make the world a lot more interesting. An easy example is a secret door. It's hidden - you can't see it. But once you know about it, the visual changes and you can see it. Another easy example is a locked door. Locking is not actually physics-based, it's just a piece of information which changes the behavior of the door. Allowing these things to be tweaked with magic can give the player endless fun, especially if he can create them and watch other players or NPCs try and figure things out.
More complexly, information environments can designate who owns specific goods or rooms, whether someone has a bounty on their head, hook a trap up to a sensor, create a trail of quest breadcrumbs that lead to the cool treasure, create a complex lever puzzle to get to the cool treasure, create trade agreements between cities, establish a road, create a destiny link between two people... and all of these can be affected by the player if you let it, allowing the player to construct the world on a much deeper level than they normally could.
5) Body physics. This includes projection (moving the camera to another location), possession (changing bodies), polymorphing (changing your body into something else), and so on. However, it also includes sub-body events such as having a wide variety of status effects (wet, painted, sneezing, broken arm) and affecting specific pieces of gear (heating armor until it glows, lighting hair on fire, etc).
6) Linked state modification. An example of this would be if your world was actually a light and dark world overlaid on each other and the player would sometimes switch between them. A fireball in the light world might resolve into a block of ice in the dark world. As the player builds the world they will find a lot of interesting balancing acts going on, especially if they can cast spells to shunt things from one world into another. For example, a fireball followed by shunting the ice from the dark world into the light world for a double-whammy.
Anyway, those are just quick thoughts. The core point is that spells can only be as interesting as the world they inhabit.