I suppose if you want prints, the higher resolution cameras are nice. Just remember that quality high gloss printing paper is probably as expensive as buying film and having prints made. Also, you are limited by the dots per inch of your printer.
I rarely print my pictures. I have an old Sony Mavica (probably 1st generation) that has a 640x480 format. I think it has a higher resolution, but I've never used it. I store my pictures on the computer, email them to others to see on their computers, or FTP them to a web server for viewing on a computer. 640x480 is fine for all of that. The pictures store in jpeg format at around 48,000 bytes; it depends on the density of colors how much it gets compressed. For web site use, I get them even smaller with optimizer software, so they'll load faster. The few occasions I have printed a picture, it looks OK on decent paper - certainly not good enough to enter in a photo art show, but not bad.
The Mavica stores it's pictures on a 3.5" floppy disk (some of the new ones have memory cards). Because of that, it's a big, bulky camera, but that hasn't caused me any grief in the 6 or 7 years I've had it. My "film" is a box of floppies, I can store 40 to 45 pictures and thumbnails on a disk, and my pictures are already archived - if I want to keep them off my hard disk, I just label the floppy and throw it in a box. I can also show my pictures on anybody's computer without having to install a cable.
The FD75 is the current model like mine, 640x480 res, 10x optical zoom, 24bit color, sells for ~$300. I paid about $800 for mine, but that was back when they were still a new toy. The FD100 has more resolution, less zoom, and can use either a floppy or a memory stick, and is a little cheaper - I've seen it for ~$250. I don't know what the current ones are using for a battery, but mine uses a video camera battery - the one that comes with it is good for about 115 minutes, and I bought a premium battery for it that's good for about 220 minutes.
They also have some new models that save the pictures right onto a 3" CD-ROM-RW that can be played on any CD-ROM. Mine's getting pretty beat up, but still works - really rugged. When it gives up the ghost, I'll get the FD75 or whatever equivalent they're selling then. 640x480 (about .3 MB) is all the resolution I'll ever need.