I stopped reading after post #91. This is where DrPep posted a suggestion on a different way for artillery to work. I don't think this is something that could be implemented in Civ4 because of the large ammount of reprogramming that would be required to accomplish it, but it is a great idea for Civ5.
Taking DrPep's suggestion and putting it in my own words, here is my idea:
Artillery units can be used to reduce city defenses, just like in Civ4. When the cities defenses are zero, the artillery unit can't be used on its own. They can move alone, but can't attack alone. To be used in an attack, the unit must be attached to an offensive unit. When that unit attacks the artillery unit fires a support shot. This shot can cause damage to the primary defending unit and can cause collateral damage to other unit in the city.
When defending artillery units work as they did in Civ3. They fire a counterstrike against the attacking unit. The can also counterfire on artillery that is bombarding defenses.
To put all of this into game terms:
If a Swordsman attacks a Catapult, the Catapult is captured or destroyed.
A Catapult bombards city defenses the same a Civ4. To attack, the Catapult is attached to a Swordsman. The Swordsman attacks the opposing unit (whether inside or outside of a city) and the Catapult fires a support short that can damage the defending unit and cause collateral damage to other units in the tile.
A Catapult can be set for defense inside a city. If an enemy Catapult bombards the city defenses, the defending Catapult will counterfire on the attacking Catapult. If successful, the attacking Catapult will be destroyed. If a Swordsman attacks the city, the defending catapult will fire against the Swordsman. This can only damage the Swordsman, it can't kill it.
Using this system would make artillery units more realistic for those that want realism and would keep it balanced for those who feel gameplay is more important. The realism would be artillery bombarding from a distance and not being at risk the way Civ4 artillery is at risk. Forcing them to be attached to units means you can't just bomb a cities defenders down to almost 0 and then sweep in with your offenseive units nad wipe them out with no effort.
More aspects can be added to this but the above would be the basis of the new design.