What type of ore you use to upgrade a weapon can fundamentally change attributes of the weapon

The normal upgrade path with hardstone and sharpstone doesn't change anything significant about the weapon, it just slightly improves the base weapon by giving incrementally raising base damage and slightly better STR and DEX scaling up to +10.

Along that track of +0 to +10 if you upgrade with a different ore at a particular point (usually at either +0, +3, or +6) then the properties of the weapon change. It can do things like remove the stat scaling from DEX and greatly buff the scaling for STR, or add fire damage to the weapon, or increase damage from backstabs and critical strike, or add damage scaling with the MAGIC stat, etc.

You are only allowed to choose one of these specialty upgrades per weapon.

So, you want to use bows, so you would want to find a bow you love (usually the compound long bow) and upgrade it to +3 with hardstone. At +3 (and only +3) the sticky upgrade path becomes available and you can use spiderstone to make it a Sticky Compound Long Bow. This will reduce the STR scaling of the bow (which wasn't that great to begin with) and GREATLY boost the DEX damage scaling of the bow.

Now for your melee weapon you have a few upgrade options. If you want a no fuse, no muse weapon that just does high damage, grab your favorite curved/Asian sword or thrusting weapon and at +0 use bladestone. It does the same thing as sticky, boost DEX scaling and remove STR scaling.

A couple of other options are more specialized: There is suckerstone which has slightly lower base damage than bladestone, but the same high DEX scaling, but adds bleed to the weapon. That means if you strike the enemy enough times in succession they will begin to "bleed out" and get damage / second for several seconds.

Marrowstone slightly lowers the base damage, and doesn't have quite as high as DEX scaling as bladestone or suckerstone, but it boosts critical damage. That means whenever you backstab an enemy or parry their blow and then strike (called a riposte) you get a MASSIVE boost in damage. This works best when applied to weapons that already have a high base critical damage like daggers and rapier-type weapons.