I purchased a DS lite for my wife about three weeks ago and she plays it every other day. She loves the games for the system because she, unlike myself, prefers the non-traditional games. She plays brain age quite a bit as well as Nintendogs.
Not only does she love it, my daughters who are three and five are constantly playing. My three year old had no problems picking up and playing the Dora game and my five year old loves her Nintendog Lucky.
I have yet to purchase the Zelda game or anything for a truly hardcore gamer, so I cannot comment on this aspect. As for any supposed non-gamer in your family, I highly recommend the DS.