In general, I don't think there's any "optimal" strategy, everything is very situational - depends on the terrain, the units you have, the units your enemy has, specific promotions, etc. Still, I do have some methods I can share.
In general, to capture a city, the majority of the damage I inflict is from ranged attacks. I do use melee units, but mainly to protect the ranged ones and to soak damage from the city.
So, I move a few melee units to range 1 of the city but just let them fortify themselves there. Then I move ranged units - preferably siege units - to range 2 of the city (or more if it's later in the game and I have units with greater range), and pummel it until it drops to 1 HP, then a single melee unit can capture it.
Using melee units to attack the city is fine if you have enough of them - it certainly speeds the process - but since melee-ing a city is often costly it often leaves them too vulnerable to bombardments from the city or from other enemy units on the enemy's turn.
This is usually uniform across all ages, except that when flight is introduced I also use planes to damage the city, and also once I get range 3 units I try to soften the city as soon as possible, even before the core of my army gets there, just to make it quicker.
There's no rule for how many units you need. Sometimes just one is enough - a heavily promoted Rocket Artillery can bring a city down very quickly, then any single melee unit, even a weak one, can capture it. In the early game, when all you have is archers and warriors, you might need a few more - but almost always, when I think how many units I need to conquer a city, the decision is more about how many units the enemy has around the city, than about the city itself.
Finally, keep in mind promotions can sometimes play a large role. Bombers with the siege promotions are amazingly good against cities. Siege units with the logistics upgrade (two attacks per turn) can also bring them down quickly.
Capturing Cities on Small Islands
I understand you have a problem with this specific scenario. A few tips:
Use ships. It's true that early on ships don't do a lot of damage, but all you need is to be able to do more than 2-3 HP per turn and it adds up. If you can't reach that number, just get more ships. Range 3+ ships are especially useful, and the +1 range promotion isn't that difficult to get, especially if you level up on barbarians first. (and yes, even Triremes can get that promotion). Note: as of the Gods and Kings expansion, there are both melee ships and ranged ships, and you can capture cities with melee ships; I recommend this for small islands. Embarked units can defend themselves to some degree against ships, but they will still take heavy damage. The advice to do the majority of your damage with ranged units still stands, though.
Get the "amphibious" promotion for your melee units. It removes the penalty when attacking across rivers and when attacking when embarked, and this can really help for capturing coastal cities. Still, remember embarked units are very vulnerable - not only to other ships but to ranged attacks as well.
Once you get flight this becomes a different deal. Bombers can devastate cities and even fighters do a little damage, just make sure you have a nearby city or enough carriers. The AI does love its AA, though, I often find myself bombarding AA guns with ships and only them use the bombers. One final tip about flight - in my experience it's better to use fighters to air sweep and clear the way to bombers, than to use them to directly damage units.
Also, regarding your general problem with embarked units getting killed by barbarians - just make sure you escort them with real ships. Even Triremes have decent enough range and sight that they can effectively guard a few embarked units. Also remember the movement penalty of entering a tile adjacent to enemy units - that means a ship can effectively block the passage for other ships even when it only takes one tile in itself.