What can we actually see?
At 'normal viewing distance', the human eye is capable of resolving separate details about 1 minute of arc apart and this translates to around 180 lines per inch - which from our point of view can further be translated into 180 pixels per inch.
To be on the safe side, and to give a little leeway, we can raise the pixels per inch to 200 or 220ppi. Resolutions in excess of this will not result in a visibly sharper image but could offer the advantage of a greater enlargement, should this be required. Bear in mind that the finer the resolution, the greater the ppi and the larger the file size will become.

Why is 300ppi often referred to as the standard?
300ppi is related to the printing industry. The off-set litho printing process requires a 'dithering process' to eliminate moire patterning. The 'dither' tends to reduce definition so a higher level is required to begin with.

200ppi
200ppi is fine for printing by an ink-jet printer or similar system as we are only concerned with optical resolution and how the print appears at normal range.

Most consumer (SLR) models will yield excellent results from pixel counts in excess of 6mpxl to 10mpxl. We can determine this by multiplying the nominal pixel count by a factor of three, as there are three channels (red, green & blue), give file sizes of (6mpxl x 3)18mb, to (10mpxl x 3) 30mb thus providing very acceptable prints of A3 size and greater.

There is also a big difference in quality (and cost!) between 'consumer chips' and 'pro chips'.

High-end 'Pro cameras' such as the 'full frame' models give fantastic results with great resolution. These models are aimed at professional photographers and dedicated enthusiasts. The price range reflects the high specification.

What is acceptable is of course subjective and depends upon content and the purpose for which the image is intended.

Quality digital cameras shoot RAW file format and this allows 16 bit working to carry through into 'Photoshop'. For more information about 'RAW' click on the link below.

Scanning-in

A starting point:
Scanning to give the same size print as the original image (a bit like a photocopier)
To do this, a flat-bed scanner should be adjusted to give 200ppi if photo-quality results are required at a 1:1 ratio from original image to printed output.
For instance: An A4 picture is placed on the flat-bed scanner and you make an A4 print from the printer.

What if the print we are to make is larger than the original image?
Say a 35mm negative or slide from which we wish to make a large print
Well, if we scan something which has a smaller size than the the print we wish to make, we have to scan with finer detail (pixels) so that there are enough pixels to make the print without the individual pixels showing.

Bearing in mind the above and with respect to 35mm originals, the following information will provide a guide to resolution and file size.
Remember how small a 35mm frame is (24 x 36mm) and how large the print will be by comparison - so we need to pack a lot of fine detail (pixels) into the scan to ensure that the print looks 'photo-realistic'.
The number of pixels we generate during scanning (or within a digital camera for that matter) have to be 'stretched like elastic' over the much larger surface area of the print. The larger the print, the more thinly the pixels are spread and we must avoid dropping below the magic 200 pixels per inch.

Scanning at 2,700ppi, a colour 35mm negative or slide will produce some 8,000,000 pixels (8mpxl) of picture information with a resulting file size of 20mb to 25mb (the figure does vary). When expanded to A3 size, the image will look fine at around 200 to 220ppi.
If the image was intended for a print no larger than A4 size, again at 200ppi, the scanning resolution could be reduced to 1,800ppi to produce a file size of around 11mb.
For A5 print, the file size would be in the order of 5mb or so.

Size is Important.
My preferred scanning-in resolution for 35mm slides or negatives is 2,700ppi (pixels per inch). This scanning resolution will enable me to print an A3 size picture of photographic quality. The resolution of the print at that size will be in the order of 220 ppi.

Remember, changing the scanning resolution will alter file size and therefor have a direct bearing upon the maximum acceptable picture quality of the final image - which shouldn't fall below 200ppi.

If you’re uncertain as to the size required for the finished picture/print, always scan in at maximum. It’s too late to change file size later as for quality the option of ‘re-sampling’ is not an answer. Re-sampling can be done but it's best treated as a last resort - there's nothing better than getting it right in the first place.

The Web
Images intended for the Internet / Web Pages require a low resolution to match monitors screens at around 72ppi this being a common resolution for many monitors. So if you need to scan an image for the web, the file size will be very small(often 1mb or so) and this in turn will be compressed into a 'jpeg' file (from, say, 150kb to 300kb).