I know plenty about ship design, both real and fictional, thanks. I was specifically referring to Starfleet ships, and I’ll stand by what I said. With the exception of vessels like the Defiant, which is a pure combat ship, or dedicated science ships such as the Oberth and Nova, Starfleet tends to take a jack of all trades approach. Most of their ships are designed to handle a wide variety of missions.
Even Defiant is used for science missions when the episode demanded it. The problem is that in terms of looking at the ships you either have to take the approach of looking at them within the entire framework of the media (so script, writers' intentions, etc.) or a much more technical approach of trying to cut them out of that framework and put them into a system and framework that makes sense for Starfleet's ship design philosophy.
But even going into the real world, just because a ship can handle a wide variety of missions doesn't mean it's designed to. Yeah you could use an Arleigh-Burke-class Destroyer's sensors to study seafloor geology, but that doesn't mean the ship is
designed for that purpose.
I'd argue Starfleet's problem really is the same as Star Wars, where every ship is basically designed to be a WWII military vessel with a couple torpedo tubes and main guns, with the equivalent of helicopters (shuttles or runabouts) tacked on. Except Star Wars is a bit worse since every ship is
also a carrier. There's basically three exceptions to this, which are Akira (50s/60s Missile Cruiser), Sovereign Refit (Iowa-class refit), and Defiant (doesn't really match a role because it's basically trying to be a "starfighter" but you can make an argument it's a Coastal Defense Ship/Littoral Combat Ship).
And even the Oberth was used as a science vessel, a supply ship, and a transport ship for both Federation bigwigs (Admiral Satie) and lowly cadets (Wesley Crusher).
Ship roles in Star Trek are meaningless, when the modus is to either use an old model or use stock footage of said old model, despite whatever the script or dialogue might have said. Or use the Enterprise-D or whatever as whatever that scriptwriter decided the ship's mission was going to be for that episode.
Exactly, it's the problem of narrative soap opera storytelling and budget versus design principles, as you say. The Akira for example probably falls into today what we'd call a "Helicopter Support Cruiser" (not really used by the US but is used by some European navies), but gets labelled all sorts of things in Beta Canon sourcebooks and novels. Or the big gun reused from the Stargazer on the Freedom is a mining laser, prototype phaser cannon, or prototype phaser lance. Virtually the same problem for every other ship. Lantree/Miranda is also a great example, as Lantree was a supply ship.
It still happens in all sorts of franchises today. Look at SWTOR where they couldn't even be assed half the time to use a Republic or Imperial model for ship in a mission, so you end up with the default freighter. It's basically a single line of code to show the correct sprite/icon/model, but it still happens due to deadlines. NeoConnie from Pic S3 is also a case in point where it was put together in a rush in 2 weeks.