Yeah but I'm kinda taking it as read that any spell SD put in the game will be useful I mean I know it's another arbitrary comparison with Master of Magic (forgive me as it's really my only frame of reference for spell use in a 4x game), but I've never felt while playing that game that there was a huge surfeit of unneccessary spells slapped into it.
The lack of other 4X spells is exactly why this game needs to be made. There really arn't many (HoMM and AoW are the main ones). But anyway, yeah Master of Magic could use some balancing with the spells, but for a game in the early 90s it is pretty good. If you want useless spells, may I direct your eyes to Age of Wonders. IN master of Magic, there is really only a few direct damage spells, so you only ran into a problem where you were having to pick between two spells that did the same thing when you took multiple elements. I vote that the solution to this is create a new type like water+wind=ice (see another thread that talks about spell ideas. there are several). Non-damage spells came in the forms of summons (which were pretty much 'you got what you paid for' in mana) and strange effects (which were usually pretty self-explanitory, easy to understand, and usable). AoW had a few spells that were maybe not even really worth it (I can find specific examples for you if you really like). More than never I'd look at my research list and say "jeez, I don't want any of these spells."
In this game the direct damage spells should have clear tiers so players don't have to waste time figuring out which is better. I think in MoM this was further squed by the ability to channel additional mana into the spell without being sure what the direct result was. The idea is a great one (to channel more power into it for better results), but the UI for it wasn't that great. If it is repeated it should be easy to set straight once the beta comes out (or just several options for the power level should be available that are better explained than a sliding bar and seemingly arbitrary number)
I'm assuming SD wouldn't do anything that stupid
well, I hang out with enough designers to tell you, it isn't stupidity that would cause it. Just maybe lack of insight. Its an easy mistake to make, since it doesn't slap you in the face with a giant wong if you start treading the line.
Also, its like the old saying goes, "you can't tell perfection if there is nothing left to add, but when there is nothing left to take away" and it remains true. This tends to cause two extremes > nothing left to add means that there is a huge spellbook, or nothing left to take away which means you have a small but functional spellbook. If you don't want a small spellbook, but you don't want a mass of crap... there is a balance to be found that failing to find isn't a mark of stupidity by any means.
I think a solution to the daunting spellbook would be to create a TON of spells, then have pre-saved sets. So your standard set would include as many and only enough spells to make the game fun. But when you go into the spell-book editor you can pick others. (the only problem with that is that players are stupid and will probebly go for the 'complete spellbook' option on 1st go anyway dispite the default 'beginners spellbook' that was there at first)