Don't take me wrong, there are indeed lots of specific reasons to scan at 300 dpi, or some case at even 600 dpi:
I've been suggesting that 300 ppi is excessive image size for video screens, but don't take me wrong, there are indeed many specific reasons to scan at 300 ppi, or even 600 ppi, even if the screen is not likely one of them.
And of course, there are very many cases when 300 dpi would be inappropriate too. It depends on the requirements of the job. Instead of the above list, most of us will typically be scanning color photos for video or printer. Normally we would use about 75 to 200 dpi. Perhaps 200 dpi for printing on photoquality printers, and more likely 100 to 150 dpi for video monitors, and much of the time, 300 dpi would be unnecessary overkill and waste and pain. But it's not without exception, and the most common exception would be to create large images for photo-quality printers (more in the Printer Resolution section).
I would suggest that 200 dpi or 300 dpi is often HIGH scanning resolution. You must ask yourself "What am I going to do with this image? What does the output device need? Can it make use of a huge image?". Except for a few special purposes, and except for Line art, more than 200 dpi can often be pointless and painful, because our output devices are not likely to be able to use more.
Part of my goal here is to point out to newbies that it is quite reasonable to scan photo prints at "as low as possible" resolution, instead of "as high as possible". "Possible" depends on the job to be accomplished.
We will speak about graphic file formats in a following section, but I would be derelict in my duty if I did not offer my opinion that it is absurd to scan a huge image supposedly "for quality" and then archive it in JPG format so the file will be small. These are conflicting goals, you either want maximal quality, or maybe not in every case. See the File Format section for advice about NOT storing your only master copy as a JPG file. JPG format is wonderful for many purposes, but archiving maximum quality is not one of them. A printer file may be an exception, and certainly web pages and email are exceptions, but I did use the word "archive" above. You would make compressed copies for purposes where size matters more than quality, but you would keep your one valuable master copy squirreled away somewhere safe as a TIF file. Each concept does have its purposes, but it doesn't make sense to use a huge but low-grade image for your master copy. You can't have both, it's a law, and they will get you. <grin> It is a very common mistake, but you'll either want the best image, like archive quality, or a small file, like for email or a web page.
The best way to reduce file size is to reduce resolution to a useful amount.
If you do have a good reason to work up near 300 dpi with color images, you'll want a lot of memory. A 300 dpi 8.5x11 inch color image is 25 megabytes!