Steamrunner's design layout resembles Springfield/"3-nacelled Excelsior Variant"/Oberth. I can buy it as a scout.
Except neither of those latter two have the warp engines running
through the ship.
Sternbach's six big classes are explorer, cruiser, cargo carrier, tanker, surveyor and scout. For comparing these classes wartime/present day/video game ship types, but using only existing Starfleet ships from the show, I got this(with some examples):
Scout=Scout--example: Oberth, Springfield, Steamrunner
Surveyor=Escort/Destroyer: Defiant, Norway
Tanker=Frigate: Constellation, Cheyenne
Cargo Carrier=Frigate or Shuttle Carrier: Miranda, Akira
Cruiser=Cruiser: Enterprise, Excelsior
Explorer=Battleship--example: Sovereign
I'd flip your Scout and Surveyor examples. Scouts are basically Destroyers with better sensors. Surveyors are more dedicated non-combatants like the
Grissom in TSFS. What's more, his list was never laid out onscreen. Thanks to DS9, we know 'Escort' is a category, but not one that Rick included in his list. I'd also argue that cargo carrier and tanker are more mission profiles than dedicated starship types. We've seen a Cruiser -- the
Lantree -- running as a cargo ship. We
did see dedicated Starfleet freighters back in TOS/TAS, but by the 24th century, they just seem to be repurposing older starships for that role, instead. We also have Frigates called out in the first season of TNG, and also not included on Rick's list.
A revised list should be something more like: Explorer, Cruiser, Escort. I've done some deep delving into the classifications thing over the last several years and -- taking canon and fanon into consideration -- treat Frigate as an accepted shorthand for either a Cruiser that has been unbalanced toward combat or an Escort/Destroyer that's been given additional equipment to balance it out somewhat. If a base
Miranda has no roll bar (like the
Lantree) and is a Light Cruiser, the upgunned
Reliant is a Frigate. If the single-nacelled
Saladin-class Destroyer is fitted with a second warp engine nacelle and some science labs and better sensors, it becomes a Frigate (basically a
Loknar or
Wilkerson). Frigate, Scout, Destroyer, Surveyor, Cargo Carrier, and Tanker are all more apropos as mission loadouts, subclasses, variants, or similar alterations.
I also used Memory Alpha's then-exhaustive NCC list to match unseen-named classes with unnamed-seen classes (and classes for which we have proof there was a model but no proof they were really onscreen. I made some assumptions about NCC numbers that not all fans will want to use, but this work took many hours and I do not want to redo it at this point.
Snipped for brevity (a losing battle with me, I know). Your methodology is sound, and if it works for you, that's the best anyone can hope for. I personally disagree with your conclusions, because I manipulated the same sparse and too-often contradictory data toward different conclusions. I'll spare you the process and try to thumbnail my conclusions:
• Jeffries' system, if fully realized and used by TPTB in TOS, TAS, and the first several films, would have been in use from the beginning of the unified Human Starfleet in the 2130s up until the mid-23rd century. Human -- and, later, Federation -- shipbuilding capacity was such that most classes didn't come
close to hitting the hundred-hull cap each number block tacitly imposed. As indicated by the "Court Martial" wall chart, obviously this was starting to no longer be the case by that time period (NCC-1697). Also to go with Jeffries' unrealized notions, Starships/Cruisers have NCC prefixes, Escorts have NDD.
• Around the time of the TWOK/TSFS/TVH triptych, expanding shipbuilding capacity and the non-Human races participating more in Starfleet necessitated a re-work of the vessel registry system. Starting with NCC-2500, all registries were assigned, regardless of class or type, in sequential order ongoingly. Any apparent "blocks" would now be due to batch orders being authorized. The NCC prefix now just indicates an active Starfleet vessel, with NDD dropped. No more distinction, per Cartwright in TVH, between "starships" (
Saratoga,
Yorktown) and "smaller vessels (
Shepard). NX is still for Federation experimental starships, nearly always (allowing for the odd exception to show up) created by Starfleet, they having the most resources for research, development, and construction.
• This was the approach that was able to preserve the most material seen onscreen intact. The only things that need changing are the prefixes of the
Revere,
Columbia,
Grissom, and
Jenolan to NDD, and the shifting of the registries of the
Bozeman to 1841 and the
Constellation (the class lead ship, not the TOS one) to 1900. Any other approach I tried breaks either the Jeffries or Okuda system to some degree.
This solves the problem of enemies knowing how many ships Starfleet has
Not even remotely a problem. All the
Yamato's registry of 71832 tells anyone is that Starfleet has ordered at least 71,832 ships -- nothing in that number says how many are currently active, or how many have been built since.
So using this and the NCC chart as a guide, these are the matches I got:
Apollo--either USS Jupp or ringships from Unification
Bradbury-Voyager Prototype
Hokule'a-Excelsior prototype (the one that looks most like the final)
Korolev-4 nacelled "proto-Nebula"
Mediterranean-Ethier unseen Pegasus design, or real name of class that includes Centaur/Curry
Merced-DOES have unique model combining Miranda and Galaxy Parts
Rigel--Enterprise-C variant of Ambassador
Renniasance--Enterprise-B-type
Sequoia--USS Elkins
Wambundu-4 nacelle Excelsior prototype
Zodiac-USS Yeager from DS9 (assumes registry of 65674 was really 85674)
Overall not bad. I'm not thrilled with a Human name being given to a Vulcan ship class. I personally have the
Apollo class as the Phase 2 study model seen in the Qualor II yard, and the
T'Pau was not that class (
Surak works nicely, though, as we haven't seen
it, either). The
Centaur I gave the unseen
Chimera class monicker. I disagree with the
Rigel and
Renaissance interpretations you have. Definitely like your revision of the
Yeager.
We've also seen a number of alien ships like the Galor that don't seem to share this philosophy, and have their engines built directly into the hull too. So I would say it depends in part on the mission profile and the resources available, although a design with separate nacelles would still be more common.
There's a lot of unknowns there -- how do those other races' FTL drive systems work? How much higher are the levels of harmful substances and/or energies in the habitable volume, because of racial prejudices (Klingons be all, "What, you can't take a little radiation, you weak
pa'takh?")? How much efficiency loss do they accept to reduce engine vulnerability? Etc.
Star Trek registries are only vaugely chronological. 5xx, 6xx, 10xx, 13xx, 16xx, 17xx, 18xx, 19xx, 20xx, and 21xx are contempoarary
Leaving out that I treat the
Constellation's registry as an error, the
Republic was a training vessel when Kirk was at the Academy. I wouldn't really call it a contemporary of the
Enterprise as far as active service during TOS goes. 15 and up, though, yes. Bearing in mind that, at the time of TOS, the
Enterprise had been in service for over twenty years, and the hull number had been assigned about twenty years before that. Forty-odd years is a decent amount of time for the
Miranda,
Constellation,
Excelsior, and
Federation classes to come along after, with their own number blocks (and the
Belknap at 2200,
Enterprise at 2300, and
Menagha at 2400, prior to the big registry revamp).
38xx if you include the tug class)
I actually just handwave it as 1800 and make the cargo-pod-hauler a
Miranda subtype. The unreadable text we see on the TMP/TWOK screens I ascribe to the
Surya class Escort. Yes, I play fast and loose with some of the fanon designs, especially if they can be made to fit the canon with comparatively minimal tweaking.
Also, of the TMP era ships, the Constellation is the most advanced in many ways and is not older than the Miranda or Soyuz classes. It also has a higher registry sequence indicating it was later. There seems to be a change from before 2000 to after 2000.
While the
Excelsior's hull number (and probably a handful of additional vessels at the same time) was authorized circa 2265, the ship itself wouldn't be launched for a couple decades. While I figure some classes benefited from the tech coming out of the Great Experiment development project, at least earlier on the new classes coming out would be extrapolations, refinements, and upgrades of the
Constitution/
Enterprise design generation. We'd probably start seeing
Excelsior-derived designs leaving dock starting around the time of TVH (point of fact, from the angles of the models involved, there were two
Excelsior-class starships in Spacedock when the whalesong probe knocked it out).
I have relented about 2 of the study models. They were on screen with enough clarity and they fit in with the flow of ship design. [...] It appears to be made from an AMT Enterprise saucer.
It doesn't really seem to have an angle to the saucer edge. Plus no "blister" on the saucer top or undercut on the saucer bottom. Rather than cut off and fill in those features, they probably just took some foamcore, cut a circle, added the superstructure and extensions, and bondoed and painted it. The only recognizable kit parts to me are the Phoenix missiles used in the engines.
This image also has one of the older Enterprise study models. This would be the TOS equivalent of the USS Discovery. Again, experimental engines.
Why?
I see a pattern developing in Enterprise and the TMP movies. Lots of experimentation with making warp drive faster. It seems to be a Starfleet obsession. This would lead to building a lot of ships in low production runs looking for the next successful jump in warp power. These would lead to a larger variety of ships, none built in large numbers and probably a lot of them one-off designs.
My takeaway from TOS is that, since several alien entities propelled the
Enterprise at heretofore-never-experienced Amazing Blinding Speeds™ (warp 14 -- old scale -- at one point), Starfleet, having seen it's
possible, started trying to figure out how to do it without outside "help". Rather than an obsession with faster for faster's sake -- they'd seen it could be done, so the rest was just an engineering challenge. The
Constellationclass seems to have been an early attempt. Premise probably being "let's just add more engines". The result was what Picard describes as "under-powered". Probably no faster, but with better endurance (the
Stargazer and
Hathaway were
waaaaaaayyy out there). I can see that quad-
Excelsior being an attempt to do better starting with better engines, maybe launched sometime in the late 2270s or early 2280s -- between TMP and TWOK, but more probably after the Khitomer Conference.
As for scarcity, lack of evidence is not evidence of lack. In "Unification", no one on the bridge pointed to the Phase 2 study model and said "Hey, there's that thing -- they only ever built the one". So we have zero data from which to derive production numbers. If you want to say it was a one-off with experimental engines, cool. My personal take is it's the
Apollo class, predecessor to the
Ambassador class, and extant in decent but not huge numbers. Neither of us can claim to be right, though, based on what we know from onscreen evidence.
The other area to look at is versitility. We know the Excelsior and Oberth classes did not see many varients, but the Miranda certainly did.
The
Lantree and Sisko's
Saratoga I feel are base
Mirandas (albeit one with mission-specific outrigger sensor pods mounted). We saw enough
Reliant types that that is definitely one subclass. The "
Soyuz class" is another. The
Antares is a 24th-century update of the
Reliant subtype (same phaser cannons, new torpedo pod). But the
Bradford is different enough I consider it its own class. If it had the same number of engines in the same place as the rest, sure, but as it is, no. Be like saying the
Enterprise and
Saladin are variants of the same class because they have the same saucer.
The
Excelsior and
Grissom have at least as many variations.
Are the engines supposed to move on that model? Because if so that is
badass 
Sure seems like it. I like to think it worked, but not as well as hoped, and it wasn't dusted off again until the
Intrepid class used it to better effect a century later.
It could that by the TNG era, technology had progressed to some degree so it was possible to build some classes without necessarily needing to jettison nacelles in the event of an emergency. Just shut them down, while giving the crew more ready access to their systems for lesser repairs.
