CCD size is an important part of what makes a good image, but it doesn't mean that you should lower your pixel density - it means you should have a bigger CCD.

As the CCD becomes more dense it's true that each pixel will receive less photons, but they're still all recorded. Once the image is rendered and you compare it to a picture taken with a lower MP camera with the same size CCD you'll see a better image with the more pixel dense CCD because it has recorded more information with more precision. Yes, the image may be more grainy the more you zoom in - but with the lower MP camera it would be more pixellated, and that's worse.

It is true, however, that you may be better off having a lower MP camera with a larger CCD than the opposite - and there are forums all over the internet with arguments about the best compromise. It is also true that there are diminishing returns as you go up the scale - but theoretically it won't hit a wall until you're making CCDs with each pixel only one photon across - and AFAIK no-one is doing that yet