Hi everyone, thanks a lot for your replies. I came here to get advice from people who have 'been there, done that' and your replies have been great.
Having other people in my bed isn't an issue for me. I stay in hotels 20+ times a year for work and I don't spend too long thinking about what's been going on in that room - but I do understand the point that if you rent it out it's not entirely 'your' home.
I own Buy to Let properties here in the UK and get that these things have to be done with a spreadsheet and they don't run themselves. I also understand that the wear and tear on a rented property is a real consideration. Multiply that by a property that's hundreds of miles away, Brexit and a schoolboy grasp of French and it's a decision I have to think hard about.
I completely get the point about the kids growing up and not wanting to visit. They are both under 4, so I hopefully have a decade or so and then I can look forward to being able to bolt there myself.
I had considered an apartment and briefly looked into leaseback in a ski resort (partner loves skiing) but we like the ability to ski in different resorts, so the fixed location has more downsides than upsides.
Other posts have hit the nail on the head. The idea is a place where the kids can splash about in the pool, we can wander into town or drive to the beach and friends and family might occasionally come to visit and stay locally (or on site). We make it a house and an area we invest time in and get to know well.
Another stumbling block is trying to find locations that fit the: '1-hour to the beach, from the airport, walkable to a nice small town and within budget' criteria. To be honest, the more time I spend in Google Maps, the more I'm paralysed by choice.
Before we had kids (and lockdowns), my partner and I visited France a lot. We have done a fair few weeks and weekends across France on a motorbike and we have had great stays throughout, from Le Crotoy, to Questembert / Malestroit to Riberac and Tarascon.. all brilliant places, with next to nothing in common when you write down their positives and negatives. Still, that's one for me to try and crack.
Your shared experiences on property ownership really help me build a clearer picture.