I still fervently despise the whole unit limit. Bot a unit per tile limit and a unit per empire limit.
It is neither realistic nor does it seem fun at all.
What will we do when we don't have any more buildings to build?
What will we do when our experienced units are outdated but we don't have the money to update them?
We will sit in the corner and cry. Thats what.
CIV is supposed to be a loose history simulator. The micromanagement is supposed to be linked to your improvements and your cities.
As for units, they are supposed to be stacks you send out to beat up other stacks.
That is how it works in CIV4 and that is how it is perfect.
Changing it is both evil and cruel.
I think you have it exactly backwards.
The completely unlimited building of Civ 4 is moronic. Once 100% of your population is military, you clearly can't build more military units. But the real limit is much lower - somebody has to feed them and produce their equipment. Then somebody has to feed and equip the people feeding an equipping the military. I would suggest that limiting the total number of military units you can have at any one time to be perhaps equal to the sum of the size values of all your cities +1, or something like that, would be better. You have 3 cities of sized 8, 5, and 2? You get to have up to 8+5+2+1= 16 military units. In the very early game this usually won't make much difference. As you move forward it would gradually become a real limit. This would make those happy resources really valuable - increasing the potential population of all cities by 1 represents a large potential increase in your military size.
If you have a finite supply of horses, and you do, then you should not be able to produce an unlimited number of mounted units. I would hope that a single tile allows more than 1 horse unit. Perhaps not initially, but some tech (sometime after the "enable horse pasture" tech, Animal Husbandry in Civ 4) could increase the number of resources provided by the tile. Or perhaps they could work synergystically - if you have just a horse pasture you get 1 horse resource, but if you also have a grain type resource it provides 2 if you allocate that resource to that purpose. Or both in conjunction - have a horse pasture, a grain resource allocated to increase horse production, and the latter tech then the one horse pasture provides 3 horse resources. Or perhaps more. Starting at 2 might be better.
If you have nothing to build in your cities, then build research or money. Or start a war so when your units die you can build replacements.
If your units need upgrading and you have no money, then produce more money by adjusting the sliders or building money. (Well, duh! It's like that in Civ 4 already.)
Units are not "supposed" to be disposable. That they are in Civ 4 is just how it turned out. There is no point in keeping it that way. Getting your people killed off should be a bad thing. "You 47 units go over there and take that city", you command. "The last group of 53 units you sent all died, so I don't think so. You are relieved of command." Say your generals. End of game. You lost due to incompetence. That would be far more realistic - bad leaders got deposed all the time in history, especially by an abused military.
I would hope that the 1 military unit per tile is 1 fully active unit per tile, otherwise placing units at strategic locations is greatly impaired since they would block your own units from moving through and you'd have to move the defender out of the way (loosing any fortification bonus, if they still exist) to let the others through. Perhaps something along the lines of 1 unit can be fortified and 1 unit not, and perhaps the unfortified unit that is just passing through does not even receive any defensive bonuses at all even if in a fort on a wooded hill (or whatever) plus give both units some significant overcrowded penalty, allowing the 2nd unit to pass through. But while this would be nice, and somewhat realistic, it is not necessary.