but in the end, the real reason is that George Lucas thought that big numbers are cooler than small numbers.
I disagree. I think the reason is because it's much more relatable to real world fighters airplanes which from WW1 until today have almost all of them been single pilot planes.
If you look at star wars in general the genius about it is that all locations, characters and vehicles are in some way instantly relatable to real world counterparts which helps massively to build immersion and feel attached to the world.
I think that you are both right... of course... when you make a movie everything need to be relatable to something or the audience will not get what the makers are trying to convey which most of the time is a feeling of some kind.
The enormous size of big capital ships IS cool and give a certain feeling... the problem comes when you try to back fill their use after the fact. These ships are so massive that it is almost ridiculous. If you ever tried to make a ship that massive in Aurora you could fit so much stuff in it that it becomes unreal. From an Aurora perspective such large ships make very little sense. The capabilities of these ships from the lore simply don't make them justice in comparison with the smaller ships, such as the Corellian corvette above that have allot more realistic proportions from an Aurora perspective.
Now. getting back to "fighters" in Aurora is that I don't actually like the term fighter for the reason it give people the wrong impression for what they really are... small space ships.
I think that missile fighters have way too few crew than for example a beam fighter that usually need something like 15-20 crew (unless it is a Gauss fighter). Box launcher require no crew, small missile fire-controls does not require crew and other small sensors does not require crew either. In my opinion these things should require crew to operate, even box launchers given the size of these system should need at least some engineers to maintain. Sensors certainly should need crew to both operate and maintain even if very small, at least one crew per system you attach no matter how small.
I do agree that you should be able to opt out of using commanding officers on small crafts in favour of other more important positions such as executive officer or commander of a CIC on a capital ship. Fighter should have the lowest of priorities followed by FAC and then as bridge crew of capital ships. It is is irritating when a fighter gets commanded before you get a CIC officer on your most important ships for example.
Sure you can solve it with more Academies and you probably should build enough of them, but sometimes pure chance will make sure that some skills are not distributed in enough quantities so there might not be enough tactical officers and most of the ones you have goes to useless fighter positions instead. So you always need to over produce officers so you have the ones you really need not just enough of them.
It would at least be good if you could set the priorities our self which positions are the most important.
Now... I might also think that in the same spirit of balance one should perhaps think about the implication of building thousands of small stations and fighters before doing that in the first place as well. Sometimes building larger vessels or stations is what you should do to preserve good leadership, the same things goes for ships. Large ships will be able to much better use good leadership... so either you match the Academies with the amount of ships you build or you will sit there with lots of ships and stations but no good officers to command them.
When you expand your fleet you must also make sure you expand your academies as well to fit the fleet you build. So if you build hundreds or thousands of small ships/fighters/stations you need to match that with even more Academies as well.