Programs like Picasa, iPhoto, ACDSee, are pretty much all most people need. They let you sort/manage your photos in a way that lets you find them again, either by searching (e.g. for keywords you've added) or just sorting them in a way that makes sense to you. It's a big step up from having a bunch of folders of badly named photos on your hard drive.
Programs like Lightroom and Aperture are the more "Pro" versions, where you can do much more serious editing, tagging, processing, etc and automate a lot of actions that might be useful for providing your photos to someone else (e.g. export a series of photos at a specific resolution with a common naming system).
Generally, I tell people to get one from the first category (Picasa if they're using Windows, iPhoto if they're on a Mac) if
they just like taking photos but don't modify them afterwards; or
they own a point & shoot (and not a high end DSLR)
(not to be elitist or anything, but generally speaking people with a cheaper camera are probably more interested in capturing the memories rather than editing/managing their photos)
they don't know what Lightroom / Aperture actually do; or
they've never opened up PhotoShop to edit a picture
(again, generally speaking if someone's not used some serious photo editing software or doesn't even know about it, they probably don't need photo management software like Lightroom or Aperture)
Whereas I'd recommend the latter for someone
who is really interested in the technical aspects of photography
who doesn't use their camera on the Auto settings
(they're probably the sorts of people who'd find the capabilities of a more professional photo manager interesting if not helpful)
Remember that with all the extra functionality of the more professional photo programs, you also have a more complex interface with more things you can break, so if you're unsure, I'd say try using Picasa (or another from that category) and if you need to make adjustments to photos often (that Picasa just can't do) then think about switching.
