This thread doesn't necessarily have to be restricted to Trek examples, but I'm interested to know what others think the ideal "build" for an organization like Starfleet would be. I personally tend to like the "big fleet" concept that seems more prevalent in some of the FASA and fan-produced technical sources, rather than going solely by canon. I tend to view Starfleet as primarily being the Federation's military force, with both a small number of dedicated warships and a much larger segment of vessels that can be multipurpose or geared for exploration.
As I define a military, Starfleet definitely is one. A military is a government organization with a license to kill anywhere. Police have jurisdictions limiting where they can legally kill, but soldiers can be sent anywhere, even within their own country, kill under their origin country's rules, and return without prosecution. Starfleet has a license to kill on behalf of the Federation.
As for fleet structure, DS9 makes it pretty clear that pure warships are not a thing, or if they are they are obscured by code words. In the Defiant's case, the code words was Escort, but that gives more credit to the idea that the Defiant as a pure warship is definitely unique in Starfleet. I imagine every ship is designed from the ground up as multipurpose, Defiant- and Prometheus-classes excluded, some may lean more toward combat, but will make up for it in other ways.
It makes sense, the universe itself may very well be the single greatest enemy to life in Star Trek. Good engineering and science have saved the hero ships more than good guns, and the majority of time is spend just treking around space, not fighting. However, the Borg, and the Dominion War had to have had a strong impact. The aforementioned Defiant and Prometheus are respectively the start of a trend and the affirmation of that trend in Starfleet's disposition.
The story of TNG is partly certain elements in Starfleet emerging from innocence and realizing the unrelenting hostility of the universe's occupants. Best of Both Worlds shows us people in the background were forced into thinking about the issue. Sisko shows us that thinking bore fruit but got waylaid. Voyager shows us the thinking finally took hold, where in the final scene Voyager is escorted by multiple Defiant class ships and a Prometheus class ship part of a larger fleet constantly guarding the Solar system. By comparison all the Solar system had in "Best of Both Worlds" was a few drones or small ships around Mars which were trivially swatted aside. "First Contact" also shows us the the fleet has been heavily updated at least in part, and it follows through in DS9.
All together, my expectation wouldn't be exploration fleets and pure combat defense fleets. I would expect every fleet to have a small core of dedicated combat ship to augment the force of every fleet. The captain on a Galaxy, or Nebula, or Sovereign class ship might act as fleet captain, but it's going to be the Defiants or Prometheuses anchoring the edges of the fleet or forming the core escorts of the flagship. For one thing it keeps things more flexible, and better fits what we've actually seen.
If we go towards the UFP as being more analogous to the UN rather than the U.S., then I think perhaps it's plausible that member worlds like Vulcan and Tellar might retain their own smaller local fleets, while also lending some or even a majority to Starfleet
The Federation is definitely more US than UN, or even EU. Member fleets would be more akin to National Guard units. The state to which they belong are able to command them as they desire, but the Federal government can call on the National Guard units to fight as part of the Federal armed services.
I also believe it is likely members, like the Andorians, have their own fleets acting as, if anything, a coast guards or outright system defense forces.