As far as in-universe explanations go, the way I've always seen it is that ships with lettered incarnations are meant to honor the contributions of a significant ship and crew to the Federation, or to note a ship of some widespread reputation.
The second Defiant-class U.S.S. Defiant (NX-74205) was to honor Sisko and crew of the first U.S.S. Defiant (NX-74205) for their service during the Dominion War.
STO's own U.S.S. Stargazer (NCC-2893-A) (from the Path to 2409 lore for those who don't remember) is an homage to the U.S.S. Stargazer (NCC-2893) for the novel Picard Maneuver.
And the Enterprise is...well, the Enterprise. Kirk and crew are just awesome.
Basically, if a ship and crew have done something particularly notable, then the name and registry are reserved. Other names are simply recycled into the pool for new ships and registries as needed. Ships like the Intrepid (NCC-1631), Constellation (NCC-1017), and Defiant (NCC-1764) are only known for being lost/destroyed/magicked over to the Mirror Universe/etc., and thus do not merit having their registries reserved for future namesake vessels.
I imagine there could even be a U.S.S. Voyager (NCC-74656-A) at some point. I wonder what kind of ship that would be?
The Stargazer part doesn't make sense, really. Kirk and the Enterprise saved how many countless lives throught their history? So for the sake of argument, OK the Federation wants to forever have a living memorial of that name, and number. Then it awards the Stargazer that same accolade for engaging, and destroying one ferengi vessel? A ferengi vessel that apparently used visual, line of sight, to track a target and engage its weapons. Doesn't make sense.
Neither does the renown "Picard maneuver". Here's why. At 9 million kilometers, you're not even going to see the Stargazer, unless it's next to a star. Even then it's only going to be a shiny speck. Second, optical illusions only work on the naked eye. Not on the targeting computers available in the 23rd which in more than a few episodes, have demonstratated the ability to track ships, even at warp. Once you've established heading, and bearing, you're going to know where he's going. So even if you could theoretically see the Stargazer at it's original location, the "Picard maneuver" might fool the naked eye, but it's not going to fool a tactical computer. If it did, the "Picard maneuver" would be standard doctrine in all space combat, and everybody would do it.
HINDSIGHT... Those really WERE the droids you were looking for.
I don't have the time re re-read through the PDF above, but I seem to remember one or two, again non-canon, ship that retained their original NCC code #s, but only a few.
Largely, and much like a modern day navel fleet, it the name that get's re-used/carried on to the next generation of ships regardless of Navel Construction Codes.
Here is a list of ship names from the US Navy. Ships named for cities or states are used again and again. So are the "classic" Navel ship name like Hood, Potemkin, Yorktown, etc.
btw - Found this thread by accident googling something Trek related but not STOL game related. Nice to see that you're keeping out of trouble, Tumerboy. We miss you on the CO board. 'sokay.