Sorry about the long post. The short point -- you can't make artillery realistic until you make all of warfare realistic!
Its way too simplistic to talk about the effectiveness of artillery and or ships in generic terms. History is fairly complex.
In ancient times, capture of cities was rarely done through medieval manners, with artillery causing a breach. Storming with rams and scale ladders was also rare. There were two main ways to capture a city -- treachery and starvation.
There are legions of examples. In the Pelopennesian War, there was only one city taken by direct siege, I think. Most were taken ultimately by the two common methods.
The later Greeks were better at it, because they learned about seige engines and especially archery. Demetrius ultimately failed to take Rhodes, but he showed the later Greek ability at siegecraft.
The story of Troy, although mythological, was basically treachery (troops entering the city at night). The story in Herodotus of how the Persians took Babylon was through Zopyrus pretending to be a traitor for Babylon, but then opened the gates for the Persians. Alaric captured Rome with a real life version of the story, having soldiers of his 'captured', enslaved, and then one night on a signal gathered in strength, overtook the guards, and opened the gates.
Starvation is the oldest technique in the books. Look at the seige of Tyre by Alexander, a marvel by both sides, which mostly came to Alexander's attempts (finally successful) in cutting the city off by sea. Athens is finally starved into submission at the end of the Pelopennesian war after their fleet is lost at Syracuse and then at Aegospotomai.
But the Romans were a different story. Their engineering ability was very advanced, and they had substantial archery and accompanying troops. After the Marian reforms, they had essentially a fully professional army. So they capture Jerusalem by storm after the revolt, beseige and take Alesia (more of an outside fort), and many other examples. However, even they usually used the old fashioned methods.
Fortification was the main defense against mobility. The steppe horse warriors/nomads (Scythians, Sarmatians, Huns, Avars, Pechenegs, Cumans, etc.) were always a problem for the stable populations, who often turned to fortifications. Neither the Goths nor the Huns could take Constantinople, but they could destroy all the surrounding lands (are were usually bribed to go away by the Eastern emperor).
One of the reasons that the Mongols were so successful was because of their legendary patience. When the took Northern China, they hired the Chinese skilled engineers, and the Mongols learned siegecraft as well as any settled community (and even taught a few tricks)!
As stated above, ancients didn't move artillery around very much. It was constructed on site. It was ineffective against ships.
The change in Medieval Siege warfare was important from technology, but the key change was that sieges became the dominant form of warfare. The advent of castles and the DIFFICULTY in taking them allowed small, local powers to thwart more powerful ones. Castles sprang up over Europe beginning in the 11th century, and siege warfare became an art.
Of course, we did have breakthroughs in siege equipment, especially trebuchets, although these were hard and expensive to build. Siege warfare is now so common that all three techniques are still in use -- treachery, assault (including advanced techniques like mining), and starvation. Indeed, the main way castles were taken was by surrender! That is, the defenders recognize that there is no out. However, when Bohemund got to the Levant during the first crusade, after seeing the walls of Tyre, he immediately began looking for a traitor. Remember the saying, 'There is no city that can't be captured without a wagon full of gold'.
The advent of cannon didn't end fortresses. They did the the OLD fortresses of large walls, and they changed to lower thicker ones. Soon, cannons peppered coastal defenses.
In real life, the range of cannon was small by naval standards. Therefore, coastal defenses could fire at ships that came close. Coastal artillery was effective at defense, but can't fire at ships passing by. Conversely, they were devestatingly powerful because a coastal battery required the cannon (or later artillery) itself to be hit -- the coastal artillery, of coarse, can aim at a lot of places on the ship, like the deck. Therefore, coming in on coastal battteries was very hazardous.
On land, siege warfare became more and more scientific. Vauban is often considered the greatest siegemaster of all time, both in defense and offense. We now have rings, and all kinds of offensive and defensive structures. All of the old tricks, mining, cannon, and of course starvation are all present, but Vaubon basically felt that eventually the attacker with enough resources can overcome any defense. Treachery isn't as common since defense is no longer based on a main gate.
Fortresses continued to be used, up to this day. In WWI, the key fortresses like Warsaw and Verdun dominated a lot of the warfare. At this point, fortresses can be taken only with artillery. Surrounding and starvation still matter but artillery assault is now the predominant form.
WWII showed fortresses at both ends. Why were fortresses less effective? It wasn't technolgy of assault and defense, it was the mobility of armies. So, the French built pretty much an impregnable fortress, but the German defeated it by going around it. This was easier in WWII because of the mobility of the armies. However, detailed sieges of Leningrad (where the Germans could take the city, but wanted to avoid heavy casualties, so tried to starve it), and Stalingrad, wehre the Soviets surrounded the Germans and went back to cutting supplies as the methodology. Rommel defeated the British in hte desert but originally couldn't take Tobruk with his small, mobile army, but did later. The Belgian forts, which were supposed to slow down the Germans as a key part of the French/Belgian/Netherlands defense, were taken by paradrop.
At this point also, artillery is absolutely devastating against ships coming close. In the Russo-Japanese war, the Japanese captured the Russian artillery and turned it against the Russian (Pacific) fleet, destroying it at Port Arthur. Running fortresses was considered heretical. Of course, this became Churchills' lament about Gallipoli. Churchill felt that the British fleet could run the straits and have 'acceptible' losses, but with enough battleships Britain could force the straits, bombard Constantinople, and force a peace. In actuality, the British took losses while running the straights (mines here were also an issue) and the Briitish fleet backed down in the middle, making it a disaster. Commanders were ALWAYS terrified of getting close to coastal artillery.
So, where does that leave us with the game? Well, to be honest, nowhere. You can't have realistic siegecraft if you don't have realistic combat conditions. And this means all kinds of factors that people usually don't like addressing. Indeed, even for hard core board wargamers, the issues over the importance of dealing with logistics and elements are unending.
In ancient times, armies did travel together because of command issues and force concentrations. Fortresses were always an issue. In real life, the besieging army's greatest fear was disease, with starvation a close second. The longer the siege, the more likely some disease breaks out. (See for instance the Assyrian attempt to take Jerusalem/Judah after defeating the Northerrn Kingdom).
Artillery wansn't taken on long trips, it was built on the spot. Therefore, artillery should take time to use once the army is in place (this happens, for instance, in the Lords of the Realm games). The amount of artillery that can be used is limited by local resources. If the siege wasn't fast enough, the army would die of starvation -- sieges were often a matter of which side could hold out!
In addition, what about traitors? How do we simulate that in the game? Do we want our defenses, well built, to fall because the computer rolled a '1' and got a traitor right away?
Worse, what about lines of communications and real fortresses? In real life, the city wasn't often the forterss itself. It was often outside the city, on a key piece of terrain. The attacking army, of course, can't leave the other army in the high terrain.
But we don't have that in the game either. The risk of getting cut from the army's base isn't in effect (why -- because , again, the attaking army doesn't starve. It doesn't need messages from home making sure there isn'at a new war, revolt, etc. The computer tells us all of that.)
So, in CIV III we had stacks of doom. The stacks of doom I made would NEVER have worked in real life -- they would have starved to death, been ravaged by disease, and ran out of artillery ammo way before doing too much damage. As I said, real life artillery didn't move. Also, the finances of the game aren't realistic. The armies I created would have been about 100 times my real life GNP.
Once cannon is invented, we have new issues. Again, though, disease, money, treachery, and especially supply need to be factored before we can make artillery 'work' properly. In real life, there would not be defenses of the enemy in the city. There would be castles all over the place with small amounts of defenders. There would be a large enemy field army. Taking the fortress all the way is very hard and tremendously expensive. What we need is a die roll to determine if the castle surrenders, based on the ability of the field army to relieve it. The defenders are also hoiping that the attackers run out of money, time, food, or get hit with a disease.
Now, don't get me started on modern times! Here, once the large armies expanded, the ability to keep the army together (in a stack) was impossible since the army couldn't be supplied. Also, the ability to flank the army was too important. With large armies, railroads to bring supplies, and modern needs, 'lines' and 'fronts' replaced armies. A stack of doom would easily be defeated by a mobile group cutting its supply lines, so a whole line needs to be created to stop that tactic.
If we want to have realistic artillery, my view is we have to have realistic warfare and combat. We need supply, disease, treachery, issues of lack of communcation. Warfare would have to be far more random than in the game. Are we OK in the game that your Assyrian army is defeated by a plague? If we had these issues, we wouldn't need to stop stacks of doom since the probability of the stack dying of disease would be so high.
We then would have artillery built on site. We would have to have realistic fortresses. We could have artillery take time to build -- unfortunately, the turns in this game are very long so a Lords of the Realm solution doesn't work. Maybe they are bought for cash on the spot? Or is this an exploit? The amount that can be used is a function of terrain. We need treachery and similar effects.
In order to simulate warfare in more modern times. your army should have a 'line on supply'. If it doesn't, it is in trouble. The enemy then stops you dead if they occupy any point between you and your supply base. So, if you set up a stack of doom, they simpyly take troops along the supply line and cut it, and your army is dead. So you don't have a stack of doom any more. Then, artillery can act as it should, taking the fortress when it gets there, without the other effects.
Please note that a lot of ancient civilizations were devastated by a series of calamities that the game doesn't simulate.
Is this a better game? I think CIV is trying to reward good play. We build an army, and attack. I can just think of posts to this forum -- 'I built an army with 50 units which should have wiped out the enemy in 10 turns. But the computer CHEATED! It said my army got typhus and wiped out 2/3 of my units. Now, I can't defend my capital. This game #$^%#&!!
What we have is unrealistic warfare. Therefore, to make the game 'work', we need unrealsitic artillery.
Best wishes,
Breunor