I made a post about this exact issue, I even included a test with pictures.
Long story short, weapon visuals should reflect their stats. If you have a generic greatsword it should scale with the appropriate stats. If it looks magical it should scale with Intelligence in some way. If it has like purity seals it can be Faith based.
A Test to see if Weapon Visuals and their Stat Scaling match your expectations