From a practical, storytelling standpoint, I'm not sure the actual dimensions of the ship matter much since it's an object floating in a vast, empty space with little to provide a sense of scale, aside from other starships and space stations. You probably want to know that a Klingon battle cruiser is roughly the same size as the
Enterprise and cannot fit into the
Enterprise's shuttle bay (and vice versa), but most of the time it's just a big object cruising through an empty void. All the ships are effectively the same size when compared to stars and planets and nebulas and so on.
I've written umpteen Trek books and stories and, honestly, I can only think of one occasion where I had to look up the actual dimensions of the
Enterprise, in order to figure out how many refugees I could realistically cram into it. Most of the time, the length and width of the ship doesn't have really have any impact on the plotting.
It's mostly an emotional thing, I guess. We're wired to think that bigger is more impressive and more powerful, so each new generation of the ship has to be bigger so it feels like the ship is getting bigger and better all the time.
Is there a tipping point where this becomes absurd? Probably, but it's hard to say where that it is. A light-year long maybe?