Here's the guide I go by:
75-100 dpi - pictures you're going to post to a web page
150-300 dpi - documents with small print that I want to be able to read later.
Even a 300dpi scan will generally make a picture that is much bigger than your monitor and isn't very usable.
Going above 300dpi is something most people don't have a use for. But some do. People that need the detail such as graphic designers, professional (or us fanatic amateur photographers), etc. But a 600 dpi scan isn't going to be a look-see type picture on the monitor.
Printing such images can give you some of that sharpness back, but not all photographs are focused sharp enough to take advantage of above 300 dpi scans. And not all printers take [b]full[/b] advantage of above 600dpi scans. Sure, they measure 1440dpi or 2880dpi. I've got one, and it prints beautifully, but my eyes can't tell the difference above a certain level.
If you're looking at buying a dedicated photo printer, then it sounds like you may have need for the finer resolution scans.
Also...for slides, you do want to use the higher resolutions such as 600-1200 dpi. But only if you printer does that resolution in it's "hardware mode" and not using "software interpolation." I've got an older scanner here that is capable in "hardware mode" up to 300dpi and goes up to 2400dpi using "software interpolation." My other scanner is newer (and it costs less as they always do) and will scan up to 600dpi without using "software interpolation."
Last...some scanners used to have dedicated slide scanning attachments. I'm not sure what they involved or if a backlight is needed for the slides. I scanned an sonogram once from the x-ray looking slide and it only worked when I used a backlight (a bright flashlight).