Right, it's a great idea and I thought about this. Games where you design your own units (Master of Orion, Galactic Civ, Alpha Centauri) have a lot to recommend them. However, I see two issues with a unit design workshop mechanic:
1) Intimidating/confusing for newcomers.
2) Thematically doesn't fit with Civilization (since the game is about re-creating history).
Two minor points. First, it doesn't need to be confusing for newcomers. MOO and SMAC aren't confusing, really. You start with relatively few options, and gradually increase the number of options as the game develops. That gives players the sense of "Ok, I can build a warrior with a shield, or with a heavier club" to start with, and gradually gets them used to the system before they're having to ask themselves "Should they ride Arabian horses, or a larger European draft horse? What material should I make their lance tips out of?" or however in-depth you want to go.
Second, I'm not so sure that Civ is meant to recreate history. I think it's meant to recreate in a very general sense the development of civilizations, but not necessarily history itself (although I do tend to play more historically based games, and focus on "Earth maps"). I think it's worth noting this if only to address how other folks play the game, and the degree to which we pay attention to history in coming up with theories on how the game improves.
I believe that the concept works much better for a science fiction theme, as anyone playing will expect "spacey, futuristic" stuff and you can make up various ships without any real immersion problem. I don't think it works as well for Civ though; there's sort of a general expectation that you're be using swords in the Ancient Age, and then rifles in the Industrial Age, and so on. Designing a fantastical unit (guys riding llamas and wielding halberds?) would be rather confusing, not to mention overwhelming for inexperienced players. I'm not saying it couldn't work - actually, the whole concept would probably work better with a randomized tech tree - I'm simply not sure it's the right fit for Civilization as a series.
Oh well. It's not like this game is going to be created anyway.
I think it could be made to work. It depends on how in-depth your system gets, and what options you give players. Some of that would necessarily be tied to the tech tree. So, for example, prior to discovering bronze or iron working, you could make warriors with, say, bone armor or no armor, stone axe or wooden club or stone spear, hide shield or no shield at all.
When you discover bronze, you can build units that use bronze weapons and armor, and would have new abilities to make wooden items with more detail (kind of like "neutronium armor" for MOO). So, now you could build a bronze-tipped spear, bronze armor, bronze swords. Eventually, you'd discover horseback riding, and that'd create an entirely new unit type, which would have its own set of designs taht you could use (IE: sabre cavalry vs. lancers, and so on).
As the game wore on, certain unit types would either be removed or would cease to be effective against your enemies. So, your cavalry units are phased out and replaced with tanks and helicopters.
The system COULD be made to function and could be fun, but the devil's obviously in the details.
I think these ideas would be great for Total War, a Master of Orion spin off, or a new series entirely but I don't think it's good for Civ.
I think Civ is very much a macro based game, and by that I mean it's a game where the appeal comes from managing your empire and seeing your civilization evolve.
What I think Sulla wants is to make the micro management in the game more involved than it has any right to be. To put it bluntly, there's a reason why you don't settle cities in Total War or in Heroes of Might and Magic, it would take away from the experience. You would have so many more things to do, it would obfuscate the ultimate goal.
This is what turned me off from HoI3. By the end of my experience with that game I didn't even know what was going on.
I personally think Civ works better when you don't have to micro everything. If you disagree fine, but this is not where I want the series to go.
Again, I think Sulla's argument (and pardon me if I'm putting words in your mouth here) is that, if we accept the premise that people want more tactical combat, we need a two-tiered system. And on that score, I agree wholeheartedly. You either need a two-tiered system or a MUCH bigger map.
Or you scrap the idea of "micro" combat, and go back to "macro" stacks/armies of some sort with some other kind of limitation so a gajillion axemen can't stand on a single tile.