As a military historian who has written and lectured on topics as different as Alexander the Great's Army's Drill Manual, the tactical differences between British, French and Swedish infantry in 1705, and the Deficiencies of the German Tiger tank of WWII, I can say that Civ VI's Unit Selections and Upgrade Paths Suck Like a Starving Leech
First, they have distinctions (Melee - AntiCavalry - Ranged) that are increasingly artificial as the game goes on, they have artificial 'requirements' for Upgrading or building units, and, finally, they have neither incremental Upgrades nor Any Upgrades for some entire classes of units for a looooong time.
First, let's take Spearmen/Pikemen: the Basic Units/Weapons of the majority of civilized and semi-civilized armies in the Ancient, Classical, and Medieval Eras. Firaxis seems to have been distracted by the evidence of the Roman Legion: the ONLY unit of primarily swordsmen in the World prior to the early Tercio, and even that unit only had a minority of swordsmen ('sword and buckler men', to be exact) in a majority of pikemen and an increasing percentage of men with firearms. Spearmen are not anti-cavalry, they are Cheap to raise, train, and maintain. Men can learn to use a spear on a part-time basis and still be effective - see the Greek Hoplite Phalanx of amateur citizens or the northern European Shield Wall of, basically, farmers with shields and metal-pointed sticks.
Swords are more flexible and allow more flexible formations in a variety of terrain, but they require a lot of practice - professionals, in other words, which are either some set of militant Aristocrats who pay for themselves (Celts), or a Standing Army that costs lots and lots of coin (Romans).
Spearmen, then, should be Cheap to produce, Swordsmen gain the current bonus against them only in forest or rainforest or hills, but Swordsmen are more expensive and have about 4 x the Maintenance Cost of Spearmen.
Mounted Troops have two basic purposes: smash the enemy with the weight of the horse and weapons of the rider, or use the mobility of the horse to stay away from the enemy and Mess With Him from a distance. That's your Heavy versus Light Cavalry distinction in a nutshell, but the Light Cavalry all tend to be for
Reconnaissance (Scouts)
Raids
Pursuit
Whenever they get too full of themselves and start charging the enemy, they also start getting heavier and end up just like all the other 'Heavy' Cavalry.
So, for the 'Pure' Cavalry, you have One Type: Heavy 'Battle' Cavalry. Starts with a Chariot which may or may not have a range factor (Egyptians) but most will be Melee units (Hittites, Chinese, Celts), changing to Horsemen in the Classical Era, Knights in Medieval, Cuirassiers in Renaissance, Cavalry in Industrial, Tanks in Modern, Modern Armor in late Atomic.
The 'Scout' line should have a third Upgrade Path: Mounted. You can build or upgrade scouts to Light Cavalry in the Classical Era, which can become Dragoons in the Renaissance Era, Armored Cars in the Industrial Era (late) and Helicopters in the Atomic Era. All these Mounted Units (Uniques like Cossacks go in here, too) would use the Mounted Scout Promotions, and would as a rule be the fastest units in their Eras, but with Melee Factors not quite good enough to go 'toe-to-toe' with that era's infantry/Melee units.
ALL gunpowder units have a Range Factor, but many of them are also tactically expected to attack and 'close with and destroy the enemy' (to quote the US Army Infantry School). BUT the original gunpowder-firing individual troops had almost no melee factor at all: the matchlock musket was a clumsy 15 - 20 pound club and their only other weapon was a cheap sword that they were very rarely trained to use. Musketmen, then, are in game terms Support Units, to be 'protected' by someone else while they fire (Pike and Shot). The Fusilier, carrying the flintlock musket and socket bayonet, was the first 'universal infantry' - able to fire, charge, and defend against cavalry all by himself. The black powder rifle did not appreciably change this, because black powder smoke makes the extra range almost useless: you can't see a target more than 100 meters away after the first few volleys fill the air with smoke.
Okay, this could go on for pages, so let me summarize:
Scouts: Include a Mounted Promo line, can Upgrade to Light Cavalry (Classical), Dragoons (Renaissance), Armored Cars (Industrial Era), and Helicopters (Atomic Era)
Melee: Now incorporates both the 'Melee' and 'Anti-Cavalry' artificial definitions:
Upgrade Path: Warrior - Spearman OR Swordsman - Pikeman OR Man-at-Arms - Pike and Shot* - Fusilier* - Infantry* - Mechanized Infantry*
Cavalry: "Battle Cavalry" Only, primarily Melee Units: Heavy Chariot - Horseman - Knight - Cuirassier - Cavalry - Tank - Modern Armor
Ranged: Slinger - Archer - Crossbow - Cannon - Artillery - Rocket Artillery
Siege: Battering Ram - Tower - Catapult - Bombard - Siege Train (Industrial)
NOTE: These are all 'Support' Units, in that they have almost NO factor against units that can move faster than a Wall, and virtually no defensive factor other than the side arms carried by their crews
* = unit that has a Range Factor as well as a Melee factor, possibly among the lines of the Civ V Zulu 'Fire Before Melee' mechanism.
Many of the ridiculous and artificial units in the game now should be special incremental Upgrades or Promotions for units: Machine-guns add Range Factor to Infantry, for instance, and Antitank Guns/Rockets/Missiles would be an Anti-Mounted Upgrade Line for Melee Units (or Helicopters = Promote to Gunships)
Of course, to make these Upgrades really work, we also have to massively Improve the current Abomination of a Tech Tree, but that's for another thread entirely...
