Nowhere's that tacky I guess, but around Yarmouth there are lots of motels, putt-putt courses etc, so that coloured by view a bit. And Haddissaggis is right that nowhere is super posh (compared with Nantucket, which I think very much is). Provincetown is a lovely town, really chilled with lots to do, but you might prefer to visit, rather than to base your stay there, as it's a bit remote (and, putting it delicately, the nightlife might not, or might, be your thing, but look into that before committing....). We had a great day there, and as we felt the children wouldn't manage 4 hours on a whale watch did an Art's dune tour, which was great fun.
We stated at Pine Lodge Cottages/ Pilgrim View, which is on White Pond in Chatham. Found it on recommendation of a Boston-based client, who has been going for years. I don't think they really advertise or use agencies. Very reasonably priced, 'rustic' (i.e. dated) lodges, with access to the private lake and beach for swimming and rowing. It is about USD 900 for a 2-bed lodge in peak season. We paid more for a three bed house, but the lodges looked absolutely fine. Lots of families (including English ones) who had been going for years and years. It's run by a very nice family, who are really helpful. You have to email them directly to book, but they do have a website with information.