Each weapon has its niche, although there is definitely a way to optimize as opposed to role-playing.
PW, PWL, PWI are the weakest weapons and are pretty much only good for shooting at enemy light infantry. However, they are very light and thus you can deploy a lot of them for the cost and/or tonnage invested, meaning that they are your base infantry weapon for the "meatshield" troops protecting your more valuable CAP/AV and are essential to keeping your heavy weapons alive as long as possible. PWL are the most effective as a pure meatshield due to small size (3 tons), however PW (5 tons) are 16x more effective at killing similar-tech enemy units due to how AP and damage scaling works), thus while you cannot deploy as many PW infantry they can kill off enemy infantry more effectively than PWL infantry. Thus, PW are better against infantry (more lethal) while PWL are better against armored units (since neither weapon type is very lethal, the bigger meatshield is better). I would say that PWI is generally a waste of the extra size point in the early game, but it's useful later on when the enemy infantry has power armor as the extra AP gives you 50-60% more killing power.
CAP/HCAP is a pure anti-infantry weapon, nothing is better to mow down the enemy legions. As above, CAP is usually fine early on but once you get up in tech and regularly face power armor infantry HCAP has more than double the kill rate and is worth the tonnage.
Anti-Vehicle similarly is a purely anti-armor weapon but with only one shot does poorly against infantry (hence, meatshields). The calculus here can actually be rather complex as you don't gain anything from overmatching the enemy with your AP/damage - for example, if you face equal-tech VEH or STA then MAV will perform just as well as HAV for a lower cost and tonnage; however if you face HVH then HAV is clearly superior. Of course, without prior knowledge of enemy tech and doctrine you don't know what you're facing, my point is just that bigger is not always better.
Autocannons are the medium point between CAP and AV, and can do both roles adequately but excels at neither. General advice that you'll see will be to avoid using AC and preferring CAP+AV instead, however there are a few situations where AC is a good choice. The most obvious is as a defensive weapon on your CON/GEO/XEN vehicles if you choose to give them defensive armaments, since these often operate separately from your main forces and specializing only against infantry or armor is risky (note, however, that many people choose not to arm these vehicles and just give 2x of the special component). Less well-known, AC performs well against lightly-armored, low-HP units which HCAP lacks the AP/damage to reliably OHKO but AV overmatches. This mostly means infantry with multiple levels of power armor + HP mods as well as light vehicles.
Personally, I use AC as the third slot weapon on heavy vehicles that are meant to be Main Battle Tanks as that way they are equally good against every possible target: (HAV+HCAP+HAC).
I differ in opinion here, as I think generally the performance of 1xCAP + 1xAV > 2xAC in most cases where you'd want CAP in the first place. Thus you'd get better results from having a mix of HAV/HCAP/HCAP and HAV/HAV/HCAP vehicles, although this does require a little bit of extra research and micromanagement. However as mentioned AC does well dealing with heavier infantry so it may be preferable.
Bombardment weapons have the ability to offer supporting fire. For LB this is basically a useless ability (note: a lot of people use LB to model mortars, which they are
okay at doing but ultimately don't contribute much that HCAP doesn't do much better in most cases. If you want to optimize, avoid using LB), for MB and larger this is very useful as they can fire from support or rear echelons without much risk of being attacked by the enemy front line. MB can fire from the support echelon, MBL/HB can fire from the rear echelon which is even safer. Since the main usage of bombardment is in a generic artillery role, the exact stats are usually not discussed much; however, it's worth noting that at equal tech levels bombardment weapons are usually the strongest killers of static units, particularly HB which will outperform AV against any static armor level.
Also relevant are AA and FFD components, both of which function best in a rear echelon formation although AA can be used for direct fire in a pinch. Note that FFD does
not have any relation to the artillery role, and instead is only used to enable orbit-to-surface fire support and bombardment (this is common confusion not helped by the name, which should be orbital fire direction/OFD instead, or better tactical air control/TAC to match modern military terminology...but I digress).
So, best practices for optimal formation design: in the front lines, you want a mix of CAP and AV in most cases to have a balanced ability to counter both infantry and armor elements. Some people will have CAP-heavy formations that fight the initial engagement to clear out enemy infantry before bringing up the AV formation, to avoid wasting expensive AV shots against cheap infantry units. In the support echelon you usually just want MB formations to support the front lines. The rear echelon holds everything else: HB/MBL, AA, FFD, HQ, LOG, and so on.
Offensive and defensive formations differ: on the offensive, you usually need to maximize firepower per transport ton since you will be performing a combat drop, while on the defensive you need to maximize combat power per build point since transportation is not a concern. This means for example that on the offensive you want your meatshield infantry to pack as much bang for the buck as possible - PW/PWI, max HP, max power armor, and so on - while on the defensive you just want as many infantry as possible getting in the way of the enemy tanks while you shoot them with big guns - PWL, no HP mods or armor to keep the costs down. Philosophies differ, but this is how I tend to look at things.
APPENDIX: damage mechanics:
Both damage and armor-piercing work based on the square of the ratio between the weapon damage/AP and the target HP/armor, with overmatch capped at 100% effect for both quantities. This means you can calculate the total expected kill rate for a weapon against a unit base type, given racial weapon and armor tech levels, as:
Kill Rate = number of shots * (weapon damage * racial damage / enemy unit HP / enemy racial armor)^2 * (weapon AP * racial damage / enemy unit armor / enemy racial armor)^2This is how I calculate which weapon types are best against certain enemy unit types above. Note the corollary here - kill rate scales to the
fourth power of racial damage and enemy racial armor, i.e. tech levels. Thus, a difference in tech levels tends to have a much larger overall impact on combat results than how optimized your choice of weapons is.
