Substrate choice partially depends on personal preference, but mainly on whether the fish you are planning to keep have any specific requirements. For example, loaches, Corydoras and earth eaters are a few of the fish which benefit from fine substrate because they will sift it through their gills while looking for food. Some loaches will even bury themselves under the sand, while some cichlids will only spawn in pits which they have dug.
As I am still undecided on which species of fish I will have for the bottom of the aquarium, safest choice is sand. I have bought some play sand, which is aquarium safe and is considerably cheaper than aquarium sand.
Regardless which substrate one chooses, it is important to make sure that it is not dyed because most dyes will come off with time and some may be harmful to aquatic animals.
Another important point to consider is that some substrates will alter the pH of the water. For example, peat will result in the water becoming more acidic, while crushed coral will have exactly the opposite effect. These properties can be very useful to for adjusting the aquarium pH in the long term, but I would still recommend that anyone new to fishkeeping should avoid these and pick fish which will be happy in the water parameters that one already has. Normal sand and gravel are usually inert, so have no effect on the water chemistry.
The first thing to do with new substrate is to clean it: this can be done by placing a small quantity of it into a bucket and rinsing it with water until the water runs clear even when one stirs it. If the substrate is not clean enough, it will cause the water to become cloudy because of the dust in it, so I usually give it an extra rinse before adding it to the aquarium. The water is still likely to be slightly cloudy for a couple of days even though the substrate has been washed, so it is possible to temporarily add filter wool (also known as floss) into the filter, so that the water clears quicker. Luckily for me, this play sand was quite clean, so each 3 litre lot took only three goes to clean, instead of the more usual 6-8. Before draining the water after each clean, I waited a few seconds to let the smaller bits of sand, which were not the dust which can make the water cloudy, settle.
Wet sand will often look darker than dry sand, so it is important to take that into account when picking a colour. In the photo above, you can see the dry sand on to top with the wet sand below it. The sand which was available at my local DIY store is a dull brown with specks of larger dark and pale stones. I think this gives it quite a natural look, which is what I am aiming at.