Of course, the best answer to, "How many megapixels do I need?" depends on what you want to do. If you're capturing photos of friends and family for publishing on the web, then its really hard to justify "needing" more than about 6. If you plan on printing your photos in large format, and by large I mean poster size, then you'll want to get at least 10.
The reality is most consumers shoot in JPG in a low-res 1024 or 640 format that's easy to email around or put on a webpage. If this is your end-use, you'll overpay for any number much greater than 6. In my case, I have a Nikon D80 which shoots 10.2. Because I shoot in RAW format (which gives me the most flexibility after the shoot), each still occupies about 10MB of disk space, requiring me to be very disciplined in cleaning up my unwanted photos. I'm not so good at doing this, thus i have a 750GB disk drive on my Mac Pro, and my Aperture photo library currently sits around 80GB. For this I pay Amazon about $25 a month to back these photos up on their S3 data service. ($25 a month is a lot cheaper than going to Vietnam and Cambodia AGAIN should I lose all my photos).
So why do I need all these megapixels? If you've been to my place, you know I don't print out my photos (there are over 13,000 to choose from on kriskoeller.com, so its not for lack of inspiration). Besides printing, the other advantage worth considering is if you do a good deal of wildlife photography, you can zoom in on a subject and crop the image down and still have a pretty high resolution photo. As an example, the photos below were discarded because the dog was too far away and couldn't really be seen. However, by cropping the photos down and focusing on the dog or her face, you can see there's actually quite a bit to these photos. A lower resolution camera would not have given me these clear images.
Do consider, however, there's more than just megapixels to taking good images. I have a $400 Nikkor lens on a high end prosumer Nikon digital SLR. The sensor has to be good quality, you have to have expensive (meaning, good) lenses and you do have to know what you're doing. My advice is get a camera with good low-light ISO resolution, discard or forgo the kit lens for an upgrade and target something in the 6-10 megapixel range. You'll be pleased with the results.
The link below is to my pictures from Yellowstone National Park last fall. Many of these were taken (safely) from far away, and the resulting photos are quite viewable.