One thing to consider is how the photos are going to be viewed. Will they be displayed on a TV or computer screen or will they be printed out?
Most DVD resolutions are 720×480 (NTSC) or 720×576 (PAL). The most common computer screen resolution is 1024×768 pixels. Pixel count on an HDTV is typically 1280×720 (720p), 1920×1080 (1080i), or 1920x1080p (1080p). A 3 megapixel camera should be able to take a 1984 x 1488 photo so for displays, most photos larger than 3 megapixels are going to be scaled down to fit the screen.
Most books and magazines use 300 pixels per inch (ppi) for their photos. At 300ppi you need 2400×3000 pixels for an 8″x10″ photo. This means for a nice book/magaine quality photo you need an 8 megapixel camera, which should be able to take 3264 x 2448 pixels (10.88″x8.16″ @ 300ppi). Most photos larger than 8 megapixels will be scaled down when printing at 300ppi. For a true (35mm camera) photo quality you will need between 24 and 30 megapixels on your camera and many more ppi. See this Megapixels chart for more information.
Another thing to consider is digital file size of the each photo.
The higher the megapixel the larger the file size of the image. This means more space needed on your memory card, but can also affect the speed at which the camera takes pictures, as with larger megapixels it has more data to store and may take longer to write the data to the memory card. You can use this Megapixel chart to determine the average filesize of each photo for a given megapixel, or use this megapixel calculator to determine about how many of these photos will fit on a given memory card size or how many megapixels are required to achieve a particular resolution.