I have not stayed at either but I can tell you that Bay View in Bourne is not on the water and isn't on the Cape proper-it's on the mainland side of the bridge. This would mean traversing the bridge any time you wanted to go further out onto the Cape. Depending on how many snow days the schools use and how late school gets out, that could be the beginning of heavy traffic season, which can be a nightmare. If you want to get out to the best beaches you have to head out to the National Seashore almost at the tip of the Cape, so a campground further is better. It's been a long time since I've done real "kid" stuff down there but I highly recommend biking along the canal and visiting the small museum there. heritage Gardens in Sandwich is a nice, peaceful place that hosts a lot of events for both kids and adults.
There are two towns I would recommend "thinking about" before visiting:
Hyannis: We were just there a few weeks ago and it's gone WAAAAY downhill. Drug busts, drunks all over the place, adult toy stores, and drug paraphernalia being sold in store fronts.
Provincetown: Home to many "alternative" lifestyles. Once a family-friendly, artist/hippie type colony, it turned into a place with punkers and goths but was still fine. Last time I was there, and I'll admit it was a number of years ago, it also had turned into a drug-infested haven for stoners and real wackadoodles. Not sure if it's changed-hopefully someone else can weigh in,