Personally, I think a lot of lore-crafting videos (and indeed the show itself at times!) tend to work off the basis of Starfleet being "WW2-in-space" conceptually, when actually its more like "Age-of-sail-in-space".
This is leads to the confusion over nomenclature and ship roles.
Essentially, Starfleet and Starships are equivalent to something like the early US Navy. The whole Constitution class plus the Age of Discovery parallels make this rather explicit. This is why it is a "cruiser-heavy" force: the difference being more one of mission than actual ship design between a cruiser and a frigate. There are however still heavy frigates and light frigates, and arguably something like Picard's Enterprise D is more of a ship-of-the-line. The whole thing is Horatio Hornblower in space, and viewed through that lens the fleet composition makes a heck of a lot more sense.
Based on this, here's my suggestion for how to rationalize the ship nomenclature and roles, without using too much in the way of outdated nomenclature your average viewer won't understand. I'm mixing Watsonian and Doyleist reasoning here so hold on to your hats!
Battleship or Ship-of-the-Line:
I personally prefer the latter because it still get used occasionally in a colloquial way yet is less blunt than "Battleship", but since the two are derived from the same root, that may be a distinction without difference.
This is your Enterprise-D and Nebula class, arguably more of the latter because I feel like the Enterprise might be a special design as the flagship, possibly more of a battlecruiser in concept with a greater range and speed than the Nebula which is clearly a more compact and robust type of design meant to take a beating, and give it right back. The Nebula is the sort of ship that ought to form the bulk of the battle fleet, yet at the same time you might not see them so much on patrol as they are large and expensive to run (in terms of crew, if nothing else). But when they do appear, they'd be the center of a force, and you'd see maybe two or three of them. They ought to be treated as the sort of ship that turns up at major engagements such as Dominion War battles & Borg encounters and the center and pride of the fleet, available only in limited numbers.
Cruisers:
These are, for the most part, our hero ships. They are basically the same a frigates, perhaps heavy frigates, and they are assigned to individual patrols. They seek out new worlds without breaking the bank, they are the biggest thing in a backwater sector, and they are the bulk of the force. In war, a squadron of them can easily be thrown together to support heavier units, and they are some of the smallest ships that can fight in a line of battle.
This is where I'd put ships like Intrepid, Streamrunner, and Akira go. They strike me as the type of ship that have the same level of technology and strength as the larger ships of the line in a more compact package, and are probably worth at least two regular frigates in terms of strength.
In an earlier period, I'd argue that the New Orleans, Cheyenne, and Springfield go too, later being downgraded to frigate status.
This is the size & strength bracket good stories are built around, at least in terms of Star Trek. These are the kinds of ships that seem to have that right mix of big enough to be a threat, especially if more than one shows up in an episode, but small enough that among the ocean of the stars they are truly alone. From a doctrinal standpoint, they are also an extremely good compromise when it comes to firepower, speed, size, and range. They probably aren't perfect for every mission, but they do what you need them to do for most missions, and they are adaptable enough that if you assign several to a mission, you can be confident that they will be able to meet almost any need.
In engagements like the Klingon Civil War blockade, perhaps one or two of these ships would serve as the flagships for small squadrons. They'd be accompanied by some frigates as well, ideally
Frigates:
These could actually be the same rate or type of ships as cruisers, but assigned to more mundane patrol duties. The sort of ships that "hauls butt between starbabses" if you will. For both doctrinal and storytelling purposes, I think we can assume these are the light frigate\cruisers to our heavy frigate\cruisers than are our hero ships. They can still hold their own in a fight, and would appear in larger numbers for small fleet battles. Maybe half a dozen? This is really where ships like the Excelsior belong: they are still capable vessels, but not really first-class vessels anymore, so they are more the standard patrol ship now. New Orleans, Cheyenne, and Springfield can also occupy this role as time goes on.
Corvette:
This is where your tough, small ships live. Or not so tough, as they case may be. Smaller and less versatile than the cruiser or frigate, shorter range, maybe slower and older too. Or not. This is the catch-all category for small ships that don't necessarily fit into the larger fleet force but are still necessary for specialized roles. The Miranda and the Centaur fit here nicely, as does the Bird of Prey. They are the gunboats, convoy escorts, and various other special duty warships of the universe, and are associated with specific storylines (big wars, convoy escort stories, DS9 &c) that require a specialized ship. I do tend to think they ought to appear in significant numbers in fleet battles, and get exploded too!
Auxiliaries:
Older Mirandas sans weapons pods, and whatever the California class is lives here. These are your survey ships, your hospital ships, your science vessels, and all other things that a fleet needs but aren't warships (which is not to say they are unarmed!).
I think with this kind of fleet composition, you actually get a fairly well balanced force. I look at Starfleet as being more akin to either the Age-of-Sail from which it was inspired or a more modern type of Navy where all warships are to a degree multi-mission, or otherwise so specialized that they have their own unique classifications.
I'll be honest, the Defiant is... a problem child when it comes to fitting into any kind of nomenclature system. It strikes me as something akin to USS Monitor: extremely specialized for battle, but largely impractical outside of the specific context that birthed it. Yet, in general, arguably revolutionary in terms of hull form, firepower, and armor. The thing is in a class of its own, which is not to say Starfleet wouldn't have built more of them during the Dominion War (they'd be foolish not to, and they clearly did!) but that outside of the wartime environment they'd probably spend alot of time in mothballs at Qualor because their range is quite limited and they are pretty much pure warships without much use outside of war. You could probably get a fun story out of that somehow with post-Civil War parallels of a Navy that has gone back to business as usual technologically, and is sitting on some interesting ships that it's not making use of... that the main characters can break out of retirement!
If we are going for a TNG fleet that omits the movie-era ships entirely, then I think we end up with something along these lines:
Battleships:
Galaxy class
Nebula class
Ambassador class
In WW2 terms, the Galaxy class is equivalent to the latest fast battleship like Iowa, whereas the Nebula is a more regular battleship like South Dakota .
In Age of Sail terms, we are talking of 1st, 2nd & 3rd rate ships of the line.
Cruisers:
Ambassador class
Akira class
Intrepid class
I think that Ambassador class ships might also fit into this category, if for no other reason than we'd otherwise be a bit short on earlier period ships for it. Think of them as potentially razeed 3rd rates in Age of Sail terms, or extra large Heavy Cruisers ala Hawaii in WW2 terms.
In Age of Sail terms, 4th rates and heavy frigates, in WW2 terms heavy cruisers.
All of these ships have good enough lines to be hero ships, and are advanced, versatile, and powerful enough to make up the bulk of the fleet. They are probably lead ships of most squadrons, too, when not on independent patrol.
Ships like Hood, Repulse, &c on TNG arguably should have been Ambassador class (although for real-world parallel reasons I like that they aren't!).
Frigates:
New Orleans class
Cheyenne class
Springfield class
Probably the vast majority of the fleet should be made up of these little guys, and they should have seen more action in the Dominion War period (although maybe they did and were all early casualties?).
WW2 terms Destroyers, Destroyer Escorts &c in Age of Sail 5th rates.
Corvette:
Defiant class
Sabre class
In Age of Sail terms a 6th rate or even a gunboat, in WW2 terms Corvettes, PCs &c.
A tricky role to fill, and definitely one that Starfleet somewhat neglects though arguably not without reason as these small ships probably don't have as good range or versatility as a frigate, the later of which they probably have sufficient numbers of in peacetime. The Defiant is classed here mostly because it's decidedly a warship but otherwise doesn't fit the schema for revolutionary design reasons (see my Monitor analogy). Probably these types of ships are what you mass produce in wartime because they are "a ship" and somedays that is what you need more than anything else. You'd see swarms of them turning up in late war battles, in other words.
The result I think is an actually decent force, provided the ratio of ships is kept reasonable. Arguably in the show we see far too many battleships, and not nearly enough frigates or corvettes. Given cruisers tend to be the "right sized" ship for both Watson & Doyle its no surprise we see alot of them...
Also, I feel like the Enterprises' role as "Flagship of the Federation" is actually somewhat underutilized in TNG. Picard perhaps ought to have been a Commodore, and Riker a Captain, and the diplomatic nature of many missions emphasized a bit more (although they hosted conferences, shuttled about Ambassadors, and went to trouble spots enough I think to justify the moniker). If they'd had the budget, the Enterprise might even have assembled more ad-hoc fleets ala the Klingon Civil War, with Picard often being the ranking officer in the sector (this also helps fix the only ship in the sector problem, by simply making Enterprise the true flagship and thus responsible for whatever the conflict of the week is due to seniority). You could have even filmed stock footage of it and reused it!
