Everyone's circumstances are different, but it can definitely work.
My mum lived with us for seven years, until she died in 1994. She was particularly close to my youngest daughter, who regarded her as a second mum. I was very glad she was with us, particularly towards the end of her life when she began to need extra care due to her heart problems.
Our youngest daughter has always lived at home and, eleven years ago, her partner moved in with us when he moved across country to be with her. They've been here ever since and now have their two little girls, aged seven and five. I have helped to look after them ever since they were born, and do so whenever their mum and dad go out, as they do most days. The girls are now being home-educated and, as their mum is often busy with running her partner's business, a lot of the teaching falls to me. They do keep me busy, but they also keep me interested and give me a lot of affection.
Yes, there have been a few spectacular rows - usually between my husband and daughter, both as hard-headed as each other - but no more than in any family. In general, we rub along together pretty well. I would miss them terribly if they moved out.