There are some great old houses in South of Market, but turnover is rare, and they're all expensive. There are some great/awful new live/work buildings there too, but most of them are condos and all of them are expensive.
Affordability comes in Outer Mission, Excelsior, Outer Richmond, Outer Sunset. Occasionally Potrero Hill toward Cesar Chavez. "Affordability" is relative of course, but if you're finding yourself priced out of the places that everyone talks about and look nice on the map, check prices in the places I listed.
Also, remember that East Bay near BART is closer to South of Market than most of San Francisco city is, time-wise.