Consoles (of any type) don't affect your damage directly. They all add to your Skill, in this case Cannons, the same as adding points to your skill tree does. Consoles do stack, but all Skills have diminishing returns (which you can clearly see in the skill trees; the first point adds far more than the ninth point), so each extra console of the same type is less effective than the previous one.
A console to boost your torpedos may be more effective than the third cannon console, but I'll leave it to the maths wizards to crunch the numbers.
Actually, this is incorrect, each Console stacks linearly (this is true for all Consoles), it is their results that give the appearance of diminishing returns (particularly, in the case of Armor), but it is more a function of the formulae that govern certain mechanics, rather than an 'actual' diminishment in value. While it is true that the more points in a specific weapon that you purchase for skillpoints, the less value you receive in return (i.e. 1 skillpoint purchased in Phasers will give you +10 to your Phaser skill, while upper level skillpoints, 6th through 9th purchased, only give you +4 per point), each +# is linearly added to your final skill value; thus, the highest-level weapon-type consoles in-game (+26) will each add their entire value to your weapon skill (thus, the highest 'actual' skill total that you can achieve is +204 = 18/Energy Weapons + 30/Beam or Cannon Weapons + 52/Energy-Type + 104/Consoles, up to 4, if an Escort); therefore, each Console is equally valuable to add (and, usually, maximizing the consoles for the weapons that you use most often, usually, energy weapons, is the optimal choice).
In the case of Tactical Consoles, each console does directly contribute to your damage output (to illustrate, hover your mouse over one of your equipped weapons, see the damage output for the given weapon, then un-equip one of your Consoles which is related to that weapon, and witness the corresponding drop in damage output); this is due to the fact that your damage output is directly related to your skill.
I think I'm missing out an important mechanic for weaponry.
From my experience, when you are using beam weapons the further away from the target you are the more your DPS drops, so point blank they do excellent firepower.
With cannons you land exactly the same damage no matter the distance.
This might have been changed tho, haven't played in a while.