My wife is a classically trained singer whose mother (a long-term cigarette smoker) died of lung cancer. My father (a non-smoker) died of a lung ailment at 59 after growing up in a smoking household, while his mother (not sure if she smoked or not - I never saw her do so) lived to be 97. Knowing exactly how an individual is going to react to environmental hazards is a serious guessing game, so I tend to discount individual experience in favor of statistics and relative risk.
Personally, I don't smoke in the house largely for aesthetic reasons - I've never been a fan of stale smoke smell, and as a result I tend to clean my pipes more than might be absolutely necessary. If I had kids, that would factor in as well, as there are documented risks associated with developing lungs and environmental pollutants (up to and including off-gassing from carpeting) that I'd prefer not to gamble with. That's my take, for what it's worth. I was paranoid enough about cancer statistics when I first thought about smoking a pipe that I did a lot of homework - and the fact that I'm still here a couple of years later should tell you what I finally decided. :
:
OTOH, if I had a room that I could isolate from central air, with a direct exhaust fan and a fully sealed doorway (like a utility room/laundry room set up), I'd be inclined to turn that into a smoking lounge. I've already been granted enthusiastic provisional approval for a free standing "man cave" outbuilding, should we buy a lot big enough to add one when we buy our next place, and a couple of places we've looked at have a "bedroom/study" in the basement that could be easily isolated.
Good on you for thinking of your family - sounds like they're likewise considerate of your choice to smoke, so you seem to be in great shape as far as that goes.