I played D&D growing up, and there casters basically had "charges" of magic. You could cast this spell once per day, and that one twice, and that's it. After your "charges" were used up, you couldn't do any more magic until the next day.

Video games initially copied that system before eventually moving on to a mana system. Mana allows for more flexibility, letting you choose the spell you want on the fly instead of having to choose at the start of each day. It also makes it easier to regain your magic power, either with mana regen or mana potions. It also allows for more types of loot, with items that increase/ reduce/ alter your mana amount and/or regen rate. The red health bar and blue mana bar are ubiquitous in countless games.

More recently there's been the cooldown method, where after casting a spell you can't cast it again for a short while. I greatly prefer this over mana. One of the issues I have with D&D is that it allows casters to be too powerful in some battles by using ALL their magic there, but then leaves them too weak for the rest of the day. From a design perspective, I think allowing these extremes makes it harder to balance and create interesting adventures. There's also a "fairness" aspect.

As a warrior, you get infinite attacks per day with your sword. As an archer, you get infinite shots. But in most games, casters only have a few spells they can cast. What's worse, they are then expected to spend the rest of their fights using a staff or crossbow or whatever. Have you EVER heard of a game where a warrior was only allowed to attack a few times, and then told they had to spend the rest of the day using wands and scrolls, because they're "exhausted"? This is the case with casters.

With cooldown you let casters cast far more often each day (infinite) while limiting how much they can cast in any one battle. I'd like to see mana disappear completely. Games long ago eliminated stamina for warriors, I think mana for casters should also disappear.

What do you think is an "ideal" magic system?

Two Worlds II has an interesting magic system - https://guides.gamepressure.com/twoworldsii/guide.asp?ID=10213

I do not think there is any "ideal" magic system, it depends on the game world and the lore, as well as how it is balanced with the specific game mechanics. what may work well in one game, may not in an other.

My thoughts:
-suitably powerful as to be worth using, but not so strong that using it is a complete advantage over other systems ingame.
-resource intensive but not so restrictive that you don't want to use it.
-useful non-combat spells are a must. Buffs that affect non-combat abilities, spells that can replace certain skills, etc.
-ability to combine spells with other abilities, such as combat buffs.

IMO, D&D magic system suffers from being too restrictive at low levels. Magic users only able to use 1 or 2 spells a day is far too restrictive, and we went out and developed a magic point system for our home brew games that was less restrictive, but pretty well balanced. That way every level 1 mage doesn't have magic missile cued up because that's the most useful spell at levels 1-2

1) Why do you think it should use resources at all?
2) You don't think duplicating skills devalues those skills?

I favor a system where casters work similarly to other classes. Other classes don't have to worry about resources, fatigue, limited uses of their combat options... none of that. To me, if you're playing a solo game like Gothic, you're going to fight the same enemies regardless of "class", have the same battles, and reach the same end. So to me it's less about if you reach the end, but how you do it. Do you like up-close combat (warrior), distance combat (archer), or sparkly tactical combat (caster)? I think too many gamers and devs have been affected by the D&D system, thinking that casters must be limited somehow, either with spell components, casting time, few spells, or whatever.

With a cooldown system, you can have several spells, not just magic missile, and it forces you to use different things to handle situations.

my ideal magic system would be some sort of Urban Fantasy story-oriented social manipulation mechanic powered by your characters terms of belief and faith vs. how much they force their Ego into the natural world and manipulated with fetishes imbued with specific power on a conceptual level and limited use ritualistic spells. this is kinda/sorta a thing in Planescape: Torment, but you can still cast Magic Missile into the darkness, so you can't win em all!

casting Fireball is not interesting to me, running a supernatural confidence game as part of a story is.

A mod for Skyrim is one the best magic systems I've played (ForgottenMagicRedone).

A good magic system in a game is very different from one in a book, for example, so if you'd have asked about the latter I'd have answered something else entirely. But in a game, just stick to mana. No cooldowns, those make no sense whatsoever, no hard limits, no consumables required. For particularly powerful spells there may be required items but not used up, just need to have them. If a stamina system exists, then it may be linked, though I'd still have mana as the restricting factor overall, with the stamina used the same way a warrior would, determining how much you can chain together without taking any moment to catch your breath. And to combine with the D&D concept a bit, may make it so spells up to a certain total complexity (so not hard numbers and definitely no limits for each level or such - can prepare lots and lots of weak ones or a few of the top ones or anything in between) can be prepared ahead of time and then cast instantly when needed, possibly even without a mana cost, while unprepared spells require mana and time. Possibly quite a lot of time for the particularly powerful ones.

I'm good. Magic should be limited if it is gonig to be as powerful as it usually is. Otherwise it would be unbalanced. Think of it like a candle; that which burns brightest burns shortest. A warrior cannot blast through hordes of enemies quickly; he will need time to do it. A powerful magic user on the other hand specializes in magics that can rend small armies to pieces in a matter of seconds. So this is the conundrum... why would anyone be anything but a magic user if their power is virtually unlimited AND they maintain the level of influence they already have.

As for duplicating skills, not all parties will have all classes, and there needs to be a way to accomplish some tasks without a locksmith or a weapon smith, or a skilled tracker. These spells may not be as effective as a master class of that type, but it will suffice in a pinch.

And I was talking table top game, not video game. As another poster said, I'd favor a different system in a video game.

I like it when mage's have powers that are very different from what other characters can do. So, for instance, I'm not fond of magic missiles. If my mage is just like a archer, I'll take the archer.

Things I like mage's to be able to do:
Summons
Druid-ish things like cause the trees to entangle people
Create an earth wall
Create night

What I think often makes mages overpowered is AOE spells.

I think mages should be harder to play because they take more creativity to use well.

I don't much care for mages having a limited pool to draw from - like mana - or like the arbitrary can only cast once per day. But I am for some sorts of limits. Like, a limit on how many simultaneous summons.

I know many old school RPGers hate it, but I actually like the cooldown system. I always think of mage classes as glass cannons with a tactical bent, and cooldowns make you think tactically about what to use and when, and if you overuse can leave you vulnerable. It's the perfect balancing act for such a class, IMO. I think cooldowns should be more punishing than games like Dragon Age tend to make them though.

Mana annoys me because it's like health potions, you don't want to use them unless you have to in case you run out, so you use them as little as possible. If I play a mage I want to be slinging spells, not trying to conserve.

The old method of so many spells per rest just ended up making me rest constantly, which was more annoying than limiting or fun. A per battle limitation would make more sense... I think that's how Pillars of Eternity did it?... and I like that. Honestly that could be my favorite but there are too few games that use it to really tell.

The "glass cannon" concept IMO applies to pnp. The original D&D game had a recommended party size of 6-10. With such a group, it's both easy and even preferred for people to specialize. Having someone who does nothing most of the game but will aid in boss fights was basically the mages' role. However by 3rd edition, recommended party size was down to 4. Other video games also have smaller party sizes, with solo not being uncommon. With so few, it's better to generalize.

So when it comes to video games I think the glass cannon view should be dropped. To be more versatile, casters should have more castings of reduced power. As I said in the OP, I think classes are more about how a player enjoys handling combat, be it melee, ranged or magic, so having a caster that can fight JUST as often with magic as the other classes use their melee and ranged attacks is preferable. If that means reducing spell potency, so be it. Just as warriors can choose sword or axe or mace or spear which are more for flavor than any distinct powergaming, I see the same with melee, ranged or magic. Let me swing my blade for 10 dmg, or shoot my bow for 10 dmg, or cast my spell for 10 dmg. How does it work?

Which is essentially what I think a cooldown system does. You can cast more per day, but less per battle. No more five fireballs in a row.

A good magic system should have:

Multiple levels
Multiple schools of magic
A good spread across classes, but not so specialized as to make useless things like Pyromancers. If you're making a class to specifically cover one basic element, you've gone too far.

A good magic system shouldn't have:
Arbitrary limitations on number of casts. Be it reagents, number of daily casts, or "time to cast".
Redundancies in classing. Say a subclass like the red mage which can only cast up to third level spells.
Arcane interference/antimagic for no reason. Either commit to an in-universe reason or don't bother. (Null bugs are fine, but the explanation in Tome 4 is kinda weak to me.)

It has a system to make it more powerful that is interesting,plus special rings to enhance the spell more.For instance,one spell I love is called Discord where it's an area spell that makes all that are in it fight/turn on each other and I just sit back and watch the mayhem,good for cleaning up a bunch of baddies as I never use companions.

Arx Fatalis. Innovative system. Really, really liked that one. Runes and hand gestures. Seems familiar, somehow...

I've played games where this is not the case. (For example, SaGa Frontier 2, where weapon and unarmed attacks use up WP, and you can't do decent damage without using up WP faster than you regenerate; by contrast, you can cast spells endlessly with the right equipment setup.)

To answer the question, here are some interesting systems I've encountered:

* Wizardry 8, Phantom Brave: Spells are split into different categories, each with its own separate pool of SP. You can increase your maximum SP by casting spells of that type (directly in Phantom Brave, indirectly via skill increases in Wizardry 8.) I like this idea, but I don't like how Wizardry 8 puts all the learnable HP restoring spells in the same category, and Phantom Brave makes it rather tedious to get enough SP to comfortably use mid to high level spells.

* SaGa 1 and 2, most Fire Emblem games: In these games, almost all (sometimes all) weapons have finite durability, and the spellbooks and staves that are used to cast spells are treated like weapons for this purpose. This encourages spell use because you can't just avoid the cost by using weapons. (Then again, I should point out that Fire Emblem lacks powerful spells of mass destruction, though they are present in the SaGa series.) The SaGa 3 remake does something similar, the one difference being that you can easily repair items in SaGa 3, unlike the other games I mentioned.

* Paladin's Quest and its sequel. In this game, each spell is a combination of 1 or 2 if the 8 elements, each character only has access to certain elements, and in the first game, you get better at elements by using them. (In the sequel, you get better at elements by killing enemies that yield that element.) Spells cost HP to use, so casting powerful spells puts you at short-term risk, but with the help of medicine bottles (or, perhaps, free healing items), you can keep using spells as your primary means of offense.

* Romancing SaGa: Minstrel Song: In this game, each character has a stat called BP. Characters start each battle with a certain percentage of their BP (depends on the character), and gain a certain amount per round (again, depends on the character). Both spells and attacks require BP to use. Some attacks, however, require DP (weapon durability, can be easily restored if the weapon hasn't been tempered) or LP (life points, if they reach 0 the character leavs your party (game over if this happens to the main character), can be restored just as easily at the inn). Items that can restore BP quickly are extremely rare, to the point where they are too valuable to use in most boss fights unless you abuse Phantom Warrior (if you've played Baldur's Gate 2, think Project Image). One could, of course, solve the problem by not making magic powerful enough to need its limits. Also, one could give physical characters such attacks of mass destruction, particularly if the game has technology advanced enough to allow for weapons like grenades or even nuclear bombs (as seen in the Game Boy SaGa games).

Problem with per-battle limitations is that they widen the difficulty gap between common enemy encounters (which are usually over so quickly that per-battle limitations are irrelevant) and boss fights (where the limits actually come into play). In particular, with just per-battle limitations, you can just blast common enemies with your most powerful attacks, and not have the strategic element of having to decide whether the enemy is dangerous enough to warrant using up your resources.