Why is Astora’s Straight Sword so bad

You are correct in saying that stat scaling doesn't really improve at all, beyond a minor improvement from ascensions (a sword might go from E --> D for example). In general, the stat classes that each weapon has in the game won't change much, even after being ascended fully.

Compared to Demon's Souls, upgrading a weapon to its max in Dark Souls provides less benefit because of this.

The bonus damage from stat scaling does increase for +n weapons, albeit it's a minor, linear increase (for example, a Battle Axe from +0 to +2 made its bonus damage from stats go +20->+22->+24). Thanks Tacroy

If you need to know when to replace your Drake's Sword, I would say get rid of it when you have high enough stats in one of the "damage" stats (Strength/Dexterity/Faith) and you have an adequate amount of resources to reinforce a scaling weapon to its max.

The Wikidot Dark Souls Wiki lists 4 areas as "optional" - The Great Hollow, Ash Lake and Painted World of Ariamis on the Areas page, plus Valley of Drakes on its own page. (I'd argue Darkroot Basin is also optional, but the border between Darkroot Garden and Darkroot Basin is somewhat blurred.)

I'm unsure of the role of the Moonlight Butterfly in the game progression, but there are definitely other parts of Darkroot Garden that need to be completed before the game can be finished.

I haven't finished the game yet myself (though I am close), but since I did not choose the Master Key as my starting gift, I have had to unlock each area in sequence. The Master Key does allow you more freedom to access areas out of sequence, but I think the game is better structured without it.